Now, you need to add the steps that define how the source data is to be
transformed into the target data. You must define three steps:
- From the palette (the toolbar on the left side of the window), click the
DB2 Programs icon:

You may need to maximize the window in order to see the DB2 Programs
icon.
Each program option in the menu represents a program group,
which is a grouping of similar programs.
- Click DB2 UDB --> Load.
- Click the spot on the canvas between the demographics.txt file and
the DEMOGRAPHICS_TARGET table.
An icon for the step is added to the window between the
demographics.txt file and the DEMOGRAPHICS_TARGET table.
- Right-click the new step.
- Click Properties.
The step Properties notebook opens.
- In the Name field, type the name of the step:
Load Demographics Data
- In the Administrator field, type your name as the contact for
this step.
- In the Description field, type the description of the
step:
Loads demographics data into the warehouse.
- Click OK.
The step Properties notebook closes.
- Click the Task Flow icon:

- Click the Data Link icon:

You will use the Data Link icon to define the flow of data from the source
file, through transformation by a step, to the target table.
- Click the middle of the Demographics source file and drag the mouse to the
Load Demographics Data step.
The Data Warehouse Center draws a line between the file and the
step.
This indicates that the Demographics source file contains the source data
for the step.
- Click the middle of the Load Demographics Data step and drag the mouse to
the DEMOGRAPHICS_TARGET table.

This indicates that the DEMOGRAPHICS_TARGET table contains the target data
for the step.
- Right-click the Load Demographics Data step.
- Click Properties.
The step Properties notebook opens.
- Click the Parameters tab.

- From the Load mode list, click REPLACE.
Accept the rest of the default values on the page.
You can skip the Column Mapping page because the Load program does not use
the column mapping to generate the target table. It generates the
target table based on the source file.
- Click OK.
The step Properties notebook closes.
- From the palette, click the SQL icon:

- Click the spot on the canvas below the GEOGRAPHIES table.
An icon for the step is added to the window.
- Right-click the step.
- Click Properties.
The step Properties notebook opens.
- In the Name field, type the name of the step:
Select Geographies Data
- In the Administrator field, type your name as the name of the
contact for the step.
- In the Description field, type the description of the
step:
Selects Geographies data from the warehouse source
- Click OK.
The step Properties notebook closes.
- Click the Task Flow icon:

- Click the Data Link icon:

- Click the middle of the Geographies source table and drag the mouse to the
middle of the Select Geographies Data step.
The Data Warehouse Center draws a line that indicates that the Geographies
source table contains the source data for the step.
Because you will specify that the Data Warehouse Center is to create the
target table, you do not need to link a target table to the step.
- Right-click the Select Geographies Data step.
- Click Properties.
The step Properties notebook opens.
- Click the SQL Statement tab.
- Click Build SQL to build SQL using the Build SQL wizard, which
generates SQL based on the selections that you make in the wizard.
Build SQL opens. The Tables tab is displayed.
- Verify that SAMPLTBC.GEOGRAPHIES is listed.

- Click the Columns tab.
- Click >> to add all the columns from the
SAMPLTBC.GEOGRAPHIES table to the Selected columns
list.

- Click the Review tab to view the SQL statement that you just
built.
- Click OK.
Build SQL closes. The SQL statement that you just built is displayed
on the SQL Statement page.

- Click Test to test the SQL that you just generated.
The Data Warehouse Center returns sample results of your SELECT
statement. These results should be the same as the results that you got
in Lesson 3, Browsing the source data when you browsed the sample data for the
SAMPLTBC.GEOGRAPHIES source table. You can compare your results
to the graphic in Lesson 3, Browsing the source data.
- Click Close to close the window.
- Select the Create Warehouse target table based on parameters
check box.
Selecting this check box specifies that the Data Warehouse Center is to
create the target table, based on the values that are specified on the Column
Mapping page.
- From the Warehouse target list, click Tutorial
Targets.
The warehouse target is the database or file system in which the target
table is to be created.
- Click the Column Mapping tab.
- Verify that the source columns map to the correct target columns.

- Click the Processing Options tab.
- Verify that Replace is selected in the Population
type list.
- Verify that the Run on demand check box is selected.
Accept the rest of the default values on this page. For more
information about the values on this page, see the online help.
- Click OK.
The Step notebook closes. The Data Warehouse Center generates a
target table called "Select_Geog_T". The target table name is in
quotation marks because it is in mixed case.
- Change the name of the target table:
- Right-click the "Select_Geog_T" target table.
- Click Properties.
The table Properties notebook opens.
- Verify that IWH is selected in the Table schema
field.
- In the Table name field, type the new name for the table:
GEOGRAPHIES_TARGET
- In the Description field, type a description of the
table:
Autogenerated target table for the Select Geographies Data step.
- In the Business name field, type a descriptive name for the
table:
Geographies Target
- Clear the Part of an OLAP schema check box.
- Click the Columns tab to view the column definitions.
- Click OK. The table Properties notebook closes.
- In the Process Model window, click View --> Arrange
- From the palette, click the SQL icon.
- Click the spot on the canvas below the two columns of steps and
tables.
An icon for the step is added to the window.
- Right-click the new step.
- Click Properties.
The step Properties notebook opens.
- In the Name field, type the name of the step:
Join Market Data
- In the Administrator field, type your name as the contact for
the step.
- In the Description field, type the description of the
step:
Joins the Geographies table with the Demographics table
- Click OK.
The Step notebook closes.
- Click the Task Flow icon:

