Converting Type 1 Slowly Changing Dimension Jobs to Type 2

Converting a Hash File Lookup in a Related Fact Table Job

Due to the relationship between dimension lookups in a fact table job and the corresponding dimension used in the lookup, if you convert a type 1 slowly changing dimension job to a type 2 slowly changing dimension job, this may impact the related fact table job. Thus, if you want to convert a type 1 slowly changing dimension job to a type 2, you might also have to modify the dimension lookup in the related fact table job.

If the fact table job related to the modified dimension uses a hash file lookup, you must convert the hash file lookup to a dynamic DRS lookup. However, if the fact table job related to the dimension uses a dynamic DRS lookup, you do not need to convert the lookup. Instructions on how to convert a hash file lookup to a dynamic DRS lookup are described in the following section.

See Converting a Hash File Lookup to a Dynamic DRS Lookup in the Related Fact Table Job.

Step 1: Modifying the Source Query

Perform the following steps to modify the source query:

1. In IBM WebSphere DataStage Designer, navigate to the type 1 slowly changing dimension job you would like to convert by expanding the nodes in the left navigation panel; then open the job.

2. Locate the source DRS stage within the job and open it.

3. In the Output tab, select the Selection sub-tab to edit the WHERE clause of your source table.

   Note: Most of the Type 1 dimension jobs have the correlating sub-query to get the latest effective dated dimensional record.

4. Remove the correlating sub-query.

   You should be left with the following SQL statement:

   (LASTUPD_EW_DTTM > %DateTimeIn('#LastModifiedDateTime#') OR LASTUPD_EW_DTTM IS NULL) ORDER BY EFFDT, EFFSEQ

   Image: DRS stage - Output tab

   This example illustrates the fields and controls on the DRS stage - Output tab. You can find definitions for the fields and controls later on this page.

   

   Note: There is an ORDER BY clause to sort the dimensions that are changed over a period of time.

5. Click OK.

Step 2: Modifying the Target Hash Lookup Stage

Perform the following steps to modify the target hash lookup stage:

1. Locate the target hash lookup stage within the job and open it.

2. In the Output tab, select the Columns sub-tab and add EFFDT and EFFSEQ as a key columns, and EFF_END_DT as a non-key column with Timestamp(19) as the datatype.

3. Select the General sub-tab and change the Hash File and Hash Stage names by adding the suffix _TGT to them.

4. Locate the transformer stage that defines the lookup transformation between this hash file and the incoming row and open it.

5. In the Output tab, select the Columns sub-tab and map the EFFDT and EFFSEQ columns from the incoming link to the newly added EFFDT and EFFSEQ columns of this Hash File.

   Image: Transformer stage - Output tab

   This example illustrates the fields and controls on the Transformer stage - Output tab. You can find definitions for the fields and controls later on this page.

   

6. Click OK.

Step 3: Adding Lookup Stages to Identify Slowly Changing Dimension Logic

Perform the following steps to add lookup stages:

1. Add new DRS and Hash File stages to the job, placing them next to the transformer that loads the target table.

2. Link the DRS stage to the newly added Hash File stage.

3. Link the new Hash File stage to the aforementioned transformer.

   Image: Adding lookup stages

   This example illustrates the fields and controls on the Adding lookup stages. You can find definitions for the fields and controls later on this page.

   

   Note: The new DRS stage should refer to that target dimension table name with target database connection parameters . The DRS stage should have all the alternate key columns and primary key columns (SID column) in the columns metadata. The alternate key columns should be enabled as key columns in the Input and Output of Hashed File columns metadata.

4. Change the DRS stage, DRS link, Hash File stage, and Hash File link names by adding the suffix _SCD to them.

5. Click OK.

Step 4: Adding a WHERE Clause to the Lookup DRS Stage

Perform the following steps to add a WHERE clause to the lookup DRS stage:

1. Locate the new DRS stage and open it.

2. In the Output tab, select the Selection sub-tab to edit the WHERE clause.

   Image: DRS stage - Output tab

   This example illustrates the fields and controls on the DRS stage - Output tab. You can find definitions for the fields and controls later on this page.

   

3. Add the following WHERE condition to get the most recent SID value:

   CURRENT_IND = 'Y'

4. Specify the database connection parameters in the General tab of the DRS stage.

5. Click OK.

Step 5: Adding a New Hash File Stage to Refresh the Lookup Data

Perform the following steps to add a new hash file stage:

1. Add a new hash file stage to the job.

2. Link the new hash file stage to the target transformer such that the target transformer loads the has file stage.

   Image: Adding a mew hash file stage

   This example illustrates the fields and controls on the Adding a mew hash file stage. You can find definitions for the fields and controls later on this page.

   

   Note: The new DRS stage should refer to that target dimension table name with target database connection parameters . The DRS stage should have all the alternate key columns and primary key columns (SID column) in the columns metadata. The alternate key columns should be enabled as key columns in the Input and Output of Hashed File columns metadata.

3. Change the hash file name by adding the suffix _SCD to it.

4. Click OK.

Step 6: Adding a Target DRS Stage to Update the Old Dimension Record

Perform the following steps to add a target DRS stage:

1. Copy the DRS stage from where the data is loaded and paste it into the same job.

2. Change the new DRS stage and DRS link names by adding the suffix _SCD_UPDATE to them.

3. Link the target transformer stage with the new target DRS stage.

4. Open the new DRS stage and select the Input tab.

