Dynamics 365 Business Central Power BI Connector: Step-by-Step Setup Guide (2026)

Dynamics 365 Business Central has a built-in Power BI connector — Microsoft ships it as a first-party integration and it is enabled by default in Business Central Online. For simple reports on small datasets, it is functional. But for finance teams doing meaningful reporting — multi-year GL analysis, consolidated entity reporting, or custom segment breakdowns — the built-in connector has three structural limitations that become increasingly painful as data volume and report complexity grow.

This guide covers the reliable alternative: syncing Business Central data to a SQL database with SyncTools, and connecting Power BI to that database via its native SQL connector — no OData throttling, no AL development required, and no gateway needed for Azure SQL.

TL;DR: SyncTools syncs Dynamics 365 Business Central records to a PostgreSQL, MySQL, SQL Server, or Azure SQL database on an automated schedule. Power BI Desktop connects to that database via its native SQL connector. Power BI Service refreshes on schedule without OData throttling or AL extensions. Full GL entry and transaction line detail included. Flat $50/month, unlimited rows.

Related: Dynamics 365 Power BI Connector landing page — architecture overview, supported entities, and pricing.

Why the Built-In Business Central Power BI Connector Falls Short

API throttling causes slow and failed refreshes. Business Central’s OData API is subject to Microsoft’s service protection limits — approximately 5–7 requests per second per environment. Power BI’s OData connector makes sequential API requests for each entity type in your report. On a report that pulls GL entries, customers, vendors, items, sales invoices, and purchase invoices across three years of history, the refresh can take 15–30 minutes and regularly hits throttle limits that cause partial or failed refreshes. During business hours, when the Business Central environment is under concurrent user load, these limits are reached faster.

Custom data requires AL extension development. The Business Central OData connector can only pull data that is exposed through Business Central’s standard API pages. Standard API pages cover the most common entities but do not include custom fields added by your implementation partner, data from ISV extensions, or complex computed values. Accessing custom data requires a Business Central developer to write AL code, create a new API page, and maintain it through Business Central major version upgrades — a significant ongoing development cost for a reporting requirement.

Live queries impact production performance. Unlike a data warehouse approach, every Power BI refresh hits the live Business Central database through the API. On Premium or higher-volume environments, concurrent Power BI refreshes from multiple reports create measurable API load during business hours. Microsoft’s throttling protects the Business Central service, but it means Power BI refresh reliability degrades exactly when the environment is busiest.

No bulk export. The OData API is page-based — Power BI pulls one page of results at a time, making sequential API calls until it has retrieved all records for an entity. There is no batch export or change-log-based incremental pull. Every scheduled refresh re-reads all records for the date range in scope, page by page, subject to API rate limits throughout.

The database sync approach resolves all of these. SyncTools uses Business Central’s API with built-in throttle handling and incremental change tracking. After the initial historical sync, only new and changed records are pulled into the database — keeping refresh cycles fast and predictable regardless of total data volume.

What Business Central Data Syncs to Your Database

SyncTools structures Business Central data into query-ready SQL tables with full transaction and line-item detail.

Business Central Entity	SQL Tables	Power BI Use Case
GL Entries	gl_entries, chart_of_accounts	Trial balance, P&L, balance sheet
Customers	customers	Customer dimension for AR and revenue
Vendors	vendors	Vendor dimension for AP and spend
Items	items	Product/service dimension
Item Ledger Entries	item_ledger_entries	Inventory movements, COGS
Sales Invoices	sales_invoices, sales_invoice_lines	AR aging, revenue by customer and item
Purchase Invoices	purchase_invoices, purchase_invoice_lines	AP aging, spend by vendor and item
Bank Accounts	bank_accounts	Cash position
Bank Account Ledger Entries	bank_account_ledger_entries	Bank reconciliation
Dimensions	dimensions, dimension_values	Segment dimension (department, project, cost centre)

Dimensions sync as joinable tables. Business Central dimensions (department, project, cost centre, and any custom dimensions) sync as separate dimension value tables with dimension set IDs on transaction lines — enabling full segment-level P&L and cost-centre reporting in Power BI.

GL entries are the source of truth. Every financial transaction in Business Central posts to GL entries. Syncing GL entries gives you a complete, immutable financial record in your database that is always consistent with Business Central’s posted ledger.

Step 1: Connect Business Central to SyncTools and Configure Your Sync

Sign up at synctools.io and select Dynamics 365 Business Central as your source. Authorization uses Azure AD OAuth — SyncTools authenticates with your Business Central environment using the standard API. No AL development, no custom API pages, no additional Business Central user licence required.

In the SyncTools dashboard, configure:

• Which entities to sync: For Power BI financial reporting, the minimum recommended set is GL Entries, Chart of Accounts, Customers, Vendors, Sales Invoices + lines, Purchase Invoices + lines, and Dimensions.
• Company / legal entity: For Business Central environments with multiple companies, each company is a separate sync configuration. All companies can write to the same destination database with a company dimension column.
• Historical date range: Set how far back to sync on the initial run. SyncTools handles Business Central API pagination and throttle backoff automatically.
• Sync schedule: Hourly sync keeps dashboards current. Daily sync suits end-of-day reporting.

Step 2: Configure Your Destination Database