- Click the Data Link icon:

- Click the middle of the GEOGRAPHIES_TARGET table and drag the mouse to the
Join Market Data step.
The Data Warehouse Center draws a line that indicates that the
GEOGRAPHIES_TARGET table contains source data for the step.
Because you will specify that the Data Warehouse Center is to create the
target table, you do not need to link a target table to the step.
- Repeat this operation with the Demographics Target table and the Join
Market Data Step.
The Data Warehouse Center draws a line that indicates that the
DEMOGRAPHICS_TARGET table contains source data for the step.
- Right-click the Join Market Data step.
- Click Properties.
The step Properties notebook opens.
- Click the SQL Statement tab.
- Click Build SQL to have the Data Warehouse Center generate
SQL. (Otherwise, you can type in your own SQL.)
Build SQL opens.
- On the Tables tab, verify that the DEMOGRAPHICS_TARGET and
GEOGRAPHIES_TARGET tables are listed.
- Click the Columns tab.

- Click >> to add all the columns from the Geographies
table and the Demographics table to the Selected columns
list.
- From the Selected columns list, click
DEMOGRAPHICS_TARGET.STATE.
- Click <.
The DEMOGRAPHICS_TARGET.STATE column moves to the Available
columns list.
- Click DEMOGRAPHICS_TARGET.CITY.
- Click <.
The DEMOGRAPHICS_TARGET.CITY column moves to the Available
columns list.
You do not need the DEMOGRAPHICS_TARGET.STATE and
DEMOGRAPHICS_TARGET.CITY columns because it is already defined in the
GEOGRAPHIES_TARGET table. You will join the tables using the CITY
column in both tables, which is unique.
- Click Add.
The Expression Builder window opens.
- In the Expression field, type the following CASE
statement:
case
when POPULATION < 9000000 then 1
when POPULATION < 15000000 then 2
Else 3
end

- Click OK.
A new column is added to the Selected columns list.
- Click the Name field of the new column and type the name of the
column:
SIZE_ID
- Press Enter.
- Click Move Up until the SIZE_ID column is above the POPULATION
column.
- Click the Joins tab.
- In the GEOGRAPHIES_TARGET table, select the CITY column.
- In the DEMOGRAPHICS_TARGET table, select the CITY column.
- Click Join.
Build SQL draws a line between the CITY columns, which indicates that the
tables are joined on that column.

- Click the Review tab to view the SQL statement that you just
built.
- Click OK.
Build SQL closes.
- On the SQL Statement tab, select the Create warehouse target table
based on parameters check box.
Selecting this check box specifies that the Data Warehouse Center is to
create the target table, based on the values that are specified on the SQL
Statement and Column Mapping pages.
- From the Warehouse target list, click Tutorial
Targets.
- Click the Column Mapping tab.
- Verify that the source columns map to the correct target columns.
- Click the Processing Options tab.
- In the Population type list, verify that Replace is
selected.
- Select the Run on demand check box to run the step on
demand.
Accept the rest of the default values on this page. For more
information about the values on this page, see the online help.
- Click OK.
The step Properties notebook closes.
- Change the name of the target table:
- Right-click the "Join_Market_T" target table.
- Click Properties.
The table Properties notebook opens.
- In the Table schema field, verify that IWH is selected.
- In the Table name field, type the new name for the table:
LOOKUP_MARKET
- In the Description field, type a description of the
table:
Market dimension data
- Verify that the Part of an OLAP schema check box and
Dimension table radio button are selected.
This table is one of the dimension tables that you will include in a star
schema in Lesson 16, Creating a star schema from within the Data Warehouse Center.
- Click the Columns tab to view the column definitions.
- Clear the Nullable check box for the CITY_ID column because you
will define this column as the primary key for the table in Lesson 11, Defining keys on target tables.
- Click the Warehouse Primary Key tab.
- Click CITY_ID in the Available columns list.
- Click >
The CITY_ID column moves to the Warehouse primary key columns
list.
- In the Constraint name field, type:
"Whse Market PK"
- Click OK. The table Properties notebook closes.

- Click the Save icon in the toolbar to save the process.

- Close the Process Model window.