Aggregations for petabyte-scale BI available in the Power BI service

Possibly the biggest scalability feature (literally) in the history of Microsoft BI is actually here! The October Power BI Desktop feature summary announced aggregations built on composite models is now supported in the Power BI service. It works on both shared (Pro) and Premium capacity.

Here is a summary of reasons to use aggregations:

Query performance over big data – as users interact with visuals on Power BI reports, DAX queries are submitted to the Power BI dataset. Boost query speeds by caching data at the aggregated level, using a fraction of the resources required at the detail level. Unlock big data in a way that would otherwise be impossible.

Data refresh optimization – reduce cache sizes and refresh times by caching data at the aggregated level. Speed up the time to make data available for users.

Achieve balanced architectures – allow the Power BI in-memory cache to handle aggregated queries, which it does effectively. Limit queries sent to the data source in DirectQuery mode, helping stay within concurrency limits. Queries that do get through tend to be filtered, transactional-level queries, which data warehouses and big-data systems normally handle well.

Aggregations is in public preview. Here are the remaining items before we make it generally available.

Incremental refresh with aggregations/composite models is not supported in the Power BI service just yet. It will be soon. [Update: As of November 2018, aggregations combined with incremental refresh in the service works for SQL (Azure SQL, Azure SQL DW, SQL Server, APS), Oracle and Teradata data sources.]

Datasets are currently disallowed from creating aggregations with RLS in the same dataset. We are actively working on allowing this.

Here is a recording of the trillion row demo. It shows how aggregations enable “clicky clicky draggy droppy” data analysis over massive datasets. We’d like to remind everyone that you don’t actually need a trillion rows to make use of this feature! Any dataset that is expensive to refresh in terms of time, money, memory usage, etc. can likely benefit greatly from aggregations.

This video is a deep dive on how to set up aggregations both for dimensional and big-data models.

Sign up for the Power BI Newsletter

Sign up below to get the latest from Power BI, direct to your inbox!

By clicking Sign up today, you are giving your consent to Microsoft for the Power BI newsletter program to provide you the exclusive news, surveys, tips and advice and other information for getting the most out of Power BI. You can unsubscribe at any time. Microsoft Privacy Statement.

What is Power BI?

Power BI is a suite of business analytics tools to analyze data and share insights. Monitor your business and get answers quickly with rich dashboards available on every device.