Guangning Yu's Blog
Home
Code
Data
Setup
Industry
MachineLearning
Archive
Power BI Performance Tuning
2022-04-14 11:51:40
# Best Practices ## Import 1. Turn on Query Caching for imported tables 2. Turn on Large Dataset Storage format ## DirectQuery 1. Use star-schema for data modeland ensure that your dimension tables contain proper keys and that those keys relate to a fact table 2. Materialize all aggregations, transformations, and calculations in SQL Server 3. Pre-filter large fact tables in SQL Serverand remove filters in visual - Adding filters will cause Power BI to generate multiple SQL queries 4. Use dynamic M query parameter to move measure logic from Power BI to SQL Server 5. Optimize measure definition to generate efficient DAX queries - Power BI will translate complex DAX into multiple SQL Server queries 6. Use dual mode for dim tables - Otherwise Power BI will use import dim tables to generate long SQL Queries for filters 7. Refine table structure and indexing strategy - Clustered rowstore (like by TimeId) + non-clustered columnstore is recommended 8. Only select required columns from SQL Server in table definition ## Visual 1. Use Query Reduction and add the “Apply“ button 2. Carefully think about the number of visuals on the report page — the more visuals, the longer time needed for data retrieval # Power BI Embedded 1. Gen2 has fewer memory restrictions. Gen2 prioritizes CPU over memory. 2. Backend cores are the ones that have the limit on it. 3. Each dataset is bound to the memory limit of the given capacity. For example, for A4/P1, it's 25GB per dataset; dataset refresh is half the limit (i.e. 12.5 GB for A4/P1). # Useful links 1. [Power BI Premium Query Profiling with DAX Studio and SQL Profiler](https://www.youtube.com/watch?v=-dqt2-xpJd4)
Previous:
Setup OpenVPN Command Line on Ubuntu
Next:
Notes | AZ-900 Microsoft Azure Fundamentals
