New features introduced in the July 2019 update of Power BI
Part five of a seven-part series of blogs

There are big changes to conditional formatting this month (you can display icons, and use percent-based thresholds), but the biggest change of all is the release - finally - of the new filter pane.

  1. Changes introduced in the July 2019 Power BI update
  2. The new fllter pane
  3. Adding icons into the table and matrix visuals
  4. Conditional formatting using percentages
  5. Aggregation tables (this blog)
  6. Little updates and changes
  7. Features waiting in preview

For a cumulative list of all of the updates to Power BI Desktop in the last few years see this blog, or have a look at the Power BI courses that we run.

Posted by Sam Lowrie on 02 August 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Aggregation tables

Big data is something of an inevitability in any modern company but do we need to see every single row?

Power BI Aggregate table

A count of the sales would be useful but knowing about the individual transaction from 8 years ago isn't.

Set up

Out of preview this month is the option to create Aggregate tables which will speed up calculations by pre-aggregating data. Here is premade model:

Power BI Aggregate table

All the tables here are currently set to Direct Query, meaning each interaction will rerun the connections.

The Purchase agg table is a duplicate of the Purchase Raw with some extra columns removed that aren't going to be used for aggregation. Now to define the aggregates:

Power BI

Right click on the soon to be aggregate table and choose Manage aggregations.

Now choose how each column will be aggregated, will the total units sold be averaged? If so will the be averaged for each product sold:

Power BI Aggregate table

CentreID is grouped so all the rows for each centre will be aggregated over. Price is set to SUM so all the grouped rows will be totalled.

Our goal is to reduce the number of queries being run and so leaving the aggregate table as a Direct Query won't help:

Power BI Aggregate table

With the aggregate table selected change the Storage mode (Properties pane) to Import. Note that aggregate tables are automatically hidden.

 

Testing performance

We can put the new efficiency to the test by using the Performance analyser under the View tab and a simple table:

Power BI Aggregate table

This visual has the CentreName column from the Centre table and the Quantity from the Raw purchase table.

Dragging Quantity in will automatically create a Sum of Quantity measure but what it does next depends on the presence of an aggregate table:

Power BI Aggregate table

Here are the results of running that query without an aggregate table. Refreshing in rapid succession will often lead to a slight time reduction due to caching.

Then here is the exact same query but where there is an aggregate table within the data model:

Power BI Aggregate table

The DAX query time is significantly lower and this is on just 32,000 rows of a very simple calculation.

Limitations

There are restrictions on when this will work based on the type of aggregation used. Here I've added an extra table in to see how this effects the results:

Power BI Aggregate tables

Each centre can be classified as either a shopping centre, retail park, shopping park or factory outlet.

Comparing the run times of the query via an aggregate table and not returns once again that the Aggregate table wins:

Aggregate table. Direct query

This works because the sales are already grouped to centre and the centre is filtered by the centre type. The original way the aggregate table stored the table was fine but what if it isn't?

Power BI Aggregate table

This measure causes problems as it can't use the already aggregated data.

The quantity above refers to the units sold in each single transaction which our aggregate table has already grouped. This causes an issue:

Using aggregate table Without aggregate table

Since the query needs to work something out on the non aggregated data it has to run a direct query. This leads the DAX query execution to be around the same as they perform the same action.

Interestingly when both data models need to use the direct query, the data model without an aggregate table will execute faster!

This blog has 0 threads Add post