# How to connect Power BI to ERPNext

> Source: https://netray.co/integrations/erpnext-power-bi
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Integrations

## Short answer

Connect Power BI to ERPNext through a read-only MariaDB user, the Frappe REST API, or a replicated reporting database. For anything beyond small data, model from a replica or warehouse, use an on-premises data gateway for self-hosted sites, and build a short set of manufacturing dashboards.

## System

ERPNext

## Capability

power-bi

## Intro

ERPNext has its own reports and Frappe Insights style tooling, but many finance and operations teams already live in Power BI and want ERPNext numbers beside budgets, CRM and spreadsheets. The question is how to pull the data without slowing the live site or breaking permissions.

Each route has a catch. Direct database reads are fast to build but depend on table names and bypass Frappe permissions. The REST API respects permissions but is slow for large tables and awkward for child tables. A reporting replica or warehouse costs more to set up, yet gives the most stable model.

This page compares the routes, covers the data gateway on self-hosted sites, lists dashboards worth building first and shows how to add natural-language questions on top.

## Use Cases

### Inventory health

**Description:** Stock value by warehouse and item group, ageing from Stock Ledger Entry, and slow-moving items against reorder levels.

### Production performance

**Description:** Work Order completion against planned dates, Job Card time by operation and workstation, and material consumption against the BOM.

### Purchasing and supplier delivery

**Description:** On-time receipt by supplier from Purchase Order schedule dates versus Purchase Receipt dates, plus price variance against last rate.

### Sales and order backlog

**Description:** Open Sales Order value by delivery month, customer concentration and delivery performance against promised dates.

### Quality and rejections

**Description:** Quality Inspection pass rates by item and supplier, with rejected quantities trended over time.

### Finance consolidation

**Description:** GL Entry summaries and receivables ageing across multiple Companies, joined with budget data held outside ERPNext.

## System Surface

- MariaDB schema with one table per DocType prefixed tab (for example tabSales Order, tabSales Order Item, tabStock Ledger Entry), readable through a read-only database user.
- Power BI MySQL connector, which commonly works with MariaDB; confirm compatibility, driver requirements and SSL settings for your versions.
- Frappe REST API through the Power BI Web connector, paginating with limit_start and limit_page_length, suitable for small or filtered datasets.
- Query Reports and Script Reports whose output can be fetched via report methods or CSV export for semi-manual refresh.
- Replication, backups or scheduled exports into a reporting database or warehouse, which decouple analytics load from the live site.
- Frappe Insights or built-in dashboards for quick in-app views, worth comparing before building a Power BI model.

## How It Works

### Pick the source pattern

**Detail:** For a small site, a read-only DB user may do. For larger data or strict permissions, replicate to a reporting database or use API extracts.

### Create a read-only database user

**Detail:** Grant SELECT only on needed tables, restrict by host and use TLS. Avoid sensitive tables such as payroll unless approved.

### Build staging views

**Detail:** Create views that rename columns, filter docstatus to submitted documents and flatten child tables, so Power BI models do not depend on raw tab names.

### Install the data gateway

**Detail:** For an on-prem database, install the on-premises data gateway on a server that reaches MariaDB and register it in the Power BI service.

### Model and measure

**Detail:** Create a star schema with dimensions for Item, Customer, Supplier, Warehouse and Date, and define measures such as stock value and on-time percentage once, in the model.

### Schedule refresh

**Detail:** Use incremental refresh on large tables like Stock Ledger Entry, keyed on posting date, and refresh outside peak hours.

### Add natural-language questions

**Detail:** Use Power BI Q and A or Copilot features where your licence allows, or add a private assistant that writes queries against the model views.

## Example Flows

### Nightly stock ageing

**Trigger:** Scheduled refresh at 02:00 through the gateway.

**Steps:**

- The gateway queries a staging view of Stock Ledger Entry and Bin from a replica.
- Power BI loads only new posting dates through incremental refresh.
- Measures compute ageing buckets and value by warehouse.
- An alert fires if slow-moving value passes a threshold.

