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 you really need to see every single row?

Power BI Aggregate table

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

To speed up processing, you can now pre-create and store aggregations of your data, as the rest of this blog explains.

Our example

Here is the pre-made model we will use:

Power BI Aggregate table

All the tables here are currently set to use Direct Query, meaning that there is no intermediate data model (reports speak directly to the underlying tables).

Creating aggregations

The Purchase agg table is a duplicate of the Purchase Raw one, 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.  For example, will the total units sold be averaged? If so, will they be averaged for each product sold?

Power BI Aggregate table

Here we've chosen to group by CentreID , sum by price and count by the purchase id field.

Our goal is to reduce the number of queries being run, 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 (on the Properties pane) to Import. Note that aggregate tables are automatically hidden.

 

Testing performance

We can put our change to the test by using the Performance analyser under the View tab, using a simple table as a test:

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 the query without an aggregate table. Refreshing in rapid succession will often lead to a slight time reduction due to caching.

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 with and without an aggregate table once again shows that the aggregate table wins:

With aggregate table ... ... and without

This works because the sales are already grouped by 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 query against the non-aggregated table. This leads the DAX query execution to be around the same, as they perform the same action.

Interestingly when both data models use the non-aggregated table, the data model without an aggregate table seems to execute faster!

This blog has 0 threads Add post