Loading a Fact Table Using Dimension Table Lookup

Problem

You want to load numeric source data into a fact table and add foreign keys from a dimension table.

Solution

You can create a job that uses the Lookup transformation, which loads fact data from a source table and uses a lookup process to load foreign keys from the dimension table.

The lookup process compares the business key in each source row to the business keys in the dimension table. When the business keys match, the foreign key from the dimension table is loaded into the fact table target.

This sample job assumes that you have already loaded data into your dimension table before you run the job that loads your fact table. Loading the dimension table first ensures that new foreign keys are available in the dimension table.

The sample job includes the following tasks:

Tasks

Create and Populate the Job

Perform the following steps to load a fact table:

Create an empty SAS Data Integration Studio job.
In the Transformations tree, in the Data folder, drag the Lookup transformation into the empty job in the Diagram tab.
Select and drag the source table containing numeric fact table data into the source table location on the Diagram tab.
Drag the cursor from the source table to the input port of the Lookup transformation. This action connects the source to the transformation.
Select and drag the lookup table that contains detail data into the Diagram tab, into a location that is near the source table.
Drag the cursor from the lookup table to the input port of the Lookup transformation. This action connects the lookup table to the transformation.

Note: To add more lookup tables, right-click the Lookup transformation and click Add Input.
Because you want to store the output of the transformation in a permanent target table, right-click the temporary work table that is attached to the transformation and select Replace. Then, use the Table Selector window to select the target table for the job. The target table must be registered in SAS Data Integration Studio. (For more information about temporary work tables, see Working with Default Temporary Output Tables.)
Select and drag the target table into the target table location on the Diagram tab. The target table has columns for data that is loaded from the source and from the lookup table.
Drag the cursor from an output port of the Surrogate Key Generator transformation to the target table. This action connects the transformation to the target. The following example shows the sample process flow.

Sample Lookup Process Flow Diagram

Note: In the display, the red icon indicates that the new Lookup transformation requires source column mappings. Click the red icon to display information about errors.

Map Source Columns Into the Target

Perform the following steps to map fact table columns from the source into the target:

In the properties window of the Lookup transformation, open the Mappings tab. Use this tab to map the columns directly from the source table to the target table, without the involvement of a lookup table.
In this sample job, map all source columns to the target by clicking the Map all columns icon (). The following display depicts the mappings between the source and the target:

Mappings Between Source and Target

Map Key Columns between the Source and Lookup Tables

Perform the following steps to define the conditions under which values from a lookup table are loaded into the target.

Select the Lookups tab.
Select the lookup table and click Lookup Properties.
Use the Source to Lookup Mapping tab to specify the source and lookup columns that are compared at run time. If values match, then the lookup value is added to the target. If a match is not found, then an exception condition exists.

In the sample job, the business key in the source is compared to the business key in the lookup table, which in this case is a dimension table that contains customer information. To map the columns, click the Customer_ID column in the Source Table list. Then right-click the Customer_ID column in the Lookup Table list, and select Map Selected. A mapping arrow appears between the two columns. The following display depicts the completed Source to Lookup Mapping tab.

Source to Lookup Column Mapping
If you want to define a WHERE clause that further refines the match between the business key columns, click the Where tab and build an expression. Click Apply to save changes.

Note: If you use a WHERE clause, and if the lookup table uses a generated key, you can improve performance by creating an index on the generated key column, as described in About Generated Keys.

Map Lookup Columns Into the Target

Perform the following steps to map lookup columns into the target. Values are loaded when keys match between the source table and lookup table. In the sample job, the target receives lookup table key values. In the target, the key values connect the factual events (orders) to detail data (customer information).

Open the Lookup to Target Mapping tab, and select the Customer_Gen_Key column.
Right-click the Customer_Gen_Key column and select Map Selected. A mapping arrow appears between the two columns.

Create Error and Exception Tables

You can create error and exception tables that receive selected data in response to selected conditions. You configure the error and exception conditions later in this sample job. Perform the following steps to create the error and exception tables:

Open the properties window of the Lookup transformation and select the Errors tab.
Click Create error table and then click Choose columns.
In the Choose Error Table Columns window, note that all source columns are selected to appear in the error table. Click OK to close the window.
On the Errors tab, click Create Exception Table and click Choose columns.
In the Choose Exception Table Columns window, note that the exception table columns include the source row number, the lookup table name, the exception condition, and the exception action. Click OK to close the window.

Configure Exception Handling

If you create an error table and an exception table, the Lookup transformation will, by default, send non-matching source rows to the error table and send exception messages to the exception table. This sample job examines and accepts the default conditions and actions for exception handling.

Perform the following steps to view the default exception handling:

In the properties window of the Lookup transformation, select the Lookups tab.
In the Lookups tab, select the lookup table and then select Lookup Properties.
In the Lookup Properties window, open the Exceptions tab. The following display depicts the default configuration for exception handling.

Default Exception Handling

In this sample job, if the Customer_ID column in a source row does not match a Customer_ID value in the target, then the error and exception tables are updated and the lookup value (Customer_Gen_Key) is set to missing for that row in the target.
Click OK to store your entries and close the properties window of the Lookup transformation.

Run the Job and View the Output

Perform the following steps to run the job and view the output:

Right-click on an empty area of the job, and click Run in the pop-up menu. SAS Data Integration Studio generates code for the job and submits it to the SAS Application Server for execution.
If error messages are displayed on the Status tab, read and respond to the messages as needed.
After the completion of the job, right-click the target and select Open to view the values that were loaded from the source and lookup tables. The following display depicts the target table data for the sample job.

Target Table Data
To view the contents of the error table, position the cursor in the job, over the Lookup transformation. When the error and exception tables appear, move the cursor over the error table, right-click, and select Open. The following display depicts the error table data for the sample job.

Error Table Data
To view the contents of the exception table, position the cursor over the Lookup transformation. When the error and exception tables appear in the job, slide the cursor over the exception table, right-click, and select Open. The following display depicts the exception table data for the sample job.

Exception Table Data