Forum Discussion
Aggregation Tables with less data than facts table - Is it a problem?
Should I then have hot and cold logic?
Fact_hot_table: last 2 years of data
AGG_hot_table: last 2 years of data
Fact_cold_table: all the data prior to the last 2 years
After, I need to change metrics accordingly to this logic. So if in the slicer I pick a date prior do 2022, for instance, the measure will be calculated on the COLD table.
You can do that, but it defeats the purpose of having an aggraegate table. The aggregate table is to make DAX Measures faster to aggregate (Sum, Count, CALCUATE(), etc.). Rather than measuring on a table with millions or billions of rows, the measure is on an pre-aggregated table to display faster.
You get an understanding of the users of the visuals and try to 'aggregate' those common visuals. The visuals going off the detail table will take longer and the end user knows this and accepts the process.
You could just add those years to the aggregate table to help speed thos up 🙂
Thank you for your suggestion, but the problem is that the granularity needed for the use cases is terrific. Meaning that if import all the data, I max out the 1Gb storage space of the PBI Pro License.
I am as well looking at future states, because the tendency is for the table to have an higher volume of records per week as time goes by. If needed I will create a perspective where users can see data aggregated by month, instead of daily, enabling your suggestion.