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.
Thank you for your answer Anonymous. I'm grouping by Date field, so I expected the engine to recognize the lack of data in the AGG table and hit the facts instead. Is that assumption wrong?
Correct, the idea with Aggregate data is to substitute the Dimension with lower cardnality and leave the large cardnality (or data that is aggregated less often) to the detail row. It does not look at rows in a dimension that do not have data in the aggregate, then go to the detail table.
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.Should I then have hot and cold logic?
--> I have never done this, so I can say if it would work as designed. Aggregate tables was not created for this scenario, sorry.
The idea of the aggregate table is reduce the size of data in a table to return calculated measures that users need to group by and filter.