Aggregations In Power BI

Teo Lachev takes us on a tour of aggregates in Power BI:

During the “Building a data model to support 1 trillion rows of data and more with Microsoft Power BI Premium” presentation at the Business Applications Summit, Microsoft discussed the technical details of how the forthcoming “Aggregations” feature can help you implement fast summarized queries on top of huge datasets. Following incremental refresh and composite models, aggregations are the next “pro” feature that debuts in Power BI and it aims to make it a more attractive option for deploying organizational semantic models. In this blog, I summarize my initial observations of this feature which should be available for preview in the September release of Power BI.

Aggregations are not a new concept to BI practitioners tackling large datasets. Ask a DBA what’s the next course of action after all tricks are exhausted to speed up massive queries and his answer would be summarized tables. That’s what aggregations are: predefined summaries of data, aimed to speed queries at the expense of more storage. BI pros would recall that Analysis Services Multidimensional (MD) has supported aggregations for a long time. Once you define an aggregation, MD maintains it automatically. When you process the partition, MD rebuilds the partition aggregations. An MD aggregation is tied to the source partition and it summarizes all measures in the partition. You might also recall that designing proper aggregations in MD isn’t easy and that the MD intra-dependencies could cause some grief, such as processing a dimension could invalidate the aggregations in the related partitions, requiring you to reprocess their indexes to restore aggregations. On the other hand, as it stands today, Analysis Services Tabular (Azure AS and SSAS Tabular) doesn’t support aggregations. Power BI takes the middle road. Like MD, Power BI would search for suitable aggregations to answer summarized queries, but it requires more work on your part to set them up.

Aggregates in Power BI aren’t as simple as they were in Analysis Services Multidimensional, but they do exist, and hopefully the Power BI team will improve upon them in future versions.

Related Posts

The Power BI Visual Vocabulary

Jason Thomas has put together a great Power BI report: Note that there are some R/Python visuals and currently, R/Python visuals are not available on “Publish to Web”. Hence, I have just used a checkbox on the top of the report to show the images wherever R visuals are used (can be identified by the […]

Read More

Experimenting With Power BI Data Privacy Settings

Chris Webb takes us through some of the intricacies of Power BI data privacy settings and what it means when data sets are Private: Not only does it only show the Package Search endpoint, there is a warning that says:“Some data sources may not be listed because of hand-authored queries”This refers to the output step […]

Read More


August 2018
« Jul Sep »