SAS Data Integration Studio 4.9

Use a Sequential Data Merge

Perform the following steps to configure the SCD Type 1 Loader transformation to use a

sequential data merge:

1. Open the properties window for the SCD Type 1 Loader transformation.

2. Click Options. Set the Use direct lookup (hash table) option in the General

category to No. This setting saves memory at a potential cost to processing

performance.

3. Click OK to save your settings and close the properties window.

Note: The DATA step merge lookup method requires the source table to be

presorted by business key with duplicate business keys removed. If the source

table is not presorted by business key or the source table contains duplicate

business keys, you should add a Sort transformation before you sort and remove

duplicate business keys with the SCD Type 1 Loader transformation. In the Sort

transform, navigate to the Options tab and change the first SAS Sort option

entitled Remove duplicate records to Remove rows with duplicate keys

(NODUPKEY). You should remove duplicate business keys from source tables

before you use the SCD Type 1 Loader transformation to avoid unexpected

results.

4. Insert a Sort transformation between the source table and the SCD Type 1 Loader

transformation.

5. Open the Sort transformation and click Sort Columns.

6. Move the columns that you want to use for the match keys in the source table to the

Sort by columns field.

7. Click OK to save your settings and close the properties window.

Loading a Dimension Table with Type 1 and 2

Updates

Problem

You want to load a dimension table using type 1 updates (overwrites) in certain columns

and type 2 updates (track changes) in other columns. You need to generate a primary key

for each target row and optimize performance for large source tables.

Solution

You can create a job that includes the SCD Type 2 Loader transformation. You can load

Type 1 and Type 2 changes in a single transformation. To optimize performance, you

can add a current-row indicator that speeds up the creation of the cross-reference table

that is used for change detection.

The sample job includes the following tasks:

• “Create and Populate the Job” on page 536

• “Configure the SCD Type 2 Loader” on page 537

• “Run the Job and View the Output” on page 537

Loading a Dimension Table with Type 1 and 2 Updates 535

Tasks

Create and Populate the Job

Perform the following steps to create and populate the job:

1. Create an empty SAS Data Integration Studio job.

2. In the Transformations tree, in the Data folder, drag the SCD Type 2 Loader

transformation into the empty job on the Diagram tab.

3. Select and drag the source table from its folder and drop it before the SCD Type 2

Loader transformation on the Diagram tab. In this sample job, the source contains

information on customers.

4. Drag the cursor from the source table to the input port of the SCD Type 2 Loader

transformation. This action connects the source to the transformation.

5. Create a new target table using the New Table Wizard. The sample job uses the same

columns as the source, and adds columns for change tracking, performance

enhancement, and a generated key. The new columns are defined as follows:

VALID_FROM DTTM

receives begin datetime values.

VALID_TO_DTTM

receives end datetime values.

CURRENT_ROW

receives 1s in current rows and zeros in closed-out rows. Adding this column

improves performance in loads that involve large amounts of data. The current

row indicator speeds up the process of creating and updating the cross-reference

table.

CUSTOMER_DIM_ID

receives the generated key values.

The following display shows the column properties for the new target table:

Display 24.7 Target Column Properties

536 Chapter 24 • Working with Slowly Changing Dimensions