Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Aggregation Tables with less data than facts table - Is it a problem?

Hello guys!

I've recently enter the big data BI world and have a very specific question.

This is my model:


I have a fact table with last 4 years (2020-2023) of data (2 billion rows) and an aggregated table with the last 2 years (2022-2023) of data (20M rows). So, the aggregated table as less years of data than the facts table.

When I use the date slicer and pick a date prior to 2022, I get a blank visual because the measure always hits the AGG (which does not have data for this time period) and not the facts table as expected. Have you guys faced a common issue? Is there a workaround?

Thank you in advance.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    The engine does not support this scenerio. You must have the same data in aggregate and detail tables. The engine looks at the attributes to group by, not volume of data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.