Extract data from Business Central

A common problem that organizations face is how to gather data from multiple sources, in multiple formats. Then you'd need to move it to one or more data stores. The destination might not be the same type of data store as the source. Often the format is different, or the data needs to be shaped or cleaned before loading it into its final destination.

Various tools, services, and processes have been developed over the years to help address these challenges. No matter the process used, there's a common need to coordinate the work and apply some level of data transformation within the data pipeline. The following sections highlight the common methods used to perform these tasks.

Extract, transform, and load (ETL) process

Extract, transform, and load (ETL) is a data pipeline used to collect data from various sources. It then transforms the data according to business rules, and it loads the data into a destination data store. The transformation work in ETL takes place in a specialized engine, and it often involves using staging tables to temporarily hold data as it is being transformed and ultimately loaded to its destination.

When establishing a data lake or a data warehouse, you typically need to do two types of data extraction:

1. A historical load (all data from a given point-in-time)
2. Delta loads (what's changed since the historical load)

Getting a historical load

For a database-level historical load from Business Central online, you can export a BACPAC file from the admin center and restore it in Azure SQL Database or SQL Server. For on-premises installations, you can take a backup of the tenant database. If Fabric is your destination, consider the managed initial snapshot described in Microsoft Fabric data mirroring.

Learn more in Exporting databases in the admin center and Create a full database backup (on-premises).

Getting delta loads

For API-based delta extraction from Business Central online, configure API pages or queries to use read scale-out. Filter results on a modification timestamp, such as SystemModifiedAt, when the API exposes it as lastModifiedDateTime. This approach retrieves added or modified records; it doesn't identify deleted records. For managed synchronization to Fabric, consider Microsoft Fabric data mirroring.

1. Enable read scale-out on the Business Central database.

   In the Business Central online service, read scale-out is automatically enabled on the databases. Learn how to enable read scale-out for on-premises installations in Configuring database for read scale-out.

2. Configure your API page/query to run with read scale‑out.

   page 50123 "Customer Changes API"
   {
       PageType = API;
       DelayedInsert = true;
       SourceTable = Customer;
       APIVersion = 'v2.0';
       APIPublisher = 'contoso';
       APIGroup = 'customers';
       EntityName = 'customerChange';
       EntitySetName = 'customerChanges';
       Editable = false;
   
       // Key for persistent id in API; use SystemId on modern BC
       ODataKeyFields = SystemId;
   
       // Route GETs to read-only replica where possible
       DataAccessIntent = ReadOnly;
   
       layout
       {
           area(content)
           {
               field(systemId; Rec.SystemId) { }
               field(no; Rec."No.") { }
               field(displayName; Rec.Name) { }
               field(lastModifiedDateTime; Rec.SystemModifiedAt) { Caption = 'lastModifiedDateTime'; }
               // add any fields required downstream
           }
       }
   }
   

   Learn more about API pages in API page type.

3. Filter the API request using the lastModifiedDateTime field.

   GET https://{bc-server}:{port}/{instance}/api/contoso/customers/v2.0/companies({CompanyId})/customerChanges
   ?$filter=lastModifiedDateTime gt 2025-12-01T00:00:00Z
   &$orderby=lastModifiedDateTime asc
   &$top=1000
   

Also consider this pattern: For each company, start by determining which tables that you load data from have new data. You can do this in an AL codeunit that you expose as a web service endpoint. Based on the result of this initial call, only call the APIs that you know will return data. This practice reduces the number of API calls you need to issue for your incremental loads. The impact of this optimization depends on the distribution of new data since last load.

Tools for reading data

For Business Central on-premises, you can just read directly from the environment database. This option isn't available for Business Central online, so establishing this type of integration could block you from migrating from on-premises to the online version.

For Business Central online, use one of the following supported options:

• Microsoft Fabric data mirroring for a managed connection that mirrors selected companies and tables to an open mirrored database in Microsoft Fabric.
• Standard or custom Business Central APIs for transactional integrations and custom ETL pipelines.

There exists a code sample that you can use to be more productive with APIs. Learn more in API query generator

Microsoft Fabric data mirroring

The Microsoft Fabric Mirroring (Preview) app connects selected Business Central companies and tables to an open mirrored database in Fabric. The integration supports an initial snapshot followed by incremental changes. In Fabric, the data is available in OneLake in Delta Parquet format for use with Power BI, Data Factory, and other Fabric workloads.

Use data mirroring when:

• You want a managed, continuous synchronization process instead of building an API extraction pipeline.
• You need data from multiple Business Central companies.
• Fabric is the destination for analytics or data engineering.
• You want to monitor synchronization from Business Central, APIs, or Fabric.

The app supports up to 1,000 selected tables across manual selections and active configuration packages. The table lookup excludes external, linked, virtual, and certain internal sensitive tables.

Connection setup and synchronization are separate operations. An administrator configures authentication, grants workspace access, and selects Connect to Fabric. After setup succeeds, Start synchronization requests continuous export. Finishing the setup guide doesn't start synchronization.

The Fabric Export APIs manage configuration and expose monitoring results. They don't expose the selected business tables as API entities. The API throughput estimates in the following section don't describe mirroring throughput.

For setup and administration, see Data mirroring to Microsoft Fabric from Business Central. For programmatic monitoring and configuration, see Microsoft Fabric Export APIs.

Throughput of data reads with APIs

Measurements show that it isn't unusual to be able to read 2 MB/sec per API call. This rate means that it's possible to transfer up to 120 MB/min or 7200 MB/hour for pipelines running sequentially.

In Business Central online, the current parallelism for API calls is 5. This value means that you can read up to 35 GB/hour if no other processes call web services on Business Central. For an ETL setup that updates the staging area nightly with yesterday's changes, this rate should fit most maintenance windows (unless the environment has several GB of new/updated data per day).

Even if the ETL setup reads the historical dataset before switching to incremental loads, with a 2 MB/sec throughput, it takes up to one day to load a 100-GB database. Also, this operation is something you only do once.

Stability of ETL pipelines

No matter which tool you choose, you must make your data pipelines robust towards timeouts and design them so that they're rerunnable. All Business Central tables have system fields SystemRowVersion and SystemModifiedAt. Learn more about system fields in About system fields. If your ETL setup tracks which watermark (either a date or a rowversion) was read last time, then data pipelines can utilize this behavior to read changes since the watermark.

ETL tools

You can use your ETL tool of choice to read and transform data. Read about some popular options in the sections that follow.

SQL Server Integration Services (SSIS)

For Business Central on-premises, one popular tool of choice is SQL Server Integration Services (SSIS) that is shipped with SQL Server. It's possible to autogenerate SSIS packages from a metadata store using script tasks or a tool such as Business Intelligence Markup Language (BIML). Learn more about these tools in About BIML and About SSIS.

Azure Data Factory

For Business Central online, you can use a tool such as Azure Data Factory to read and transform data. Azure Data Factory is a managed cloud service that is built for complex hybrid extract-transform-load (ETL), extract-load-transform (ELT), and data integration projects. Learn more about Azure Data Factory in About ADF

You can use the Azure Data Factory OData connector with service principal authentication to extract data from Business Central. Learn more in Azure Data Factory OData connector and How-to connect Business Central to Azure.

Power BI dataflows

It's also possible to use Power BI dataflows for your extract pipelines. With Power BI dataflows, you can connect to Business Central APIs and utilize incremental refresh to only load data that was changed since last refresh. Learn more in About incremental refresh and About Power BI dataflows.

bc2adls code sample (unsupported)

Another (unsupported) option is to use the bc2adls code sample to transfer data from the Business Central Server directly to an Azure Data Lake Storage (ADLS) data lake. For more information about bc2adls in About bc2adls on GitHub.com.

Related information

Adding Power BI Report parts to pages
Using read scale-out for better performance
Using filters with OData/API calls