Going, on prem, directly to the SQL database, using queries/views/stored procedures, is maybe very fast, but not compatible with BC Saas, that's why I would not recommend that approach anymore.
When connecting to BC, use query objects. In Query objects you can filter and aggregate (group by) your data. That is your first and fastest filter possibility.
So create queries that only return the columns you need and only the rows that you need.
Then when importing in PBI you can implement an incremental refresh workaround: How Do I: Implement an Incremental Refresh in Power BI (Free or Pro)? – think about IT
API's are not yet available when you use the Business Central connector in Power BI Desktop, currently only page and query ODATA web services are exposed. Queries should be better then pages, because the SQL statements are better and queries support grouping. API's will become available soon, in the connector, but performance wise I'm not expecting them to be faster, and they might have some stricter limitations on the number of rows they can return. But you never know ;-)
On your Query objects, implement Read Scale-Out: Using Read Scale-Out for Better Performance - Business Central | Microsoft Docs using the DataAccessIntent = ReadOnly; property.
Once you import the data in Power BI, make sure you implement a star or snowflake data model.
Create individual Query objects for every table in your star/snowflake shema. You can reuse them and so build a scalable and reusable solution.
Regarding refresh, test it and see that you schedule refresh outside business hours.
As an alternative, using shared datasets or dafaflows might also decouple the datasets (and refreshing) from the reports and improve performance. This also allows to separately refresh fact tables from other tables.
You can also implement parameters in your PBI reports and use them as filters during import. For example filter on a specific company.
Furthermore, you can also vote for these ideas ;-)
Add Business Central as a Data Source in DataFlows:
Microsoft Idea · Add Business Central as a Data Source in DataFlows (dynamics.com)
Share
Report
2,484
All responses (
Answers (
