Search code examples
database-designolap

Best practice to store diverse time intervals in OLAP cube

My task is to make "OLAP cube", aggregated by time intervals

So, it is supposed, that facts table will store aggregates:

  1. for each day
  2. for each month based on its days records
  3. for each year based on its months records

And it will look like this:

|------------------------------------------|
|   id |  day | month | year | total_sales |
|------------------------------------------|
|    1 |    1 |     1 | 2020 |          10 |
|    2 |    2 |     1 | 2020 |          10 |
| ...N | ...N |  ...N | 2020 |          10 |
|   32 | null |     1 | 2020 |         310 |  # total for Jan 2020
| ...N | null |  ...N | 2020 |         300 |
|  378 | null |  null | 2020 |        3600 |  # total for 2020
|------------------------------------------|

So, is that a good plan in general?

Will it be better to make day, month, year as a standalone dimension, or it does not matter?

Solution

  • It is not good idea, in most cases, to mix facts of differing granularity in a single fact table. If you really need to store daily, monthly and yearly data, consider more than one fact table.

    You can also have a dimension table with one row per day and various attributes that facilitate aggregation, such as fiscal year.