Business Intelligence Tutorial

Defining warehouse targets

In this lesson, you will define warehouse targets. Warehouse targets identify the database and tables that the Data Warehouse Center is to use for your data warehouse. Generally, the target tables that are defined in the warehouse target are also used as the dimension and fact tables in the star schema. However, the warehouse target might also include interim target tables that are used for data transformation.

In this lesson, you will define the Tutorial Targets warehouse target, which is a logical definition for the warehouse database that you created in Creating a warehouse database. Within the warehouse target, you will define the DEMOGRAPHICS_TARGET target table.

You can also specify that the Data Warehouse Center is to generate the target table. This step will be covered in the next lesson.


Defining a warehouse target

In this exercise, you will define the Tutorial Targets warehouse target, which is a logical definition for the TUTWHS database that you created in Creating a warehouse database.

To define the warehouse target:

  1. From the Data Warehouse Center window, right-click the Warehouse Targets folder.
  2. Click Define --> DB2 Family --> DB2 UDB for Windows NT.

    The Define Warehouse Target notebook opens.

  3. In the Name field, type the business name for the warehouse target:
     Tutorial Targets
    
  4. In the Administrator field, type your name as the contact for the warehouse target.
  5. In the Description field, type a short description of the data:
    Warehouse for the TBC company
    
  6. Click the Database tab.
  7. In the Database name field, click or type the name of the database:
    TUTWHS
    

    You are creating the target database in the default system, so you can skip the System name field.

  8. In the User ID field, type the user ID that you use to log on to the Data Warehouse Center.
  9. In the Password field, type the password for the user ID.
  10. In the Verify password field, type the password again.

    Use the default values for the rest of the controls on the page.

  11. Click the Security tab.
  12. Select the Tutorial Warehouse Group.
  13. Click > to move the Tutorial Warehouse Group to the Selected warehouse groups list.

    Adding the target to the warehouse group authorizes the users in the group (in this case, you) to create steps that use this warehouse target.

  14. Click OK to save your changes and close the Define Warehouse Target notebook.

Defining a target table

In this exercise you will define the DEMOGRAPHICS_TARGET table within the Tutorial Targets warehouse target.

To define a warehouse target table:

  1. Expand the Warehouse Targets tree until you see the Tables folder under the Tutorial Targets warehouse target.
  2. Right-click the Tables folder, and click Define.

    The Define Warehouse Target Table notebook opens, and you can define the DEMOGRAPHICS_TARGET table.
    Figure db2tu023 not displayed.

  3. In the Table schema list, click or type the user ID under which you created the warehouse database in Creating a warehouse database.
  4. In the Table name field, type the name of the target table:
    DEMOGRAPHICS_TARGET
    

    Because you are creating the tables in the default table space, you can skip the Table space and Index table space lists.

  5. In the Description field, type the description of the table:
    Demographics data for sales regions
    
  6. In the Business name field, type the business name for the table:
    Demographics Target
    
  7. Verify that the Data Warehouse Center created table check box is selected.

    The Data Warehouse Center will create this table when the step that loads the Demographic data runs.

    You use this option when you want the Data Warehouse Center to create the target table. Otherwise, you can use a target table that is already defined.

  8. Verify that the Grant to public check box is selected.

    This check box specifies that anyone who has access to the database has access to the table.

  9. Clear the Part of an OLAP schema check box.

    The Part of an OLAP schema check box indicates that the table is a dimension table or fact table that is to be exported to OLAP Integration Server. For more information about exporting to OLAP Integration Server, see Creating a star schema from within the Data Warehouse Center.

    Skip the rest of the controls on the page. For more information about them, see "Defining a warehouse target table" in the online help.

    Leave the Define Warehouse Target Table window open and go on to the next exercise.

Adding columns to the target table

To add columns to the target table:

  1. Click the Columns tab.
  2. Right-click the empty space in the list.
  3. Click Add.

    A row is added to the list, and you can define the State column, which is one of the main values for the table.

  4. Click the Column name column, and type STATE.
  5. In the Data type column, verify that CHAR is selected.
  6. Click the Length column, and type 50.

    Skip the Precision and Scale columns because they apply to decimal data only.

  7. Verify that the Nullable check box is selected.
  8. In the Business name field, type State.
  9. Repeat steps 2 through 8 using the values in the following table to define the rest of the columns in the table:
    Name Data type Length Allow nulls Business name
    CITY CHAR 50 Clear the Nullable check box City
    POPULATION INTEGER N/A Clear the Nullable check box Population

    The Columns page of the Define Warehouse Target Table notebook

    Skip the rest of the notebook. You will use the DB2 Control Center and Data Warehouse Center to define primary and foreign keys on the physical target tables. You can use primary and foreign keys for joins.

  10. Click OK.

    The Define Warehouse Target Table notebook closes. The Demographics table is displayed under the Tables folder.


What you just did

In this lesson, you defined a warehouse target with one target table, DEMOGRAPHICS_TARGET. In Defining data transformation and movement, you will generate target tables for this warehouse target, including the LOOKUP_MARKET dimension table for the star schema.


[ Top of Page | Previous Page | Next Page | Table of Contents ]