Slowly Changing Dimension (Type 2) in ODI
Here is some background info about Slowly Changing Dimensions (Type 2), but consider this use case: imagine there is a customer dimension, and you would like to track the address of the customer, where he lives now, and where he lived in the past.
First we set the OLAP Type to: Slowly Changing Dimension
Secondly we set the Attributes SCD Behavior, a summary by type:
- Surrogate Key: A System Generated Identifier (a meaningless Number, populated by Sequence) not to be exposed to the end user
- Natural Key: A meaningful key used by the "business", e.g. Customer Number or in this example the Customer unique login; it's email address.
- Starting Timestamp or Effective Date indicates when this record was first seen, or the start date of the record.
- Ending Timestamp or Expiration Date indicates when this record ceased to exist, or the end date of the record.
- Current Record Flag: a value of 1 indicates it is the current record, a value of 0 indicates the record is expired and has a new version.
- Add Row on Change: would we like to add a new row if a different value for the same natural key exists? or:
- Overwrite on Change: would we like to overwrite existing values when a different value of the same record exists.
So that is the physical layout; how about the actual mapping?
Here is what it looks like (see left) : looks easy is easy.
D1S_CUSTOMER_O is a staging out table, which hold the data of the customers at moment in time x. W_CUSTOMER_D is a SCD2 dimension which holds historic data.
The physical lay-out mapping is as follows;
- The surrogate key is populated by sequence
- Effective date or start date by sysdate
- Current Flag by 1
The full physical mapping is displayed below;
And the results? Well if you execute this mapping at 2 moments in time you'll see that a customer moved from Brussels to Antwerp.
Note: be careful when migrating mappings from one ODI version to the other; newer versions have the current record expiration_date set to null instead of 2400....