BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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.
- Changes introduced in the July 2019 Power BI update
- The new fllter pane
- Adding icons into the table and matrix visuals
- Conditional formatting using percentages
- Aggregation tables (this blog)
- Little updates and changes
- Features waiting in preview
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.
Big data is something of an inevitability in any modern company, but do you really need to see every single row?
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.
Here is the pre-made model we will use:
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).
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:
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?
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:
With the aggregate table selected change the Storage mode (on the Properties pane) to Import. Note that aggregate tables are automatically hidden.
We can put our change to the test by using the Performance analyser under the View tab, using a simple table as a test:
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:
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:
The DAX query time is significantly lower (and this is on just 32,000 rows of a very simple calculation).
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:
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?
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!