Power BI cheat sheet

This page provides clear and opinionated guidance for efficiently managing your data in Power BI and SAP Databricks to optimize query performance and create efficient dashboards.

Connect SAP Databricks and Power BI

Best practice	Impact	Docs
Use Power BI parameters when connecting to different SAP Databricks environments	Allows flexibility when connecting to different SAP Databricks workspaces or different SAP Databricks SQL warehouses.	Connection parameters for Databricks
Use SAP Databricks publish to Power BI service functionality	Enables seamless catalog integration and data model sync without leaving the SAP Databricks UI.	Publish to the Power BI service from SAP Databricks

Choose the most appropriate storage mode

Best practice	Impact	Docs
Use DirectQuery for Fact tables and Dual for Dimension tables (not Import)	Generate more efficient SQL queries by using the most suitable storage mode.	Manage storage mode in Power BI Desktop Feature setup tutorial
Prefer DirectQuery over Import whenever possible	Allows you to maintain governance and audibility.	DirectQuery in PowerBI
Use composite models for mixed storage modes	Allows mixed usage of DirectQuery, Dual, Import mode tables, and Aggregation and Hybrid tables.	Composite models in Power BI Desktop
Use hybrid tables for aggregated historical data with real-time data	Enables efficient in-memory queries.	Hybrid tables

Optimize data access

Best practice	Impact	Docs
Use user-defined aggregations	Improves query performance over large DirectQuery semantic models by caching pre-aggregated data.	User-defined aggregations Feature setup tutorial
Use automatic aggregations	Continuously optimizes DirectQuery semantic models by building aggregations based on Query History for maximum report performance.	Automatic aggregations Feature setup tutorial
Use table partitioning or incremental refresh	Allows importing data faster and managing larger datasets, especially for very small, static and performance sensitive (less than 2 seconds) reports.	Tabular model partitions Incremental refresh Feature setup tutorial
Add Apply all slicers and Clear all slicers buttons	Prevents unnecessary queries by leveraging query reduction settings when users interact with report filters.	Apply all slicers and Clear all slicers buttons.
Use Assume referential integrity when defining table relations if referential integrity has been validated in the upstream ingestion	Enables more efficient join strategies in SQL queries.	Set Assume referential integrity

Fine-tune your data model

Best practice	Impact	Docs
If you must use DAX formulas, optimize DAX formulas and avoid large result sets.	Prevents inefficient calculations that lead to deteriorated performance	Best practices to improve model performance
For DirectQuery, check for query parallelization configuration settings	Improves query parallelization and maximizes utilization of SQL warehouse to improve overall performance.	Query parallelization for Direct Query mode Feature setup tutorial

Monitor performance and metrics

Best practice	Impact	Docs
Use Power BI Performance Analyzer to examine report element performance	Identifies the visualization that takes the most time to load and where the bottleneck is.	Use Performance Analyzer
Evaluate the following properties of Power BI semantic models: Maximum connections per data source Maximum number of simultaneous evaluations Maximum number of concurrent jobs MaxParallelismPerQuery	Fine-tunes the performance of the model.	Maximum number of connections Evaluation configuration settings Query parallelization for dataset performance