.png){kind=small}
.png){kind=small}
In this short writing, I want to share what I do and check when configuring aggregations tables with direct query mode datamodel in Power BI.
When I start to talk and listen to people about their experiences in configuring direct query mode, some of them say that it was OK, but some others say it was horrible, because of the performance issue.
There are many ways to enhance direct query mode's performance in Power BI reports, and one of them is creating aggregations tables when necessary.
Below, I write what I check when I create aggregations tables with a direct query mode data model.
The first thing I check is, before creating aggregation tables, the performance of the direct query mode.
And then, I create an aggregations table based on what aggregations (sum, min, max, count, average, and so forth) are required in the report.
The second thing I check is the data type of the column in a direct query table and the aggregated column in the aggregations table. For instance, if one is a whole number type and the other is a decimal number type, the configuration won't be successfully done. In the "Manage aggregations" popup window, if the "Quantity" column is grayed out and not shown in the DETAIL COLUMN area, one of the reasons is that the data types are different in the two tables.
Once the above setup is successfully finished, the aggregations table will be hidden automatically from users.
The third thing I check and change is, I create relationships and I change related dimension tables to DUAL MODE from direct query mode.
The last thing I check is, the performance of the configuration, and which table (direct query table? or aggregation table?) is actually used in the visualization.
As shown below, Direct query duration is not described in the Performance analyzer pane.
DAX Studio also indicates, like below, that the table used is "Aggregate Sales".
To summarize,
Check the performance of the direct query mode.
Once a decision to create aggregations tables is made, check the data type of the column in the direct query table and the column in the aggregations table. It will not be successfully configured even if there is an unnoticeable difference like whole number type vs. decimal number type.
Change dimension tables to DUAL MODE and create relationships with aggregations table.
Check the performance of aggregations, and whether the direct query mode table was still used or not.
I hope this helps to have more fun in authoring a direct query mode data model.