# How to connect Power BI to Dynamics 365 Finance and Supply Chain

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

## Short answer

For the cloud service, do not point Power BI at transactional OData for large reports. Use the lakehouse route (Fabric Link or Synapse Link), export to your own database where still supported, or embedded workspaces for operational views. On-premises deployments rely on the entity store and a data gateway.

## System

Microsoft Dynamics 365 Finance and Supply Chain

## Capability

power-bi

## Intro

Power BI and FSCM are close relatives, which is both helpful and misleading. Microsoft ships embedded Power BI workspaces and many reports out of the box, so basic dashboards are quick. The trouble starts when finance wants a multi-year margin analysis or operations wants inventory ageing across sites: those queries cannot sensibly run against the transactional database.

The integration choice is largely about where analytical data lives. Direct database access to a production cloud environment is not how you build reporting, so the supported answer is to replicate data to an analytics store and report from there. The options have changed over the years, so confirm what is current and supported for your environment before designing.

Once data is modelled well, adding natural-language Q&A is easier. Power BI's own Copilot features can answer questions over a semantic model, and a custom assistant can sit on the same model for questions the standard experience handles poorly. The semantic model is the asset that makes both work.

## Use Cases

### Inventory ageing and turns

**Description:** Slow-moving stock by site, warehouse and product category from on-hand and inventory transaction history, highlighting capital tied in obsolete items.

### Purchase price and delivery performance

**Description:** Vendor on-time-in-full against confirmed dates from received lines, plus price variance trends by vendor and item.

### Production schedule adherence

**Description:** Planned versus actual start and finish by work cell, with scrap and rework drawn from production journals.

### Margin by product and customer

**Description:** Sales order and invoice lines combined with cost data to show true margin after freight and rebates.

### Finance close monitoring

**Description:** Open subledger items, unposted journals and intercompany balances by legal entity in one view.

### Supply chain exception board

**Description:** Planned orders with action messages and open shortages, refreshed on a schedule for the planning meeting.

## System Surface

- Fabric Link and Azure Synapse Link for Dataverse, which replicate selected FSCM tables into a lakehouse or lake for analytics; the older Export to Data Lake feature was deprecated, so verify current guidance.
- Bring your own database (BYOD) export through the data management framework to an Azure SQL database, still used in many estates though Microsoft steers new work toward the lake routes.
- OData v4 data entities, acceptable for small, filtered datasets and ad hoc reports but not for bulk history because of throttling.
- Embedded Power BI reports and workspaces inside FSCM, and the entity store (aggregate measurements) used by those standard analytics.
- For on-premises (local business data) deployments, the on-premises data gateway and direct access to the AX database or entity store, with feature support differing from the cloud.
- Dataverse virtual entities and the Power Platform integration for combining FSCM data with other business apps.

## How It Works

### List the reports and grain

**Detail:** Decide which questions you need and at what level: item by warehouse by day is far larger than item by month. This drives table selection and refresh cost.

### Choose the replication path

**Detail:** Pick Fabric Link or Synapse Link for new cloud work, BYOD only if you already depend on it, and a gateway with the entity store or database for on-premises.

### Select tables and entities

**Detail:** Replicate only the tables you need, such as inventory transactions, purchase and sales lines, production orders and ledger entries, then check column names against your version.

### Build the semantic model

**Detail:** Create dimensions (product, site, vendor, legal entity, financial dimensions) and facts, with measures for on-hand, margin and on-time delivery defined once.

### Apply security

**Detail:** Implement row-level security by legal entity or business unit so Power BI honours the same boundaries as FSCM.

### Schedule refresh and monitor

**Detail:** Set refresh cadence per fact table, and watch lag so planners know how fresh the numbers are.

### Add natural-language Q&A

**Detail:** Prepare the semantic model with clear names, synonyms and descriptions, then enable Power BI Q&A or Copilot, or place a custom assistant over the model.

## Example Flows

### Inventory ageing refresh

**Trigger:** The scheduled refresh runs at the start of the shift.

**Steps:**

- The lakehouse already holds replicated inventory transaction tables.
- The semantic model computes ageing buckets by receipt date.
- Row-level security filters the report to the viewer's sites.

**Result:** Planners open a current ageing view without any load on the transactional system.

### Ask a question over the model

**Trigger:** A finance lead types, which vendors had the worst on-time delivery last quarter?

**Steps:**

- Q&A or an assistant maps the terms to the on-time measure and vendor dimension.
- It runs the query against the semantic model, not FSCM.
- It returns a chart and the definition of on-time used.

**Result:** A fast answer with the measure definition visible, avoiding a ticket to the BI team.

## On Prem

On-premises (local business data) FSCM reports from SQL-based sources through the on-premises data gateway, with the entity store or AX database as the source; check what your build supports and avoid reporting against the live OLTP database during peak hours. If you need to keep the data fully inside your network, Power BI Report Server is the self-hosted alternative to the cloud service, with feature gaps compared with Power BI online. For natural-language questions with no cloud service, a locally hosted open-weight model can generate queries against the warehouse or semantic model under read-only access.

## Security

- Implement row-level security by legal entity and business unit in the semantic model to mirror FSCM data access.
- Use workspace roles and sensitivity labels, and control export from reports containing cost or margin.
- Limit replication to the tables needed; avoid copying bank, payroll or personal data unless required.
- Secure the lakehouse or database with the same care as the ERP: network rules, managed identities and audit logs.
- For natural-language access, run generated queries with a read-only identity that respects the same security.

## Options

### Use embedded workspaces and standard Power BI content in FSCM

**Fit:** Enough for common operational dashboards and for teams without a BI developer.

**Effort:** Configuration, usually days.

### Fabric Link or Synapse Link into a lakehouse with a custom semantic model

**Fit:** Best for cross-functional analytics, history and combining FSCM with other sources.

**Effort:** Several weeks for a first domain, including modelling and security.

### Add a natural-language layer with a partner such as Netray

**Fit:** Useful when you need grounded Q&A over the model or over live data, including on-premises inference.

**Effort:** Built on top of the semantic model once it is stable.

## Pitfalls

- Connecting Power BI directly to OData for large fact tables and hitting throttling or very slow refreshes.
- Relying on a deprecated export route without checking Microsoft's current guidance.
- Skipping financial dimensions modelling, then being unable to reproduce the numbers on finance's reports.
- Defining on-time delivery or margin differently in each report instead of once in the model.
- Forgetting row-level security, so a regional manager sees every legal entity.

## Faq

### Can Power BI query the Dynamics 365 FSCM database directly?

**Answer:** Not in the production cloud service as a reporting approach. Use replication to a lakehouse (Fabric Link or Synapse Link), a BYOD database where supported, or limited OData. On-premises deployments can reach the database through a data gateway, but protect the live system from heavy queries.

### What happened to Export to Data Lake?

**Answer:** Microsoft deprecated that feature in favour of Synapse Link for Dataverse and Fabric Link. Dates and details have shifted, so check the current Microsoft documentation for your environment before building anything new on the older route.

### Is OData ever suitable for Power BI?

**Answer:** For small, filtered datasets such as a current open-order list or a reference table, yes. For history, large transaction tables or frequent refresh, no, because of service protection and throttling. Choose the replication path for anything big.

### How do we keep security consistent?

**Answer:** Apply row-level security in the semantic model keyed to legal entity and business unit, and assign users to roles accordingly. Power BI does not automatically inherit FSCM security roles on replicated data, so the model must reproduce the boundaries deliberately.

### Can we ask questions in plain English?

**Answer:** Yes. Power BI Q&A and Copilot features work best on a well-named semantic model with synonyms and descriptions. For harder questions or private hosting, a custom assistant can generate read-only queries against the same model or warehouse. The model quality matters more than the chat interface.

## Search terms

dynamics 365 finance and supply chain power bi, power bi dynamics 365 f&o, d365 fscm power bi fabric link, synapse link dynamics 365 finance power bi, dynamics 365 byod power bi, power bi dynamics 365 on premises gateway, dynamics 365 supply chain dashboards, dynamics 365 inventory power bi, power bi natural language dynamics 365, dynamics 365 fscm reporting odata power bi

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