Data Warehouse Center Administration Guide

Appendix E. The data warehousing sample

DB2 Universal Database provides a data warehousing sample that you can use to familiarize yourself with the Data Warehouse Center. It includes sample data and metadata that you can run to create tables in a warehouse database.

Install and run the sample to learn about the relationships between source and target data, and the definitions in the Data Warehouse Center that move and transform the data.

The sample defines a star schema in the Data Warehouse Center. The star schema has three dimension tables:

• PRODUCT
• SCENARIO
• TIME

It has one fact table, named FACT TABLE.

Installing the sample

You can install the Data Warehousing sample on Windows NT only. The sample is installed as part of the typical installation of DB2 Universal Database on Windows NT. You can also install the sample by selecting the Getting Started component, or the First Steps and Sample Databases subcomponents of the Getting Started component, in a custom installation.

You must install the sample on the same workstation as the warehouse server.

Creating the sample databases

You must create the sample databases after you install the files for the sample. To create the databases:

1. Open the First Steps window.
2. Click Create Sample Databases.

   The Create SAMPLE Databases window opens.

3. Select the Data Warehousing sample check box.
4. Click OK.
5. If you are installing the data warehousing sample, a window opens for the DB2 user ID and password to use to access the sample.
   1. Type the user ID and password that you want to use. You must specify a valid DB2 user ID and password.
   2. Click OK.

   A progress window opens. When the databases are created, click OK.

The databases that you created are registered with ODBC.

The following databases are created:

DWCTBC

Contains the operational source tables that are required for the Data warehousing section of the tutorial.

TBC_MD

Contains metadata for the Data Warehouse Center.

Creating the warehouse database

You need to create a database for the data that is generated when you run the sample.

To create the database:

1. Start the DB2 Control Center:
   • On Windows NT, click Start --> Programs --> IBM DB2--> Control Center.
   • On AIX or the Solaris Operating Environment, type the following command:

     db2jstrt 6790
     db2cc 6790b
     

2. Right-click the Databases folder, and click Create --> Database Using Wizard. The Create Database wizard opens.
3. In the Database name field, type the name of the database:

   SAMPWHS
   

   If you use a different name, you must change the name of the database in the TBC Sample Targets warehouse target. Otherwise the sample will not run.

4. From the Default drive list, select a drive for the database.
5. In the Comments field, type a description of the database:

   Sample warehouse database
   

6. Click Finish. All other fields and pages in this wizard are optional. The SAMPWHS database is created and is listed in the DB2 Control Center.

There are several ways that you can register a database with ODBC. You can use the Client Configuration Assistant on Windows NT, the DB2 Command Line Processor, or the ODBC32 Data Source Administrator on Windows NT. The following instructions apply to the Client Configuration Assistant.

For more information about the Command Line Processor, see the DB2 Universal Database Command Reference. For more information about the ODBC32 Data Source Administrator, see the online help in the Administrator.

To register the SAMPWHS database with ODBC on Windows NT:

1. Start the Client Configuration Assistant by clicking Start --> Programs --> IBM DB2 --> Client Configuration Assistant. The Client Configuration Assistant window opens.
2. Select SAMPWHS from the list of databases.
3. Click Properties. The Database Properties window opens.
4. Select Register this database for ODBC. Use the default selection of As a system data source, which means that the data is available to all users on the system.
5. Click OK. All other fields are optional. The SAMPWHS database is registered with ODBC.

Viewing the sample data

The DWCTBC database contains the source tables for the sample warehouse. It contains the following tables:

• SALES
• INVENTORY
• PRODUCTION_COSTS
• GEOGRAPHIES
• SCENARIO
• TIME
• PRODUCT

To view the data in these tables:

1. From the DB2 Control Center, expand the objects in the DWCTBC database until you see the Tables folder.
2. Click the folder. In the right pane, you see all the tables for the database.
3. Find the table that you want to view. Right-click it, and click Sample Contents.

   Up to 200 rows of the table are displayed. The column names are displayed at the top of the window. You might need to scroll to the right to see all the columns and scroll down to see all the rows.

Viewing and modifying the sample metadata

To access the sample, you must log on to the Data Warehouse Center, specifying TBC_MD as the warehouse control database.

