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

Get SAS Data Integration Studio 4.9 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.