Load Large data from business central

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).