When it comes to dynamic hierarchies and slowly-changing dimensions, three Temporal Scenarios (Up-to-date, Historical, Rollback) are supported by the DFM and by indyco, and each of them is properly managed when the SQL DDL is generated during logical design.
Up-to-date
In this scenario, all the (past and current) events are analyzed according to the current value of the hierarchy instances.
To achieve this goal no change to the schema must be done; when a hierarchy instance is modified, the old tuple in the corresponding dimension table is simply overwritten with the new one.
Note that, if this solution is applied, all the sales of SmartMart are attributed to Smith even if they were carried out when Johnson was in charge.
Historical
In this scenario, each event is analyzed according to the value the hierarchy instances had at the exact time when the event occurred. Even in this case there is no impact on the schema; however, a new tuple (with a different surrogate key) must be added to the corresponding dimension table whenever a hierarchy instance changes.
By applying this solution, the historical truth can be reconstructed with a simple star join, provided that all the events occurred since 1/1/2009 are stored in the fact table with keyS = 4.
Rollback
All the events are analyzed according to the configuration the hierarchies had at some previous time.
Supporting this scenario requires that dimension tables are completely historicized, which requires that the dimension table is extended with the following elements:
1. A couple of from-to timestamps specifying the validity interval of tuples;
2. A master attribute that specifies the key value of the reference tuple, i.e., the one from which each tuple stems. If multiple changes are applied to a tuple, the reference tuple is the original one, not the tuple resulting from the latest change. You can easily determine original tuples because their master attribute references their own key values.
Like for the historical scenario, in this case a new tuple is added to the dimension table after every change.
Remarkably, this solution also support the up-to-date and the historical scenarios.
Note: depending on your Business Intelligence frontend capabilities, the Master attribute should reference the current tuple, not the original one!
Design rules for the rollback temporal scenario
The basic rules are:
1. Conceptually, a rollback scenario on an attribute a is applied to a and to all the arcs exiting from a.*
2. In the logical schema, a rollback scenario on a is implemented by adding a couple of from-to validity timestamps and a master field to the dimension table where a is stored. The master field is used to link all the different versions of the same business object together.
3. To optimize querying performances and avoid self-joins when a reference date is set for rollback, every (fact, dimension, and bridge) table including a foreign key to the dimension table D that includes a, is extended to reference also the master field of D.
* Noticeably, in general there is no need for using timestamps and master fields for the arcs going from the fact to the dimensions, because these are natively historicized thanks to the time dimension that is normally present in facts.
Specific additional rules must then be applied to cope with the different constructs of DFM and with the possible design options.
Multiple arcs
1. Given a multiple arc from a to b, the corresponding bridge table is extended with a couple of from-to validity timestamps if a rollback scenario is specified on a.
Cross-dimensional attributes
Given a cross-dimensional attribute a with fathers b and c, the corresponding bridge table** is extended with a couple of from-to validity timestamps if the rollback scenario is specified on either b or c.
In case a is a leaf and the rollback scenario is specified on a, a snowflaking on a is forced so that rule 2 can be applied to the corresponding dimension table.
** No bridge table is necessary if a and b belong to the same hierarchy and no snowflaking is made on a and b.
Recursions
Given a recursion on a, the corresponding navigation table*** is extended with a couple of from-to validity timestamps if the rollback scenario is specified on a.
** The presence of the navigation table depends on the design option set.