Search code examples
iccube

Aggregation Type 'Close' with incomplete Date Dimension

We have a schema, were we show the last two months on a daily basis and the last two years on a monthly basis.
To get rid of the days in the date dimension we remove them in a Perspective.

Perspective

Removing directly in the dimension unfortunately doesn't work, since we would have to give a specific date there (e.g. 2020-10-01) instead of 'two months ago'. And then we would have to change it every month by hand.

Anyway this all works well as long as we don't use for our measures the advanced aggregation type (e.g. Close). As soon as we do that, we get an error if we want to see that measure on a monthly basis more than two months into the past (the daily values are not found, so the last value per day can obviously not be found).

Measure with aggregation type Close

With September 2020 the statement fails:
failed statement

From October 2020 the statement succeeds: succeeded statement

What is the best way to use these complex aggregation types with incomplete time dimensions?

Solution

  • I found a solution for this use case:

    I added a new hierarchy 'day' to the date dimension and used this new hierarchy for the sole purpose of roll_up hierarchy of these measures. This hierarchy won't be filtered, hence there won't be any days missing, but it also won't be used for display in a report. So the user will only see the filtered hierarchy and still the correct number will be shown on month level.