Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Best Regards.
Solved! Go to Solution.
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
I 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
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
Hello,
Thanks for your feedback.
I will need refresh data two or three times during the work hours!
Using import mode is a good solution? Or the best solution it is create several views in SQL Server and import them?
Best regards.
Hi @bmms
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.
Hi @bmms
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.
Hello,
thanks for your feedback!
I am little bit confused.
You said that i should use "Direct Query", but on one of the sites you shared (zeabra bi) says that using direct query can be very slow!
Should I or shouldn't I use direct query?
Something is escaping me!!
Best regards.
Hi @bmms
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |