SCD stage in Datastage

SCD stage in Datastage

The SCD stage in Datastage allows implementing slowly changing dimension Approaches in the context of  Star/Snowflake schema. The SCD stage enables you to perform type 1 as well as type 2. it can have one input, output,  dimension update and dimension reference link

Understanding SCD types:

Type 1: Overwrite the Existing row in Dimension table

Type 2: Update the Current row with Expiry date and active flag as ‘N’ and insert the new row as the current row with the current date as the effective date and Active Flag as ‘Y.’

Example:

BEFORE TYPE 2 UPSERT

STORE_ID NAME LOCATION EFFECTIVE DATE EXPIRY_DATE ACTIVE_FLAG
100 MRETAIL NOIDA 01-01-2009 31-12-999 Y

AFTER ( For Example the location of MRETAIL changes to GURGAON)

STORE_ID NAME LOCATION EFFECTIVE DATE EXPIRY_DATE ACTIVE_FLAG
100 MRETAIL NOIDA 01-01-2009 001-01-2015 N
101 MRETAIL GURGAON 01-01-2015(Current Date) 31-12-9999 Y

Explanation: let us consider a small star flake schema for Sales:

Source Data:

  • SKU
  • NAME
  • DESC
  • AMOUNT
  • UNIT

Dimension table: DIMESNION_PRODUCT

  • SKU
  • NAME
  • DESC
  • ACTIVE_FLAG
  • EFFECTIVE_DATE
  • EXPIRE_DATE

Fact Table:

  • PRODUCT_ID
  • SALES_ID
  • AMOUNT
  • UNIT
scd stage star schema datastage
SCD stage: Star schema DataStage

Quick Steps:

  1. Design the job as follows:
SCD Stage : Example job
SCD Stage: Example job
  1. Set up the Properties for the Sequential stage:
  2. SCD Stage ? properties ? lookup
  3. Map the SKU column to SKU column of DB2_DIMENSION lStage link
  4. SCD Stage ? properties ? surrogates key
  5. Specify the file generated with surrogate stage earlier
  6. SCD Stage ? properties ? DIM UPDATE
  7. map all the columns to DB2_DIMENSION_ref link columns
  8. for Active flag set Y in the deviation
  9. Similarly current_date for Effective date and 31-11-999 as expire_date
  10. For product_id set next surrogate key () function in derivation
  11. IN output tab map the product_ID from db2_dimension_ref Link to output link
  12. Edit db2_dimension_updae stage and set the write mode to Upsert mode:
  13. Compile and run the job.

Share this post

Leave a Reply