Report performance is one of the critical success factors in implementing BI solutions. We could achieve excellent performance if we make a good data model. I have provided many Cognos performance tuning solutions. While we tune SQL/MDX for Cognos Analytics, we should look into DAX for Power BI . This short document is intended to list the most popular solutions for Power BI performance tuning:
3 Utilities to identify performance bottleneck
1. SQL SSMS profile if use SQL server: Track SQL statement. (Azure SQL or synapse are not supporting SQL profile as of now )
2. DaxStudio: Display Storage Engine queries.
3. VertiPaq Analyzer (DaxStudio - Advanced - View Metrics): Display data and column size.
Query folding to reduce work load at Power BI engine.
The idea behind Query Folding is to push the logic that you built into a Power BI query back to the data source server and execute it there in it's native language instead of doing a client side transform of the data.
This is a similar concept in Cognos to avoid local process, along with minimized SQL. When connecting a database, let maximize usage of the database while minimizing using the Analysis engine.
- If direct query, then has a significant impact on the report performance.
- If import, then there will a huge impact on data refresh performance.
Solutions at the model level:
Reduce the number of columns by removing unique columns
Change left outer join to inner join in the model.
Use join with integer if possible.
Avoid using big dimensions.
Remove default DateTime intelligence
Avoid composite mode with import and direct query - mixed storage model
Write better DAX calculated measure
Solution at report level
query reduction when using direct query
reduce number of queries by reducing number of visuals.
Reference
No comments:
Post a Comment