Microsoft Stack
How to Connect Power BI to Dynamics 365: 4 Methods Compared
On this page
Dynamics 365 holds your sales, service and finance data. Power BI turns that data into dashboards people actually read. Putting them together sounds simple, and it is, until a report takes ten minutes to refresh or the numbers do not match what the sales team sees in the CRM.
This guide explains the main ways to connect Power BI to Dynamics 365, which one to choose for your situation and how to avoid the mistakes that make reports slow or untrustworthy.
Microsoft updates these features often. Where a detail is likely to change, such as licensing or retirement dates, we say so. Always confirm against Microsoft's current documentation before you commit to a design.
First, know which Dynamics 365 you have
The right connection depends on which application you are reporting on:
- Dataverse-based apps: Dynamics 365 Sales, Customer Service, Marketing, Field Service and apps built on Power Platform. Their data lives in Microsoft Dataverse.
- Finance and operations apps: Finance, Supply Chain Management and Commerce, which have their own data structure and different reporting options.
- Business Central: has its own Power BI connector and a separate approach.
Most of this article focuses on Dataverse-based apps, since they are the most common starting point. We cover finance and operations separately below.
The 4 main methods at a glance
| Method | Best for | Data volume | Effort | Notes |
|---|---|---|---|---|
| Dataverse connector | Quick reports on a few tables | Small to medium | Low | Simplest starting point |
| TDS endpoint (SQL) | Analysts who prefer SQL | Small to medium | Low to medium | Read-only SQL access to Dataverse |
| Azure Synapse Link for Dataverse | Large data, history, enterprise reporting | Medium to very large | Medium to high | Replicates data to a lake |
| Link to Microsoft Fabric | Organisations moving to Fabric | Medium to very large | Medium | Dataverse tables available in a Fabric lakehouse |
Method 1: The Dataverse connector
This is the default choice and where most teams should begin.
How to do it
- Open Power BI Desktop.
- Select Get Data, then search for Dataverse.
- Sign in with your organisational account.
- Choose your environment, then select the tables you need, such as Accounts, Contacts, Opportunities or Cases.
- Choose Import or DirectQuery, depending on your needs.
- Open Power Query to remove unneeded columns, filter rows and rename fields.
- Build your model and visuals, then publish to the Power BI service and set a scheduled refresh.
Strengths: quick to set up, respects Dataverse security, no extra infrastructure.
Limits: large tables can refresh slowly, and heavy transformation inside Power BI can strain performance. It is also easy to pull in far more data than the report needs.
Tip: Dataverse stores choice fields, lookups and statuses as codes and IDs. Pull the related label or name fields so your visuals show readable values, not numbers.
Method 2: The TDS endpoint
The Tabular Data Stream (TDS) endpoint lets tools query Dataverse with SQL syntax, in a read-only way. Power BI can use it through a SQL-style connection, which suits analysts who think in SQL and want more control over the query.
Good for: teams with SQL skills, report logic that is easier to write as a query, and some cases where the standard connector struggles.
Watch out for: it is read-only and does not support every SQL feature or every table scenario. Check that it is enabled for your environment and that the query you need is supported before building around it.
Method 3: Azure Synapse Link for Dataverse
Synapse Link continuously copies Dataverse data to an Azure data lake or Synapse workspace. Power BI then reads from that copy instead of hitting your live CRM.
Why teams use it
- Reports no longer compete with day-to-day users for performance
- It handles larger volumes of data more comfortably
- You can keep history and combine Dynamics 365 data with other sources, such as finance, web or marketing data
- It supports advanced analytics and machine learning on the same data
Trade-offs: you need an Azure subscription, storage and compute costs, and someone who can design and maintain the data pipeline. It is more than a reporting setup. It is a small data platform.
Method 4: Link to Microsoft Fabric
Link to Microsoft Fabric makes Dataverse tables available in a Fabric lakehouse so Power BI can report on them within the Fabric ecosystem. For organisations already investing in Fabric, this can simplify architecture compared with managing separate lake and Synapse resources.
Good for: teams standardising on Fabric, combining Dynamics 365 with other enterprise data and building shared semantic models used by many reports.
Watch out for: Fabric capacity planning and cost, and the need for clear ownership of the lakehouse and models. Confirm current feature availability for your region and licences.
What about Dynamics 365 Finance and Operations?
Finance and operations apps do not store data in the same way as the customer engagement apps, so reporting often follows a different path.
Historically, many teams used Export to Data Lake. Microsoft has been retiring that feature and steering customers toward Synapse Link and Link to Microsoft Fabric. If you are designing something new, plan around the current recommended options and verify the latest retirement and migration guidance on Microsoft's site before you start. If you already rely on the older export, put a migration plan on your roadmap.
For simple operational reporting, built-in embedded Power BI workspaces and reports in the application can also be a useful starting point.
Import, DirectQuery or a lake: how to choose
| Question | If yes, lean towards |
|---|---|
| Is the data volume small and the report simple? | Dataverse connector with Import mode |
| Do users need near-real-time numbers? | DirectQuery, accepting some performance trade-offs |
| Are tables very large or do you need history? | Synapse Link or Fabric Link |
| Do you combine Dynamics 365 with several other systems? | A lake or lakehouse approach |
| Is your team SQL-first? | TDS endpoint |
| Is the report used by hundreds of people daily? | Lake or lakehouse with a shared semantic model |
10 best practices for Power BI on Dynamics 365
- Start with the business question, not the data. List the five decisions the dashboard should support.
- Bring in only the tables and columns you need. Wide tables slow everything down.
- Filter early, for example the last 24 months rather than all history, unless users need it.
- Use a star schema. Separate fact tables (opportunities, cases, invoices) from dimensions (accounts, products, dates).
- Create a proper date table for time intelligence rather than relying on auto-generated dates.
- Show labels, not codes, for status, stage and lookup fields.
- Write measures in DAX instead of calculated columns where possible, and name them clearly.
- Agree one definition per metric. "Pipeline", "closed won" and "churn" should mean the same thing in every report.
- Protect data with row-level security so people see only their region, team or accounts.
- Monitor refreshes and performance, and set up alerts for failures.
Common problems and quick fixes
- Refresh times out: reduce columns and rows, move heavy transformations upstream, or switch to a lake-based method.
- Numbers do not match the CRM: check filters, time zones, currency conversion and inactive records. Align definitions with the sales team.
- Users see data they should not: apply row-level security in Power BI and confirm Dataverse security roles.
- Many-to-many relationships cause odd totals: redesign the model with bridge tables or a star schema.
- Dashboards load slowly: reduce visuals per page, simplify measures and avoid unnecessary cross-filtering.
Reports that deliver value in Dynamics 365
Dashboards that sales and service leaders use again and again usually include:
- Sales: pipeline by stage, win rate, average deal size, sales cycle length, forecast vs target
- Customer service: open cases by priority, first response and resolution time, backlog ageing, satisfaction trends
- Marketing: lead source performance, conversion funnel, campaign return
- Field service: job completion rate, technician utilisation, repeat visits
- Finance: revenue, receivables ageing, cash position, margin by product or customer
Start with one or two of these, get them right and expand.
When to bring in a partner
A simple Dataverse report can be built in-house in a few days. Bring in specialist help when you have large data volumes, need a lake or Fabric architecture, want a governed semantic model across departments, or find that reports are slow and the numbers are disputed. A short design review at the start often prevents expensive rebuilds.
If you are still planning your Dynamics 365 rollout, read our guide to Dynamics 365 implementation: steps, timeline and common mistakes. If you are deciding between low-code and code for your reporting and apps, see Power Platform vs custom development.
Final thoughts
Connecting Power BI to Dynamics 365 is easy to start and easy to get wrong at scale. Begin with the Dataverse connector for quick wins, move to Synapse Link or Fabric when volume, history or multi-source reporting demands it and keep your model clean, secure and consistent.
Need dashboards your leadership can trust? LoopInfinity Tech is a DPIIT-recognised software and AI company in Bhopal with expertise in Dynamics 365, Power BI and Power Platform. Book a free discovery call or email [email protected] and we will recommend the right connection method for your data.