Aggregation Tables in Datamart generated Dataset

[JamesMcEwan]

I have a dataset which is generated from my datamart. The 'factSales' table in my model is >100m rows and although this is fine, I want to create a couple of aggregation tables to help speed up the user exprience when refreshing visuals. 

I load the datamart generated dataset into a PBI desktop instance, I then create an 'aggfactSales' table as an Import table from my SQL, create a relationship with 'dimDate' and then go through the manage aggreagtions process. 

However - I cannot change the 'dimDate' table from Direct Query to Duel, as it is greyed out. I checked using DaxStudio and the agg table is not being hit during evaluation. 

Any ideas on what is going on, or if I need to change my approach?

[v-binbinyu-msft · Accepted solution]

Hi @JamesMcEwan ,

It seems that you are trying to create an aggregated table to improve the performance of your report. However, you are unable to change the 'dimDate' table from Direct Query to Dual, which is causing the aggregation table to not be hit during evaluation.

One possible reason for this issue is that the 'dimDate' table is being used in other relationships or calculations that require Direct Query mode. In this case, you may need to create a separate 'dimDate' table for the aggregation table and use a different name to avoid conflicts with the original 'dimDate' table.

Another possible reason is that the aggregation table is not being used in any visuals or calculations, which could cause it to be optimized out during evaluation. To ensure that the aggregation table is being used, you can try adding it to a visual or calculation and see if it is being hit during evaluation.

If neither of these solutions work, you may need to change your approach and consider using other techniques to improve the performance of your report, such as optimizing your data model, using DirectQuery mode for large tables, or using incremental refresh to load only the most recent data.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.