**Result:** The inventory manager sees ageing and stuck value every morning without running exports.

### Supplier scorecard

**Trigger:** Monthly refresh before the purchasing review.

**Steps:**

- Join Purchase Order Item schedule dates to Purchase Receipt Item receipt dates.
- Calculate days late and on-time percentage by supplier.
- Add Quality Inspection rejection rates by supplier.
- Publish a report with supplier and item group slicers.

**Result:** The review uses one agreed scorecard rather than separate spreadsheets.

## On Prem

For a self-hosted ERPNext, the main piece is the on-premises data gateway, which lets the Power BI service refresh from a MariaDB server inside your network without opening inbound ports. Point it at a replica so analytics queries do not compete with users. Power BI itself is a cloud service, so decide which data is acceptable to import. If not, consider Power BI Report Server or another on-prem reporting tool. For natural-language questions on restricted data, a private model can sit beside the replica, answer through a governed set of views and keep prompts inside your network. Netray builds that layer.

## Security

- Use a read-only database account limited to reporting views, with network access restricted to the gateway or replica host.
- Remember direct SQL bypasses Frappe role and User Permissions, so apply row-level security in the Power BI model for multi-company or multi-site access.
- Exclude sensitive tables and fields such as HR, payroll and bank accounts from the reporting schema.
- Protect the gateway server, its service account and stored credentials like any production system.
- Control workspace sharing and export rights in Power BI, since cost and margin data spreads easily once published.

## Options

### Direct MariaDB connection through a gateway

**Fit:** Self-hosted sites with a technical owner and moderate data size.

**Effort:** Days to a first report; more for modelling and security.

### REST API or report extracts

**Fit:** Frappe Cloud or sites where database access is not available, with small, filtered datasets.

**Effort:** Low to start, but slow and harder to scale.

### Replica or warehouse with a Netray-built Q&A layer

**Fit:** Larger data, several sources and a need for natural-language questions that stay private.

**Effort:** Larger project; typically staged by dashboard.

## Pitfalls

- Importing every tab table raw. The model becomes slow and tied to schema changes.
- Forgetting docstatus filters, so Draft and Cancelled documents inflate totals.
- Joining child tables to parents without care, which duplicates amounts.
- Pointing refreshes at the production database during working hours.
- Defining stock value differently in Power BI than ERPNext does, causing finance disputes. Reconcile to the Stock Balance report.

## Faq

### Can Power BI connect directly to ERPNext?

**Answer:** Yes, in practice. The common routes are a read-only MariaDB connection, often via the MySQL connector, or the Frappe REST API through the Web connector. Which one fits depends on whether you host the site yourself and on how much data you need.

### Do I need the on-premises data gateway?

**Answer:** If your database is inside a private network and you use the Power BI cloud service, yes. The gateway makes outbound connections to Power BI, so you do not open inbound firewall ports. Fully cloud-hosted sources do not need one.

### Are ERPNext permissions respected in Power BI?

**Answer:** Not with direct SQL, because it bypasses Frappe permissions. The REST API respects them for the API user. For direct database models, implement row-level security in Power BI and restrict which tables and columns reach the dataset.

### Should I use Frappe Insights instead?

**Answer:** For teams that only need ERPNext data and want quick dashboards, in-app tools may be enough and are simpler. Power BI earns its place when you combine ERPNext with other sources or the organisation already standardises on it.

### Can we ask questions in plain language?

**Answer:** Yes. Power BI offers natural-language features depending on licence, and you can also add a private assistant that writes queries over curated views. Either way, restrict it to governed views so it cannot reach unreviewed tables.

## Search terms

erpnext power bi, power bi for erpnext, connect power bi to erpnext, erpnext power bi integration, erpnext mariadb power bi, erpnext reporting dashboard, erpnext data gateway power bi, erpnext manufacturing dashboard, frappe power bi

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json