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 we need to see every single row?
A count of the sales would be useful but knowing about the individual transaction from 8 years ago isn't.
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:
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:
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:
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:
With the aggregate table selected change the Storage mode (Properties pane) to Import. Note that aggregate tables are automatically hidden.
We can put the new efficiency to the test by using the Performance analyser under the View tab and a simple 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:
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:
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 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?
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!