Forum Discussion
How to avoid poor performance when the data base has millions of records
Hello Everyone,
very soon i will build a PowerBi report based on data base in SQL Server with million of records.
But, I am concerned with performance!
In your opinion:
- What is the best way for built this report?
- What is the best way to get data from SQL Server?
- Which things i need be carefull for avoid poor performance?
Best Regards.
You should not use Direct Query if you can avoid it. Import mode is more performant. Here are some key things to consider:
- Use import mode
- Create a star schema data model
- Only bring in the data you need (remove any unneeded columns/rows)
- Avoid DateTime and Decimal columns (split into Date and Time, used Fixed Decimal)
- Leverage incremental refresh to minimize refresh times
Pat
7 Replies
ppm1Solution Sage
You should not use Direct Query if you can avoid it. Import mode is more performant. Here are some key things to consider:
- Use import mode
- Create a star schema data model
- Only bring in the data you need (remove any unneeded columns/rows)
- Avoid DateTime and Decimal columns (split into Date and Time, used Fixed Decimal)
- Leverage incremental refresh to minimize refresh times
Pat
- AnkitKukreja
Super User
Hi Anonymous
You can create a single view using joins and then call in a single table or connect to multiple tables would require you to build the relationship in PBI. So that depends on your requirement, both of them won't have any effect on your model performance.
miTutorialsI just made a tutorial today talking about dealing with million records and things to do to speeden up your report. Hope this helps.
Reasons why your Power BI report is slow & how to optimize | Performance Tuning | MiTutorials
- AnkitKukreja
Hi Anonymous
It's best to do most of the transformation on the source (create a view for that) and connect directly to the source using Direct Query. You can refer few of the posts below for more clarity.
https://www.youtube.com/watch?v=_zYvybVMk7k
https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-large-models
https://learn.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
I hope this helps.
- AnkitKukreja
Hi Anonymous
Sorry for the confusion. You can fall back on the DQ approach if you need the most recent data, if you can wait for a few hours then Import mode would be best. I agree with the points shared by ppm1.
