A rollup dimension enables you to present a more coarsely grained representation of a dimension, without breaking the conformance in that dimension.
Consider a DATE dimension as our starting point. Our business key is CalendarDate, and we have attributes such as DayNumber, MonthNumber, YearNumber, DayName, MonthName, etc. in the dimension.
It is very common for a business to require a grain of Year, Quarter or Month for face tables that are related to the calendar, but not to a specific date.
One solution might be to create separate dimensions, but that breaks conformance, and leads to an undesirable situation where the MonthName in the DATE dimension might be “January”, but in the MONTH dimension it might be “Jan”.
Another flawed solution is to implement a rule that says a Month will use the first day of the month from as its starting point. This is flawed because we’re mixing the concepts at different grains.
Ajilius implements a rollup dimension concept that creates a view over the base dimension, stripping away the data from beneath the new grain.
To create a rollup dimension, select the Create Rollup action from the base dimension context menu:
Assign a new name and description to the new rollup, then press the Add button to save the new definition:
Click the Change link to modify the column definitions for the rollup:
Using the list of columns, rename the dimension key, remove the columns that are not required for this dimension, and assign the new business key. For purposes of illustration, we’ve stripped away all other columns:
The CREATE script for this dimension will now be generated as a VIEW over the base dimension. There is no transform script for this dimension because it is implemented as a view.
This is a properly conformed dimension, re-using the attributes and structure of the base dimension, but rolled up to a new grain.