Automatic aggregation in Power BI

[Kumail]

In this post, we are going to discuss the “Automatic aggregation in Power BI”. But before proceeding let’s discuss the term “Aggregation”.

What is Aggregation?

In simple words, aggregation means a cluster of things that have come or been brought together. But if we talk about Data aggregation it is simply compiling of information from databases with the intent to prepare combined datasets for data processing.

Why choose automatic aggregation in Power BI?

Automatic aggregations will help unlock huge datasets for interactive analysis, enhance query performance, and enable interactive analysis over big data. Automated aggregation tables will be automatically produced in Power BI Premium datasets in Direct Query mode. The aggregation tables will be based on practice and query patterns. They'll be automatically maintained as a self-learning, self-optimizing, AI-driven system. Power BI queries generated when users interact with report visuals will automatically use the aggregation tables. This feature will provide enhanced performance and user concurrency to unlock massive datasets for interactive analysis.

Benefits of using Automated Aggregation:

                                   

Dissimilar to 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. They enable dataset owners to improve query performance, providing faster report visualizations for even the largest datasets.

With automatic aggregations:

• Report visualizations are faster - An optimal percentage of report queries are returned by an automatically maintained in-memory aggregations cache instead of backend data source systems. Outlier queries that cannot be returned by the in-memory cache are passed directly to the data source using Direct Query.
• Balanced architecture - When compared to pure Direct Query mode, most query results are returned by the Power BI query engine and in-memory aggregations cache. Query processing load on data source system peak reporting times can be significantly reduced, which means increased scalability in the data source backend.
• Easy setup - Dataset owners can permit automatic aggregations and schedule one or more refreshes for the dataset. With the first refresh, automatic aggregations take over and begin creating an aggregations framework and optimal aggregations. The system automatically refreshes itself over time.
• Fine-tuning – With a simple and user-friendly interface in dataset Settings, you can estimate the performance gains for a different percentage of queries returned from the in-memory aggregations' cache and make adjustments for even greater gains. A single slide bar control helps you easily fine-tune for your environment.

So, how many of you are using Automatic Aggregations in Power BI or intend to use this special feature in Power BI?

All of you. Great!!

Other useful resources for setting up Automatic Aggregations in Power BI;

- https://docs.microsoft.com/en-us/power-bi/admin/aggregations-auto-configure