SyncTools supports four destination options:

Azure SQL — The natural choice for Business Central Online teams on Microsoft Azure. Power BI Service reaches Azure SQL directly for scheduled refresh without a gateway, and the entire stack stays within Microsoft Azure infrastructure.

PostgreSQL — Works with cloud-hosted PostgreSQL (Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL). Power BI Service reaches cloud PostgreSQL directly for gateway-free refresh.

SQL Server — On-premises SQL Server requires the standard Power BI on-premises data gateway for cloud-scheduled refresh.

SyncTools-hosted database — Zero infrastructure setup. SyncTools provides a cloud-hosted database with the connection string pre-configured. Best for teams without existing database infrastructure.

SyncTools creates and maintains all table schemas automatically — no DDL, no migration scripts, no AL development to expose new fields.

Step 3: Connect Power BI Desktop to the Database

Open Power BI Desktop → Get Data. Select the connector matching your database:

• Azure SQL: Use the Azure SQL Database connector.
• PostgreSQL: Use the PostgreSQL connector.
• SQL Server: Use the SQL Server connector.

Enter your server hostname, port, and database name from SyncTools. Load the tables for your report. The recommended initial import for Business Central financial reporting:

gl_entries
chart_of_accounts
customers
vendors
sales_invoices
sales_invoice_lines
purchase_invoices
purchase_invoice_lines
dimensions
dimension_values

Build relationships in Power BI’s model view:

• gl_entries.account_id → chart_of_accounts.id
• sales_invoice_lines.sales_invoice_id → sales_invoices.id
• purchase_invoice_lines.purchase_invoice_id → purchase_invoices.id
• sales_invoices.customer_id → customers.id
• purchase_invoices.vendor_id → vendors.id

For dimension reporting, join dimension_values to transaction tables via the dimension set ID column.

Common Business Central Power BI DAX measures:

• Total Revenue: SUM(sales_invoices[amount_excluding_vat])
• Total Purchases: SUM(purchase_invoices[amount_excluding_vat])
• Net Profit: [Total Revenue] - [Total Expenses]
• GL Balance at Date: CALCULATE(SUM(gl_entries[amount]), gl_entries[posting_date] <= MAX(gl_entries[posting_date]))

Step 4: Publish to Power BI Service and Schedule Refresh

Publish your report to Power BI Service from File → Publish. In the dataset settings, configure data source credentials for your SQL database.

For Azure SQL: Power BI Service connects directly. Set your refresh schedule — up to 8 refreshes per day on Power BI Pro, 48 per day on Premium. No gateway needed.

For on-premises SQL Server: Install the standard Power BI on-premises data gateway. Configure the gateway connection in dataset settings before enabling scheduled refresh.

Your Business Central dashboards update automatically on schedule, pulling the latest records SyncTools has written to the database since the previous run — no OData throttling, no AL development, no API calls at refresh time.

Common Business Central Power BI Report Structures

Income Statement (P&L): Filter chart_of_accounts on income and expense categories. Join gl_entries to chart_of_accounts. Pivot by posting date month. Add dimension slicers for department or cost centre P&L.

Balance Sheet: Filter chart_of_accounts on balance sheet account categories. Use gl_entries with a period-end date filter. CALCULATE with LASTDATE for point-in-time balance snapshot.

Trial Balance: Group gl_entries by account, sum debit and credit amounts, calculate net balance. Add period filter for current vs. prior period comparison.

AR Aging: Join sales_invoices to customers, filter for open status (remaining amount > 0), calculate days from due_date to today, bucket into 0–30, 31–60, 61–90, 90+ columns.

AP Aging: Same structure using purchase_invoices and vendors.

Revenue by Dimension: Join sales_invoice_lines to sales_invoices and dimension_values. Pivot by dimension (department, project, cost centre) and month.

Inventory Valuation: Join item_ledger_entries to items. Sum quantity and cost amount by item and location. Calculate current inventory value.

Built-In Business Central Connector vs SyncTools: Side-by-Side

	Built-In Business Central Connector	SyncTools
API throttling at refresh time?	Yes — 5–7 req/s, causes slow/failed refreshes	No — data already in your database
Custom fields and AL extensions needed?	Yes — for any non-standard data	No
Impact on production Business Central performance?	Yes — live queries during business hours	No
Incremental refresh support?	No — full re-read per refresh	Yes — incremental change tracking
Dimensions as queryable tables in Power BI?	Limited	Yes
Works with Tableau, Metabase, Looker?	No	Yes — any SQL tool
Price	Included in Business Central	$50/month flat, unlimited rows

---

Get Started

Start a free SyncTools trial → — no credit card required, setup in under 15 minutes.

Also using NetSuite, QuickBooks, Xero, or MYOB alongside Business Central? SyncTools can sync multiple accounting systems into the same database for a consolidated BI layer.

Related reading:

• Dynamics 365 Power BI Connector overview — architecture, supported entities, and pricing
• Dynamics 365 Tableau Connector — same sync approach for Tableau instead of Power BI
• NetSuite Power BI connector guide — how the database-sync approach works for NetSuite
• QuickBooks Power BI connector guide — same database-sync approach for QuickBooks Online
• Zoho Books Power BI connector guide — database-sync approach for Zoho Books