Slowly Changing Dimension (SCD)

        The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables. For example, you can use this transformation to configure the transformation outputs that insert and update records in the Dimension table.

Example:
      I am having a Customer dimension table and source file. Using source file i need to update the dimension table using SCD Transformation.

Source File:

Target Table Script:

CREATE TABLE DIM_CUSTOMER

(

      ID VARCHAR(10)

      ,NAME VARCHAR(50)

      ,MOBBILE_NUMBER VARCHAR(20)

      ,ADDRESS VARCHAR(1000)

      ,LATEST_FLG VARCHAR(10)

)

        In the above file, Column ID and Name are Fixed attributes (Never change), Mobile number is changing attribute (Can change, we need to maintain latest number) and Address is Historical attribute (Can change but we need to maintain history).

• Open BIDS.
• From Control flow items, drag and drop “Data flow task” into designer surface.

• From toolbox drag and drop "Flat file source" and configure with source file.
• From the toolbox drag and drop "Slowly Changing Dimension" (SCD).
• Connect Flat file source green arrow to SCD.

• Edit the SCD Transformation (select SCD and right click on that and select edit option). 'Slowly Changing Dimension Wizard' will open.

• Click on "Next" button. Create connection manager and Select the required table.

•  Using 'Input Column' drop down list select the required columns. Using 'Key Type' drop down list select the key type.

• Click on 'Next Button'.
• Select 'Dimension Columns' and 'Change Type' from drop down list. Click on 'Next' button.

• Again Click on "Next" button.

• Select Latest_Flg from, Value When Current and Expiration value from the drop down list. Click on "Next" button.
• Again Click on "Next" button. Finally click on "Finish" Button. Then Package will create automatic.

•  Package is ready to execute. Run the package.

Output:

• If we change the Mobile number in the source file, then the change should reflect in the table.

Source File:

• Run the SSIS package.

Output:

• Record is updated as expected.
• If we change Address in the source file, then new record should insert. Existing record Latest flag should update as 'Expired'.

• After source file change, run the SSIS package.

Output:

• Table is updated as expected.