Lab: SCDs
Slowly Changing Dimensions ..
Last updated
Was this helpful?
Slowly Changing Dimensions ..
Last updated
Was this helpful?
The demonstration illustrates the key concepts and workflows for dealing with Slowly Changing Dimensions.
In this Lab, you are going to create a dimension table DIM_SCD, which will have:
technical key field
version field
date from and date to fields
Id field
name field
The technical key will become the primary key for the dimension table.
The version field sets the record version (increments) on an update or insert.
Create the Table in MySQL sampledata database.
In your DB management tool, execute the following statement.
This step is rarely used for lookups as specific steps exists; however, it does illustrate the database process of how referential integrity is maintained.
Add ‘London’ to the Data Grid.
To initially configure the fields set the step to Update mode.
Once completed remember to reset to lookup mode.
Ensure that the Dimension Lookup / Update is set to: Lookup Mode.
Sets the step to Update Mode with lookup keys: id
There’s no point adding the LAST_UPDATE field as we're dealing with Type 1 changes.
Click on the Fields tab.
RUN the Transformation
Click the Run button in the Canvas Toolbar.
Click on the Dimension lookup/update step and Preview the data.
Why does London have a TK 0 and a value of NULL?
To maintain referential integrity, the record is assigned a TK 0, and as it doesn’t exist in the database, the value returned is NULL. As this is a lookup, no record is written to the database table.
Add the following City: Pariss (incorrectly spelt )