Today we are going to talk about an old question that we already got several, hundreds, thousands (lol) of questions but, at true, is pretty easy!
I would like to indroduce you to the Mr. SCD!!!!!
In few lines, you will be able to configurate and use it…. Let’s start:
This post defines the necessary setting to use the Oracle Slowly changing dimension .
Set the columns with required properties namely,
SURROGATE_KEY – Sequence
NATURAL_KEY – Primary or Unique Key
OVERWRITE ON CHANGE – Column for which data needs to be updated
ADD ROW ON CHANGE – Column for which a new Surrogate Key and New rows to generated when the data for this Column changes
CURRENT RECORD FLAG – 0 or 1 . The KM populates this column so no need to populate this column .(1 : Current Record , 0 : Past Records)
STARTING TIMESTAMP – Column indicating the beginning of a Record’s availability. use SYSDATE or SYSTIMESTAMP depending on the datatype.
ENDING TIMESTAMP -Column indicating the end of the Record’s availability. The value is a default of “01 January 2400”
Map the columns in this way where the Surrogate Key needs to be sequence . The ending timestamp and Current Flag columns need to be mapped even that the KM will populate those column accordingly. Just add any value to them.
1. If in case you don’t have any ‘Overwrite on change‘ column , please check the option ignore errors on Update Existing row., since in the select statement it wont find the column to do query and will error out.
2. If the Target datastore is table , Flow control or static control have to yes and the appropriate CKM have to be selected , else there will be error in Historize old rows saying ‘invalid user.table.column, table.column, or column specification”.
Well, as you can see, no secrets to use it!!!!