Slowly Changing Dimensions are a recurring topic in Business Intelligence and Data Warehousing in general. The
term describes the unpredictable change of dimensions over the life-cycle of a data model and the approaches
used to capture and document historical data for future reference.
Before:
After:
There are 4 ways to deal with them:
- Dimension Update
- Parallel Hierarchies
- Split information over different dimensions
- Attribute/Control Cube
Dimension Update (Slowly Changing Dimensions)
The first approach is the so-called “Update” approach. As the word “Update” suggests, this approach removes the
old structure and replaces it with the new one. This approach has proven to be efficient, in the case when no historical
comparison is required. The latest structural changes should simply be displayed in the respective dimension.
Parallel Hierarchies (Slowly Changing Dimensions)
An approach to preserve historical structures and information is to create so-called “parallel hierarchies”. Hereby
the new structure is created under a new consolidated element in the respective dimension.
Attention should be paid to how many parallel hierarchies are being set up as this method can quickly “clog” the
dimensions and thereby negatively influence the performance of the system.
Split information over different dimensions (Slowly Changing Dimensions)
The changes over time can also be captured by breaking down the changing dimension in two. One dimension
would capture the hierarchies which are not changing and the second dimension would have the underlying
elements. New elements would be added over time to the existing ones. Historical data are accessible.
Attribute/Control cube
Another approach to handle Slowly Changing Dimensions is to record structural changes as attributes. These
attributes can be dates that indicate the validity period of an element. When evaluating the data, the user can set
up filters to explicitly look for elements at the desired point in time.
Following the same concept, it is also possible to store the validity information in a separate “control” cube. This
the approach is best used for larger projects with Web-based reporting.
By setting up an attribute filter that separates all elements within a certain validity period, according to
their “From” and “To” values, we can see the most recent structure and values as well as their previous states.
E.g.: When we want to see the 2019s cost center structure, the attribute filter for “From” needs to be set to “<2019”
and the one for “To” to “>2019”.
NOTE: virtual dimensions could be used instead of parallel hierarchies if the cost center structure has only two levels.
Database examples can be downloaded here.