It’s hard to believe but Power BI has now been available for over 10 years (see history)! Over the last few years there have been a number of new features to improve the performance of queries for dashboards and reports, especially for very large datasets, and I wanted to mention those features so you are aware of them in case you need to give your dashboards and reports a speed boost. Some of these features also improve the time is takes to load data:
- Composite models: Allows a single report to seamlessly combine data from one or more DirectQuery sources, and/or combine data from a mix of DirectQuery sources and imported data. So this means you can combine multiple DirectQuery sources with multiple Import sources. I blogged about this in detail at Power BI new feature: Composite models. A good review of Import mode, DirectQuery mode, and Composite mode is at Dataset modes in the Power BI service.
- User-defined Aggregations: Can improve query performance over very large DirectQuery datasets. By using aggregations, you cache data at the aggregated level in-memory (it is similar to what Azure Analysis Services does). I blogged about this in detail at Power BI new feature: Composite models. For more info see User-defined aggregations
- Incremental refreshes: When data is loaded, instead of doing a flush-and-fill (wiping out the entire data and re-loading it again which can be a long process if you have a big dataset), it can be incrementally refreshed, so that only new or updated data is loaded. Power BI Incremental refresh does that and also provides automated partition formation and administration for dataset tables that often load new and updated data. See Incremental refresh and real-time data for datasets
- Hybrid tables (public preview): A hybrid table is a table with one or multiple Import partitions and one DirectQuery partition. The advantage of a hybrid table is it could be efficiently and quickly queried from in-memory while at the same time including the latest data changes from the data source that occurred after the last import cycle. The easiest way to create a hybrid table is to configure an incremental refresh policy in Power BI Desktop and enable the option “Get the latest data in real time with DirectQuery (Premium only)”. More info
- Automatic Aggregations (public preview): Uses state-of-the-art machine learning (ML) to continuously optimize DirectQuery datasets for maximum report query performance. Built on top of existing user-defined aggregations (mentioned above). Unlike user-defined aggregations, automatic aggregations don’t require extensive data modeling and query-optimization skills to configure and maintain. Automatic aggregations are both self-training and self-optimizing. Automatic aggregations are supported for Power BI Premium per capacity, Premium per user, and Power BI Embedded datasets. More info
- Power BI performance accelerator for Azure Synapse Analytics: When turned on in Azure Synapse Analytics, tracks the most utilized Power BI queries in an organization and creates cached views to optimize query performance. This was announced a while back, but not available yet
If you are using Synapse with Power BI, check out Azure Synapse Analytics & Power BI performance.
The post Power BI Performance Features first appeared on James Serra's Blog.
