Submitted by wolf on Jan 19, 2021

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.

           Product - Type 2 dimension   

 

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.