5. Select the General sub-tab and change the Update action value to Update existing rows only.

   Image: DRS stage - Input tab

   This example illustrates the fields and controls on the DRS stage - Input tab. You can find definitions for the fields and controls later on this page.

   

6. Click OK.

Step 7: Verifying the Number of Links in the Job Design

Examine the entire job and verify that there are a total of six links in the job. There should be three input links to the target transformer stage and three output links from target transformer. All the links should be connected as follows:

Image: Verifying links

This example illustrates the fields and controls on the Verifying links. You can find definitions for the fields and controls later on this page.

Step 8: Adding Stage Variables to Perform Slowly Changing Dimension Logic

Perform the following steps to add stage variables:

1. Locate the target transformer and open it.

2. Verify the lookup join between input link and the new lookup stage (for example, _TGT) .

3. Link the key columns of the input link to those in the [hash file name]_SCD lookup link.

4. Add a new SCDFlag stage variable to the transformer, using the following expression:

   If NOT(HASH_PS_D_EMPL_JOB_SCD_LKP.NOTFOUND) AND (Substrings(PS_D_EMPL_JOB4_Load.EFFDT, 1, 19) <> Substrings(HASH_PS_D_EMPL_JOB_SCD_LKP.EFFDT, 1, 19) or (HASH_PS_D_EMPL_JOB_SCD_LKP.EFFSEQ <> PS_D_EMPL_JOB4_Load.EFFSEQ))Then 'Y' Else 'N'

5. Add a new EFFENDDTUPD stage variable to the transformer, using the following expression:

   AddToDate(<INPUT_LINK_NAME>.EFFDT, 'DD', -1)

6. Add a new EFFENDDTUPD stage variable to the transformer, using the following expression:

   If Len(<TGT_LOOKUP_LINK_NAME>.EFF_END_DT)= 0 Then MaxDate Else <TGT_LOOKUP_LINK_NAME>.EFF_END_DT

7. Click OK.

Image: Adding stage variables

This example illustrates the fields and controls on the Adding stage variables. You can find definitions for the fields and controls later on this page.

Step 9: Modifying Column Expressions to Perform Slowly Changing Dimension Logic

Perform the following steps to modify column expressions:

1. Locate the target transformer and open it.

2. Locate the output link that loads the target with Update existing rows or Insert new rows.

3. Open the link for editing and modify the expression for the EFF_START_DT column as follows:

   EFF_START_DT = <INPUT_LINK_NAME>.EFFDT

4. Modify the expression for the EFF_END_DT column as follows:

   EFF_END_DT = EFFENDDT (it is a stage variable)

5. Locate the output link that updates the target with Update existing rows only.

6. Open the link for editing and delete all columns from the table except the primary key column (SID column), EFF_END_DT, LASTUPD_EW_DTTM, CURRENT_IND and BATCH_SID.

7. Modify the expression for the SID column as follows:

   SID column = <SCD_LOOKUP_LINK_NAME>.<PRIMARY_SID_COLUMN_NAME>

8. Modify the expression for the EFF_END_DT column as follows:

   EFF_END_DT = EFFENDDTUPD

9. Modify the expression for the LASTUPD_EW_DTTM column as follows:

   LASTUPD_EW_DTTM = DSJobStartTimestamp

10. Modify the expression for the CURRENT_IND column as follows:

    CURRENT_IND = 'N'

11. Modify the expression for the BATCH_SID column as follows:

    BATCH_SID = BATCH_SID

12. Add the following constraint to the link so that the EFF_END_DT of the old dimension record is updated:

    SCDFlag = 'Y'

13. Locate the output link that updates the new Hash file (for example, [hash file name]_SCD).

14. Map the alternate key columns from the input link to alternate keys in the lookup table.

    Although the column mapping is one-to-one, there should be proper NULL handling based on the column data type.

15. Modify the expression for the primary key (SID) column so that the expression uses the stage variable SIDValue.

16. Click OK.

Image: Modifying column expressions, 1 of 2

This example illustrates the fields and controls on the Modifying column expressions, 1 of 2. You can find definitions for the fields and controls later on this page.

Image: Modifying column expressions, 2 of 2

This example illustrates the fields and controls on the Modifying column expressions, 2 of 2. You can find definitions for the fields and controls later on this page.

Step 10: Compiling the job

Perform the following steps to compile the job:

1. Select File, Save from the menu to save the job.

2. Select File, Compile from the menu to compile the job.

   If your mapping is correct, the Compilation Status window displays the Job successfully compiled with no errors message. If your mapping is incorrect, the Compilation Status window displays an error message.

3. If your job successfully compiles, select Close.

   If you job does not compile successfully, you must return to the job and troubleshoot the errors.

Step 3: Adding Lookup Stages to Identify Slowly Changing Dimension Logic

COLUMN_X should not be enabled as a key in the input and output links of the hash file stage (for example, [hash file name]_SCD).

Step 5: Adding a New Hash File Stage to Refresh the Lookup Data

COLUMN_X should not be enabled as a key in the input links of the hash file stage (for example, [hash file name]_SCD).

Step 8: Adding Stage Variables to Perform Slowly Changing Dimension Logic

Add a new SCDFlag stage variable to the transformer, using the following expression:

If NOT(<SCD_LOOKUP_LINK_NAME>.NOTFOUND) AND <INPUT_LINK_NAME>.<COLUMN_X> <> <SCD_LOOKUP_LINK_NAME>.<COLUMN_X> Then 'Y' Else 'N'