If the TBC_MD database is not local to the workstation that contains the warehouse server, you must catalog it as a remote database on the workstation. If it is not local to the workstation that contains the Data Warehouse Center administrative client, you must catalog it on that workstation as well.

To log on to the Data Warehouse Center:

1. Click Tools --> Data Warehouse Center in the DB2 Control Center. The system will automatically try to log on to the Data Warehouse Center using your DB2 Control Center user name and password. The Data Warehouse Center Logon window opens.
2. Click Advanced.

   The Advanced window opens.

3. In the Control database field, type TBC_MD, the name of the warehouse control database that is included in the sample.
4. In the Server host name field, type the TCP/IP host name for the workstation where the warehouse server is installed.
5. Click OK.

   The Advanced Logon window closes.

   The next time that you log on, the Data Warehouse Center will use the settings that you specified in the Advanced Logon window. If you first log on to the DB2 Control Center with a user ID that is defined to the Data Warehouse Center, then the Data Warehouse Center will use the same user ID to log you on automatically when you click Tools --> Data Warehouse Center.

6. In the User ID field of the Logon window, type the user ID that you specified when you created the Data Warehousing sample databases.
7. In the Password field, type the password for the user ID.
8. Click OK.

   The Data Warehouse Center Logon window closes, and you are now logged on to the Data Warehouse Center.

After you log on to the Data Warehouse Center, you can view and modify the properties of the sample metadata:

1. Expand the Warehouse Sources folder.

   The TBC Sample Sources warehouse source is displayed.

2. Right-click the TBC Sample Sources warehouse source and click Properties to view the properties of the warehouse source.
3. In the System Name field of the Database page, specify the host name of the workstation on which the sample database exists.

   This step is optional and is used only if you are using a database or file that has the same name as another database or file on a different workstation.

4. In the User ID field, type the user ID that you specified when you created the sample database.
5. In the Password field, type the password for the user ID.
6. Type the password again in the Verify Password field.
7. Click OK to close the notebook.
8. Click the Tables folder. The tables in the warehouse source are displayed on the right-hand side of the window.

   The warehouse source contains the following tables:

   • INVENTORY
   • PRODUCT
   • SCENARIO
   • TIME
   • PRODUCTION_COSTS
   • SALES
9. Right-click a table, and click Properties to view its properties.
10. Click OK or Cancel to close the notebook.
11. Expand the Warehouse Targets folder.

    The TBC Sample Targets warehouse target is displayed.

12. Right-click the TBC Sample Targets warehouse target and click Properties to view the properties of the warehouse target.
13. In the System Name field of the Database page, specify the host name of the workstation on which the sample database exists.

    This step is optional and is used only if you are using a database or file that has the same name as another database or file on a different workstation.

14. In the User ID field, type the user ID that you specified when you created the sample database.
15. In the Password field, type the password for the user ID.
16. Type the password again in the Verify Password field.
17. Click OK to close the notebook.
18. Expand the Subject Area folder.

    An icon for the TBC Sample subject area is displayed.

19. Right-click the TBC Sample subject area and click Properties to view the properties of the subject area.
20. After you have finished viewing the properties, click OK or Cancel to close the notebook.
21. Expand the TBC Sample subject area tree.
22. Expand the Processes folder.

    Four processes are displayed under the folder:

    • Sample Fact Table
    • Sample Product
    • Sample Scenario
    • Sample Time
23. Right-click the Sample Fact Table process, and click Open.

    The Process Model window opens.

    The process contains the following objects:

    • Three sources:
      • SALES
      • PRODUCTION_COSTS
      • INVENTORY
    • An SQL step named Fact Table SQL. The SQL step joins the sources in the process, and adds three columns: CITY_ID, TIME_ID, and SCENARIO_ID.
    • A target table named TARGET FACT TABLE.
    • A shortcut to the Select Time step. This shortcut is used in scheduling the sequence of steps to run in this sample.

    To view the metadata for an object in a process, right-click the object and click Properties. Click OK or Cancel to close the notebook.

