You’re offline. This is a read only version of the page.
2,851
Hi Guys
I've been to get Power BI to load data faster and wondering what recommendations are applicable. So far here's some thoughts:
1. Filter columns in transform data step
2. Filter by date range to narrow down fact tables
3. Summarize data at the source
4. Use API data sources instead of pages (not sure if this improves performance)
5.Create separate data sources for different summary levels
I don't know exactly how Power BI apply various filters for example we can pass parameters to the API call to get a reduced dataset would speed up
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 ;-)
Microsoft Idea · Add Business Central as a Data Source in DataFlows (dynamics.com)
hi,
the things you indicated are correct.
if you use BC Cloud, create and publish BC AL Queries
if you use Power BI with BC Onpremise,
create views on SQL Server by forcing read only, the performance difference is impressive.
nice post here about best practices (general)
#1
Sohail Ahmed
807
#2
YUN ZHU
764
Super User 2025 Season 1
#3
Mansi Soni
529
Share
Report
All responses (
Answers (
