Business Central is great at running a business. It is not great at reporting across multiple companies. Standard OData queries are slow, throttled, and Microsoft is deprecating them in 2027. I wanted to fix that.
The idea
Pull BC data into Azure SQL on a schedule, then point Power BI at the database instead of BC directly. No throttling, no slow queries, and you can consolidate data from every environment and company into one place.
That last part is the real value. Most BC customers with multiple companies cannot get a consolidated P&L or balance sheet without manual exports and spreadsheets. This solves that.
How it works
An AL extension inside Business Central exposes 36 custom API endpoints covering GL, sales, purchasing, inventory, banking, fixed assets, and master data. Every endpoint includes lastModifiedDateTime for incremental sync.
An Azure Function runs every 30 minutes, pulls new and changed records from each endpoint, and upserts them into Azure SQL using a staging table and MERGE pattern.
The multi-tenant architecture keeps customer data isolated. One control database tracks all tenants. Each customer gets their own database with data from all their BC environments and companies, distinguished by EnvironmentName and CompanyId.
The stack
- BC API extension built in AL, using BC API v2.0
- Azure Functions in C# (.NET 8), timer-triggered
- Azure SQL for storage, one database per tenant
- OAuth 2.0 client credentials flow for authentication
- Power BI for the reporting layer
Where it stands
The sync pipeline is complete and running. Three BC environments sync roughly 27,000 records in about 34 seconds. The Power BI report library is the next phase, starting with core financials: P&L, balance sheet, trial balance, budget vs actual, and AR/AP aging.
What I learned
BC encodes option fields as hex sequences in its OData responses. Something like G/L Account comes through as G_x002F_L_x0020_Account. That was a fun afternoon of debugging.
Building multi-tenant from the start was the right call. The control plane pattern keeps things clean, and adding a new customer is just two SQL inserts and a database provision.