Forum Discussion

Hussein_charif's avatar
Hussein_charif
Icon for Helper V rankHelper V
1 year ago
Solved

Load 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 mul...
CICD
one lake
  • burakkaragoz's avatar
    burakkaragoz
    1 year ago

    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 Dataflows

    It 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.
    Dataverse

    While 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 DB

    With 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 BI

    By 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_charif's avatar
Hussein_charif
Icon for Helper V rankHelper V
1 year ago

thank 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's avatar
burakkaragoz
Icon for Super User rankSuper User
1 year ago

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.