Has Microsoft created a product (Power BI) I can't use directly for data from Business Central?

[Bokazoit]

We are a small NGO and can atmost produce 7 mio rows for one table. But I can't find a good way to retrieve data from Business Central without it taking like forever. The only solution I have read about for now is to have a data warehouse to stage data, but if that is so, then I can imaging most big business will find Power BI inadequete - not good enough.

What other solutions have You come up with that do not considder the use of a data warehouse for big data and specificly Business Central

[v-karpurapud]

Hi @Bokazoit

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-karpurapud · Accepted solution]

Hello @Bokazoit 

Thank you for reaching out to the Microsoft Fabric Community Forum.

To efficiently connect Microsoft Business Central to Power BI and improve performance with large datasets, consider these best practices:

Instead of pulling raw data directly from Business Central into Power BI, use Power BI Dataflows to extract and store pre-processed data in Azure Data Lake Storage. This reduces data load time in Power BI by performing transformations in Power Query.

Also, consider using Business Central APIs via Power BI's web connector. These APIs are faster and more optimized than OData, and you can use pagination to retrieve large datasets in chunks. APIs allow filtering and selecting only relevant data, reducing the dataset size.

For datasets with millions of rows, incremental refresh is crucial to prevent loading all data every time. It significantly reduces query time by refreshing only new or modified rows instead of the entire dataset, and works well with Business Central when using date-based filtering.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you