All of the dimension tables provided in the OEA360 Data Warehouse are capable of supporting Type 2 method of Slowly Changing Dimensions (SCDs). This method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys. Unlimited history is preserved for each insert.
To support this capability every provided dimension table contains the following columns:
Here is an example of a Type 2 Slowly Changing Dimension table in action. Suppose we have a suppliers in our source system that we load within our Data Warehouse. One of those suppliers has an ID of 'ABC' that we use to identify them within the source system. When we first load this supplier within the Data Warehouse our Supplier dimension table would look something like this:
Later in the future this Supplier moves locations so therefore when the change is received by the Data Warehouse the Supplier dimension table will be update like so (this assumes other supplier records are also loaded so key values 2 - 230 are already used):
When the new version of the supplier is loaded the DIM_IsCurrent and DIM_EndDate of the prior version is updated appropriately. For fact tables that use the Supplier dimension they would contain the appropriate SupplierKey value based on version applicable for the SupplierID at the time the fact was applicable. During analytics if interested in showing the current location of Suppliers the table would be queried for those records which are presently marked as current in DIM_IsCurrent.
A Type 2 SCD retains the full history of values.