Creating Slowly Changing Dimension

This option allows you to create a new SCD entry.

To create SCD:

1. From the Slowly Changing Dimension Summary window, click Add.
   The Slowly Changing Dimension window is displayed.

   Figure 7-37 Slowly Changing Dimension window

   
   

   

   
   
2. Enter the details as tabulated:

   Table 7-13 Fields in the Slowly Changing Dimension window and their Description

   Field Name	Description
   Define SCD
   Map Reference Number	Enter a Mapping Reference Number for this unique mapping of a Source to a Dimension Table. The supported numbers are from 0 to 999. If it is given as -1, SCD will execute for all Map Reference Numbers.
   Stage Table Name	Enter the stage table name.
   Source Priority	Enter the priority of the source when multiple sources are mapped to the same target.
   Table Name	Enter the dimension table name, whose record needs to be updated.
   SCD Details
   Source Type	Enter the type of the Source for a Dimension, that is, Transaction or Master Source.
   Source Key	Enter the Source Key.
   Data Offset	Enter the offset for calculating the Start Date based on the File Received Date.
   Source Process Sequence	Enter the sequence in which the various sources for the DIMENSION will be taken up for processing.

3. Click the Add icon from the Column Mapping tab. A new row gets added.
4. Double-click each cell to edit it. Enter the following details for each record.

   Table 7-14 Fields in the Slowly Changing Dimension window and their Description

   Column Name	Description
   Sr. No.	Enter a unique serial number.
   Stage Column Name	Enter the stage column name.
   Colum Type	Enter the type of the column. For information for the possible values, see Column Types. You must enter information about at least the following column types: PK- Primary key, SK -Surrogate Key, SD- Start Date, LRI - Latest Record Indicator, ED - End Date, DA - Dimensional attribute and MD - MIS Date.
   Column Datatype	Enter the column data type.
   SCD Type	Enter the SCD type. The options are: 1 – Type I SCD 2 – Type II SCD NULL – No SCD handling for such attributes For information on different SCD types, see SCD Types.
   Priority Lookup Required	Specify whether Lookup is required for Priority of Source against the Source Key Column or not. The possible values are Y and N.
   Column Format	Enter the format of the column.

5. Click the Optimizations tab to add optimizer hints for merge execution mode.

   Figure 7-38 Optimizations tab

   
   

   

   
   
6. Enter statement-level optimizer hints for the merge statement in the Source Hint field.
7. Enter statement-level optimizer hint for the select statement in merge in the Merge Hint field.
8. Enter alter statements to enable session level execution before merge statement in the Session Enable Statement field.
   Format: "<enable stmt1>","<enable stmt2>"
   For example: "alter session enable parallel dml","alter session enable parallel query"
9. Enter alter statements to disable session level execution after merge statement in the Session Enable Statement field.
   Format: "< disable stmt1>","< disable stmt2>"
   For example: "alter session disable parallel dml","alter session disable parallel query"
10. Click Save.