[progress Communities] [progress Openedge Abl] Wiki Page: Type 2 Slowly Changing Dimensions...

[T. Mike Smith]

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: "ID" (Ex: CustomerID) - A unique key from the source system for identifying a record, also know as a natural key. "Key" (Ex: CustomerKey) - A unique key generated during the data warehouse load process to identify a unique dimension record at a specific point in time, also known as a surrogate key. This key is used in tying a dimension table to fact tables. DIM_IsCurrent - A flag column which specifies if a record is the latest version for the ID value. Only one record should be marked as "current" for an ID value. DIM_EffectiveDate - A date column which specifies when a record became the current version for its ID value. DIM_EndDate - A date column which specifies when a record was no longer the current version for its ID value. DIM_Version - A sequential number column which indicates the version iteration of the record for its ID value. The very first instance (or version) of a record is version 1. 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: SupplierKey SupplierID SupplierName SupplierState DIM_IsCurrent DIM_EffectiveDate DIM_EndDate DIM_Version 1 ABC Acme Supply Co CA 1 1/1/1900 1/1/2100 1 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): SupplierKey SupplierID SupplierName SupplierState DIM_IsCurrent DIM_EffectiveDate DIM_EndDate DIM_Version 1 ABC Acme Supply Co CA 0 1/1/1900 4/3/2017 1 231 ABC Acme Supply Co IL 1 4/4/2017 1/1/2100 2 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.

Continue reading...