Forum Discussion
Helper VLoad Large data from business central
Hi, i have a table that i want to get from business central to power bi, the table has around 30m+ rows, which are going to add up between 100-200k daily when its published to production. i tried multiple ways after researching but could reach to a solution:
- BC2ADLS:
at first, i was able to extract all my rows in a very fast and efficient way, after going through the documentation for BC2ADLS on github, but then after we tried refreshing on business central to update our data, we kept getting the same error exactly after 30 minutes of each refresh. we checked with the extension creator on github and he told us that the data is probably too much.
-Dataflows (fabric):
i tried getting the data in the very beginning using dataflows. i created a dataflow in a fabric workspace and linked it to a lakehouse.
i tried to load that large table, and i kept getting a timeout error. i saw that turning on fast copy makes the data extraction with dataflows very fast and efficient, unfortunately, fast copy is not supported for business central as a source.
-dataverse:
i tried getting data from that table published on dataverse, i used a pipeline and gave it a 24 hours timeout, the data was extracted within around 10 hours, all 30m rows, so i was researching on how to apply incremental load on data pipelines, and after we tried refreshing the table on dataverse, we received an error from dataverse and the table could not be loaded anymore.
i saw on a reddit post that some are just deploying their table on an SQL server or azure, then taking the data from sql or azure using dataflows since they support fast copy and as they said are faster in general and better.
what other options do i have to be able to load all my data? and is deploying my table to an SQL server a good option actually?
Hi Hussein_charif ! Importing a 30 million row table from Business Central to Power BI requires a really large-scale data integration. The methods you've tried are quite viable, but each has its limitations. Below I've summarized both why the current methods are problematic and alternative solutions:
📌Problems of Existing Methods:
BC2ADLS (Business Central to Azure Data Lake Storage)While fast and efficient, it is not directly supported by Power BI.
The refresh process, which is interrupted at the minute, is most likely stuck in Power BI's data ingestion time limit.
Fabric DataflowsIt is normal that you are successful on the first load because there may be a cache effect.
However, the 1-hour timeout is a common limitation for large datasets.
Also, Fabric Dataflows currently has limited support for sources other than CSV.
DataverseWhile Dataverse is powerful for data modeling, it is not optimized for large data loads.
With data sets as large as 30 million rows, timeout and performance issues are expected.✅ Alternative and Recommended Solution: Azure SQL Database or Azure Synapse Analytics
Yes, a much better solution would be to export your data to an Azure SQL database or big data platform like Azure Synapse.
These platforms
Supports large data loads (fast load, partitioning, indexing).
Works seamlessly with Power BI in DirectQuery or Import mode.
Performance can be optimized with features such as scheduled data updates and incremental refresh.🔄Recommended Flow:
Business Central → Azure Data Factory (ADF) → Azure SQL DBWith ADF you can pull data piece by piece (batch) and write it to SQL.
This process can be scheduled and parallelized.
Azure SQL DB → Power BIBy defining incremental refresh in Power BI, you can pull only changed data.
This significantly improves performance.🎯 Extra Tips:
You can improve query performance by using partitioning in Azure SQL.
You can improve report performance by defining aggregation tables in Power BI.
If cost is an issue, consider flexible solutions such as Azure SQL Hyperscale or Serverless SQL Pool (Synapse).
10 Replies
- burakkaragoz
Super User
Hi Hussein_charif ! Importing a 30 million row table from Business Central to Power BI requires a really large-scale data integration. The methods you've tried are quite viable, but each has its limitations. Below I've summarized both why the current methods are problematic and alternative solutions:
📌Problems of Existing Methods:
BC2ADLS (Business Central to Azure Data Lake Storage)While fast and efficient, it is not directly supported by Power BI.
The refresh process, which is interrupted at the minute, is most likely stuck in Power BI's data ingestion time limit.
Fabric DataflowsIt is normal that you are successful on the first load because there may be a cache effect.
However, the 1-hour timeout is a common limitation for large datasets.
Also, Fabric Dataflows currently has limited support for sources other than CSV.
DataverseWhile Dataverse is powerful for data modeling, it is not optimized for large data loads.
With data sets as large as 30 million rows, timeout and performance issues are expected.✅ Alternative and Recommended Solution: Azure SQL Database or Azure Synapse Analytics
Yes, a much better solution would be to export your data to an Azure SQL database or big data platform like Azure Synapse.
These platforms
Supports large data loads (fast load, partitioning, indexing).
Works seamlessly with Power BI in DirectQuery or Import mode.
Performance can be optimized with features such as scheduled data updates and incremental refresh.🔄Recommended Flow:
Business Central → Azure Data Factory (ADF) → Azure SQL DBWith ADF you can pull data piece by piece (batch) and write it to SQL.
This process can be scheduled and parallelized.
Azure SQL DB → Power BIBy defining incremental refresh in Power BI, you can pull only changed data.
This significantly improves performance.🎯 Extra Tips:
You can improve query performance by using partitioning in Azure SQL.
You can improve report performance by defining aggregation tables in Power BI.
If cost is an issue, consider flexible solutions such as Azure SQL Hyperscale or Serverless SQL Pool (Synapse).
Hussein_charifthank you for the reply burakkaragoz , i also found that importing to another database is the only best option when working with a very large dataset from business central.
- burakkaragoz
You're absolutely right — when dealing with very large datasets from Business Central, importing into another database (like Azure SQL or Synapse) is often the most scalable and manageable solution.
Regarding the cost of transition:
- Initial migration can be resource-intensive depending on the volume and complexity of the data.
- Ongoing costs will depend on the chosen platform (e.g., Azure SQL, Synapse, Data Lake) and how frequently the data is refreshed or queried.
- Using incremental loads and partitioning can help reduce both cost and processing time.
- If you're using Azure, consider serverless options or reserved capacity for cost optimization.
- Hussein_charif
i have a question though, how costly do you think the transition would be? considering the very large amount of data
V-yubandi-msftCommunity Support
Hi Hussein_charif ,
Thank you for engaging with the Microsoft Fabric Community. Deploying to SQL Server is a good choice because it supports Fast Copy in Power BI Dataflows, allowing for quicker and more reliable data loads while integrating smoothly with Power BI.
Have you thought about trying the DirectQuery method?
Regards,
Yugandhar
- Hussein_charif
Hi V-yubandi-msft , thank you for your reply.
do you have an idea on how i can transfer my BC data to SQL server?
also no i still haven't tried direct query, do you suggest giving it a try? does it have any limitations considering the data size i have(30m+)?
- V-yubandi-msft
Hi Hussein_charif ,
1. To transfer BC data to SQL, you can use OData along with Fabric Dataflows.
OData Web Services - Business Central | Microsoft Learn
2. A limit of 1 million rows can return on any query.
DirectQuery in Power BI - Power BI | Microsoft Learn
In my opinion, for datasets that exceed 30 million rows, it is advisable to avoid DirectQuery. Instead, use Import Mode with incremental refresh to achieve better speed, flexibility, and scalability.
- V-yubandi-msft
Hi Hussein_charif ,
We haven’t received a response yet and want to ensure the solution met your needs. If you need any further assistance, feel free to reach out we’d be happy to help. If everything is working as expected, kindly mark it as Accepted as solution.
Thank You.
- V-yubandi-msft
Hi Hussein_charif ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- V-yubandi-msft
Hi Hussein_charif ,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.