At work we use a third-party analytics platform on top of Business Central, and we’ve had a lot of performance issues with it. I kept wondering whether it could be done better, so I started exploring it on my own in my spare time. It turned out it could.
Why it’s slow in the first place
Reporting straight from Business Central goes through OData, which is slow and gets throttled. Microsoft is also deprecating the standard OData queries in 2027, so it’s not something to build on long term. It gets worse if you have several companies. Getting a consolidated P&L or balance sheet usually means manual exports and spreadsheets.
So the idea was to sync the data into Azure SQL on a schedule and point Power BI at the database instead of at Business Central. Every environment and company ends up in one place, and Power BI never has to wait on BC.
How it works
There are three parts.
First, an AL extension inside Business Central exposes 36 custom API endpoints. They cover GL, sales, purchasing, inventory, banking, fixed assets and master data. Every endpoint includes lastModifiedDateTime, so the sync only has to fetch what’s changed.
Second, an Azure Function (C#, .NET 8) runs every 30 minutes. It pulls new and changed records from each endpoint, loads them into a staging table and uses MERGE to upsert them into Azure SQL. It authenticates with the OAuth 2.0 client credentials flow.
Third, the data lives in Azure SQL. I built it multi-tenant from the start: a control database keeps track of the tenants, and each tenant gets its own database with data from all of its BC environments and companies, told apart by EnvironmentName and CompanyId.
Power BI sits on top of that.
Where it stands
The sync pipeline works. Three BC environments sync roughly 27,000 records in about 34 seconds. The next step would be the Power BI reports themselves, starting with the core financials: P&L, balance sheet, trial balance, budget vs actual, and AR/AP aging.
Things I learned
BC encodes option fields as hex sequences in its OData responses, so G/L Account comes through as G_x002F_L_x0020_Account. That one took me an afternoon to figure out.
Going multi-tenant from day one paid off. Keeping tenants in a control database keeps things tidy, and adding a new tenant is two SQL inserts and provisioning a database.