Dimension Type 2
Description
Flattened structure that stores attribute information for one class of data (e.g. person, place, entity). In simple terms, dimensions give who, what, where of a fact. One or more records having the same natural key can exist.
Type 2 dimensions track changes. They are used when the data changes often and the change history needs to be recorded. Examples of type 2 dimensions include: Product, Customer, Vendor and Address. All Type 2 dimension have additional control attributes used to identify current and historic records with the same natural keys.
Data Change Observation
Data changes are observed for a given Natural Key(NK). New NK combinations will generate a new record. Existing NK combinations will update the record with most recent values. History of data changes is retained.
The A2B Data process will insert a new record when it observes new data for a given Natural Key:
The New Record will have attributes set to the following values:
Current Row Flag = 'Y'
Eff DateTime = Processed Date/Time
Replaced DateTime = Default Future Date (2099)
The A2B Data process will update the current record and insert a new record when it observes changes in data for a given Natural Key:
The Old Record will have the following attribute values set:
Current Row Flag = 'N'
Replaced DateTime = (Processed Date-1 day)/Time
The New Record will have attributes set to the following values:
Current Row Flag = 'Y'
Eff DateTime = Processed Date/Time
Replaced DateTime = Default Future Date (2099)
If the A2B Data process does not find the Natural Key in the source data, the record will be marked as deleted:
The Deleted Record will have the following attribute values set:
Delete Row = 'Y'
Update DateTime = Processed Date/Time
Primary Key
Primary Key is assigned for the unique combination of Natural Key(s) of the Driver Table.