Business Central reporting architecture
How do you choose the right reporting architecture for a Business Central project? Choosing the right Business Central reporting architecture is pretty simple as there are only three main approaches: a direct connection with BI tools like Power BI, data pipelines, or a data warehouse.
The three choices are different in scope, complexity, and therefore costs:
- A direct connection to a BI app is the simplest and cheapest option. Power BI Desktop and a personal workspace often need no additional spend. The Business Central Power BI apps include out-of-the-box reports and semantic models, but installing and sharing them needs Power BI Pro, or Premium or Fabric capacity.
- Data pipelines can be a bit more complex and might require Power BI Pro, or Premium or Fabric capacity, and additional days of configuration.
- A data warehouse is the most complex and costly option: a medium-sized organisation might require months of configuration and build, plus additional licences to develop and implement a data warehouse BI solution.
As Microsoft ships new features and Microsoft partners develop extensions and even entire verticals on top of Business Central, more mid-sized enterprises are choosing Business Central as the organisation’s ERP. That creates the need for a strategic approach to design, implement and maintain reporting and intelligence tools, including decisions about governance, security and data engineering that span across multiple applications, external services and roles.
A reporting strategy, therefore, becomes essential; fortunately, Microsoft gives us the tools to make Business Central data available for enterprise-tier intelligence services and apps. In this post, I will cover the decision-making process that allows you to choose the tools and the right approach for your organisation or your clients.
Direct vs pipelines vs data warehouse: the three architectures explained
Besides reports and analysis you can already run in the applications, a Business Central reporting architecture usually follows one of three approaches. Direct connection means using another application such as Power BI or Excel to read and display Business Central data. With this method, the connection is via APIs. Data pipelines are scheduled processes that extract and prepare data, which is then loaded into a semantic model in Power BI, often through a dataflow. Using a data warehouse or lakehouse means the scheduled process that uses data pipelines loads the data into a separate analytical store first, which can be used with other BI tools like Power BI or Tableau.
The terminology here can get confusing. Let’s get some of the terms right: A data pipeline is the process that extracts, transforms, and loads data for analytics. A data warehouse is an analytical database or lakehouse built for reporting.
If you use Power BI, you can build and maintain data pipelines in it, and use Power BI dashboards to display and distribute reports. That means you need one tool for two jobs. Conversely, using a data warehouse requires more tools: the data pipeline that extracts and transforms the data, the warehouse itself, where the data is stored and possibly enriched with advanced data engineering tools, and the BI tool that displays and distributes the data.
You can run pipelines without a warehouse when Power BI data crunching tools are enough. You almost never want a warehouse without pipelines, because a warehouse that is not refreshed on a schedule is only a copy of an old export.
| Architecture | How it works | Use case |
|---|---|---|
| Direct connection | Power BI or Excel reads Business Central through the connector and APIs | One or more companies, mainly Business Central and Dataverse, for operational and analysis work |
| Pipelines plus Power BI | A scheduled process moves and prepares data into Power BI | Scheduled data refresh and shared dashboards with data from multiple sources |
| Pipelines plus warehouse / lakehouse | Pipelines load data to a warehouse or lakehouse; Power BI and other tools read from it | Multi-tenant, multi-system reporting, large history, shared definitions across tools, data engineers need high-performing data crunching tools |
Direct connections
In a direct connection, Power BI Desktop or Excel reads Business Central without a separate extract platform between them. The report or workbook queries Business Central and holds the model.
In Power BI Desktop, you choose Get Data, then Online Services, then Dynamics 365 Business Central. After sign-in, the Navigator lists environments and companies; choose one company for each query. You can add more companies later. Under each company, Navigator lists Advanced APIs, Standard APIs v2.0, and Web services (legacy). Prefer Standard APIs v2.0 for master data, transactions, and setup data. Microsoft discourages the legacy web services folder for new work. The flow is documented in Building reports in Power BI Desktop to display Business Central data.
For Business Central online, new Power BI reports read from a secondary, read-only replica of the database, so the report is not hitting the same database that posts invoices and journals. Each query in Navigator is one company. To report on more than one company, add another query and combine them in Power Query when the signed-in account has access to those companies.
The new Power BI apps for Business Central also use a direct API-based connection with a semantic model and scheduled refresh. They do not use the kind of custom data pipelines (ETL with staging, BACPAC historical loads, explicit delta logic on SystemModifiedAt/lastModifiedDateTime, etc.) that are recommended when you need more control, larger volumes, or a reusable intermediate data store.
This keeps them simple to install and use, at the cost of less flexibility compared with a fully engineered pipeline architecture.
Data pipelines
A data pipeline is a scheduled process that reads Business Central, prepares the data on every pipeline run, and loads it into Power BI or another tool. The destination can be a Power BI dataflow. You do not need a warehouse for that.
Microsoft describes this as Extract, Transform, and Load (ETL). This method uses staging tables as an optional holding area while the data is prepared. On Business Central online, you typically load history once, then only the rows that changed. A common way to get the first full copy is a BACPAC export from the admin centre, restored to Azure SQL Database or SQL Server. After that, the process reads APIs and filters on SystemModifiedAt (shown in APIs as lastModifiedDateTime). For Business Central online, APIs are the supported way to read this data. Direct SQL against the production database is not. This is documented in Extract data from Business Central.
Data warehouse
A data warehouse is a database you keep beside Business Central so analysis does not run on the same database where users work to register transactions and post documents. This architecture, which keeps data processing and data extraction separate, typically gives better query performance. Tables in a data warehouse are organised for queries, history, and joins across systems. A lakehouse is the same idea with files in a data lake that reporting tools can query as tables. Power BI, Excel, or Tableau then display the data from the warehouse.
Microsoft Fabric is the Microsoft product that packages this third approach: OneLake, a lakehouse, and Power BI in the same tenant, plus features for data engineering and data science on those tables. Azure SQL and Snowflake are other examples of data warehouse products. In every case, you still need a pipeline that reads Business Central through APIs. You can read more about Fabric here: Introduction to Microsoft Fabric and Business Central.
When to use direct connections?
Use a direct connection between Business Central and Power BI or Excel when you need to build reports for operations or management.
That is the first option I listed at the start of this post, and it is the approach I recommend for most small organisations that need simple operational and analysis tools when all the data they need is in Business Central. If the available reports and queries are not enough, you can build more models and publish them to the Power BI service. A Business Central Power BI architecture of this kind can usually be done in a few days of work.
You will outgrow a direct connection when reports get slow, when the same models are copied into several Power BI files saved locally, or when dashboards must be shared on a schedule and the data comes from more than Business Central. Those are issues solved with data pipelines, not necessarily a data warehouse problem.
When do you need data pipelines?
You need data pipelines when dashboards are shared in the organisation and the data has to be prepared once, on a schedule, often from more than one source.
This is the middle option: more configuration than a direct connection. Shared dashboards in the organisation need Power BI Pro; Premium or Fabric capacity is needed for higher scale, more frequent refreshes, or larger models. You can still do both jobs in Power BI. A dataflow prepares the tables; a semantic model and the dashboards sit on top. You write the transform rules in the Power Query editor once and publish them in the organisation workspace. Each run can load only the rows that changed. On Business Central online, typical API reads are around 2 MB per second per call, and current parallelism is five calls at a time.
Stay on this option while Power BI can still crunch the data.
When do you need a warehouse?
You need a data warehouse when you need one source of truth for historical data, several companies, or data from other systems, and more than one tool will read those tables. That is the most complex option: months of work and extra licences. Pipelines load the tables into a Business Central Power BI data warehouse; Power BI then reads that store.
Can you use Non-Microsoft pipelines and data warehouse tools?
Yes. For Business Central online, Microsoft supports this kind of read through standard or custom APIs. The pipeline and the warehouse can be Microsoft or non-Microsoft products.
Costs, security and governance decisions
As I said in the opening paragraph, Power BI Desktop and a personal workspace often need no extra spend. Installing and sharing the Business Central Power BI apps, and most organisational use, needs Power BI Pro. Premium or Fabric capacity is needed for higher scale, more frequent refreshes, or larger models. Building Power BI pipelines needs a few extra days or weeks. A complex architecture with a data warehouse is months of work and tens to hundreds of thousands.
On security, Business Central permissions still decide what an ETL application can read, and APIs only expose what you publish. With Power BI pipelines, the security strategy should set who can refresh a semantic model and who can only open the app. With a data warehouse, you add another set of roles and row-level rules, which can be managed via Microsoft Entra and security groups if you use Microsoft tools.
What architecture is best for AI?
A data warehouse or lakehouse is generally preferred for advanced AI workloads that need clean, structured Business Central data at volume. Business Central Fabric reporting puts that store in the same tenant as Power BI and adds data engineering and data science on those tables.
In summary
Start with the analytics already in Business Central, then use a direct Power BI connection while that is enough, add pipelines when preparation and sharing are essential, and use a data warehouse only when complexity scales.