24. Right-click the Sample Product process, and click Open.

    The Process Model window opens.

    The process contains the following objects:

    • A source named PRODUCT.
    • An SQL step named Select Product. The SQL step selects all the columns from the source.
    • A generated target table named TARGET PRODUCT.
    • A shortcut to the Select Scenario step. This shortcut is used in scheduling the sequence of steps to run in this sample.

    To view the metadata for an object in a process, right-click the object, and click Properties. Click OK or Cancel to close the notebook.

25. Right-click the Sample Scenario process, and click Open.

    The Process Model window opens.

    The process contains the following objects:

    • A source named SCENARIO.
    • An SQL step named Select Scenario. The SQL step selects all the columns from the source.
    • A generated target table named TARGET SCENARIO.
    • A shortcut to the Select Time step. This shortcut is used in scheduling the sequence of steps to run in this sample.

    To view the metadata for an object in a process, right-click the object and click Properties. Click OK or Cancel to close the notebook.

26. Right-click the Sample Time process, and click Open.

    The Process Model window opens.

    The process contains the following objects:

    • A source named TIME.
    • An SQL step named Select Time. The SQL step selects all the columns from the source.
    • A generated target table named TARGET TIME.
    • A shortcut to the Fact Table SQL step. This shortcut is used in scheduling the sequence of steps to run in this sample.

    To view the metadata for an object in a process, right-click the object, and click Properties. Click OK or Cancel to close the notebook.

27. Expand the Warehouse Schemas folder.
28. Right-click the warehouse schema and click Open.

    The Warehouse Schema Model window opens. It contains a join of the following tables:

    • TARGET FACT TABLE
    • TARGET PRODUCT
    • TARGET SCENARIO
    • TARGET TIME

Promoting the steps

Before you run the steps, you must promote them to test mode, and then to production mode. You must promote them in the order in which they will run:

1. Select Product
2. Select Scenario
3. Select Time
4. Fact Table SQL

To promote the steps to test mode:

1. Right-click the Sample Product process, and click Open.
2. Right-click the Select Product step, and click Mode --> Test.

   The Data Warehouse Center starts to create the target table, and displays a progress window. Wait until the Data Warehouse Center finishes processing before you start the next procedure.

3. Repeat steps 1 and 2 for the rest of the steps to promote the steps to test mode:
   • The Select Scenario step in the Sample Scenario process.
   • The Select Time step in the Sample Time process.
   • The Fact Table SQL step in the Sample Fact Table process.

To promote the steps to production mode:

1. Right-click the Sample Product process, and click Open.
2. Right-click the Select Product step, and click Mode --> Production.

   The Data Warehouse Center starts to create the target table, and displays a progress window. Wait until the Data Warehouse Center finishes processing before you start the next procedure.

3. Repeat steps 1 and 2 for the rest of the steps to promote the steps to production mode:
   • The Select Scenario step in the Sample Scenario process.
   • The Select Time step in the Sample Time process.
   • The Fact Table SQL step in the Sample Fact Table process.

Running the steps

To run the steps, you run the Sample Product step. The rest of the steps start, in sequence, after the Sample Product step finishes processing.

To run the Sample Product step:

1. From the main Data Warehouse Center window, click Warehouse --> Work in Progress.

   The Work in Progress window opens. You use the Work in Progress window to monitor the progress of all steps in the Data Warehouse Center that are running or scheduled.

2. Click Work in Progress --> Run New Step.

   The Run New Step window opens.

3. Select the Sample Product step, and click >.
4. Click OK.

   You should see an entry for the step that is running. While the step is running, it has a status of Populating. When it finishes running, it should have a status of Successful. As one step in the sequence finishes processing, the next step starts to run and has a status of Populating.

Viewing the sample warehouse data

The SAMPWHS database that you created contains the results of the step processing. It contains the following tables:

• TARGET FACT TABLE
• TARGET PRODUCT
• TARGET SCENARIO
• TARGET TIME

To view the data in these tables:

1. From the DB2 Control Center, expand the objects in the SAMPWHS database until you see the Tables folder.
2. Click the folder. In the right pane, you see all the tables for the database.
3. Find the table that you want to view. Right-click it, and click Sample Contents.

   Up to 200 rows of the table are displayed. The column names are displayed at the top of the window. You might need to scroll to the right to see all the columns and scroll down to see all the rows.