A data warehouse fed from Business Central for reporting at scale

When a Business Central data warehouse is worth building

Many companies never need one. Power BI connected to Business Central through its standard APIs can carry a single company with moderate volumes. It needs a well-built semantic model, but that is all. A warehouse earns its place when that direct route starts to strain. We would rather tell you that early than build something you do not need.

The usual triggers are several companies or environments to combine, or history from a retired system that must sit beside current figures. Others are data from a CRM, a web store or a production system to join in, or refreshes that run so long they collide with the working day. We ask about these before we propose anything.

How data leaves a Business Central data warehouse source

In the cloud, there is no SQL connection to the production database. Data comes out through web service endpoints. These are the standard API pages, custom API pages written in AL for tables the standard set does not expose, and API queries that pre-join and total on the server.

For larger volumes, an extension can export changed records as files to Azure storage. A lake or Microsoft Fabric then picks them up. You can also send read-only requests to a database replica, so they do not compete with users who are posting. We choose the route by volume and by how fresh the numbers need to be.

Modeling ledgers and dimensions in a Business Central data warehouse

Business Central stores history in ledger entry tables: general ledger, customer, vendor, item and value entries, among others. These become the fact tables. Accounts, customers, items, locations and dates become the dimension tables that slice them. Every fact row keeps its entry number, so you can trace a figure back to the posting behind it.

The part that needs thought is Business Central dimensions. Global and shortcut dimensions sit on the entries. But the full set lives in dimension set entries, and you must unpack it into columns. Do that once in the warehouse, and every report benefits. Do it separately in each report, and the results drift apart.

What our Business Central data warehouse work delivers

You receive a working pipeline with incremental loading, so each run fetches only the records that changed since the last one. You also get a documented model and a first set of Power BI reports built on it. We set refresh schedules around your posting routines and the update window of your environment.

We also hand over the plain documentation that keeps a warehouse alive. It shows which table feeds which, how each measure is defined and what to check when a load fails. Before anyone is asked to trust a dashboard, we reconcile totals against the trial balance and inventory valuation in Business Central.

Pitfalls in Business Central data warehouse projects

Extracting whole tables every night works on day one and fails as history grows. The service throttles requests, so a pipeline must page through results and retry politely. It should also rely on change tracking columns. Deleted records need handling too, because an incremental load never sees a row that has gone.

The other trap is rebuilding Business Central logic outside it. Inventory cost, for instance, settles through cost adjustment. A report that recalculates it on its own will disagree with finance. So we take the posted values as the source of truth. We keep clever arithmetic out of the pipeline.

Business Central data warehouse questions

Can we connect straight to the Business Central online database?

No. Microsoft manages the database and does not expose it. You read data through APIs, OData web services or an export built as an extension. On-premises installations do allow SQL access, and that changes the design a lot. So the first thing we ask is whether you run online or on-premises Business Central.

Is a data warehouse the same as Power BI?

No. Power BI is the reporting and modeling tool your users see. A warehouse is the store underneath, which holds cleaned, combined history. Small setups can skip the warehouse. Larger ones put Power BI on top of it. A Business Central data warehouse and Power BI work best as a pair.

Which platform do you build on?

Most of our clients are already in the Microsoft cloud. So Azure SQL, Azure storage with a lake or Microsoft Fabric are the natural choices. We recommend one after we see your volumes and your other systems. We also weigh the skills of the team that will own it, because they will run it after we leave.

Will extracting data slow Business Central down for users?

It should not, when the pipeline is built properly. Incremental loads keep requests small. We schedule heavy runs outside busy periods. You can also send read-only requests to a replica of the database instead of the primary. As a result, users who post orders and invoices see no slowdown during a refresh.

Do custom fields added by extensions come across?

They can. Table extensions add fields that are not on the standard API pages. So we expose them through custom API pages, or we include them in the export. We list them during design, so none are missed. Your reports then carry the custom fields beside the standard ones, with no gaps.

Reporting and analytics services

A warehouse is one layer of a reporting setup. The pages below cover the layers around it: Power BI models, dashboards, the API work that feeds them and analysis inside Business Central itself. Read them to see where a Business Central data warehouse fits. Then we can scope the extraction and the model for your own data. For the product detail, see Business Central documentation on Microsoft Learn.