Column Types

The possible values for column type in the SYS_STG_JOIN_MASTER are:

1. PK – Primary Dimension Value (maybe multiple for a given “Mapping Reference Number”)
2. SK – Surrogate Key
3. DA – Dimensional Attribute (maybe multiple for a given “Mapping Reference Number”)
4. DS – Works same as DA; additionally inserts description for default entries(MSG and OTH) into DS type columns
5. SD – Start Date
6. ED – End Date
7. LRI – Latest Record Indicator (Current Flag)
8. CSK – Current Surrogate Key
9. PSK – Previous Surrogate Key
10. SS – Source Key
11. LUD – Last Updated Date / Time
12. LUB – Last Updated By
13. NN- Not Null columns
14. MD – MISDATE

    Note:

    • For records of Column type SK, the value of STG_COL_NM for that record should be SEQUENCE_NAME.nextval. Name of the sequence can be of the form SEQ_DIMTABLENAME which has to be created before executing SCD.
    • For records of Column type DA (value of OL_TYP of SYS_STG_JOIN_MASTER is DA), the value of the column SCD_TYP_ID should be set to 1 or 2 (depending upon the SCD type). Since SKEY is a sequence, this is available only in the dimension table and cannot be considered for the change in the values of the fields; hence for any non-DA columns, we cannot set the SCD_TYP_ID to 1 or 2. They have to be set to NULL.
    • For records of Column type ED, the value that goes into the column STG_COL_NM should be ’31-dec-9999’.