Business Intelligence Tutorial

Creating a star schema from within the Data Warehouse Center

In this lesson, you will create a star schema from the warehouse tables that you created in this tutorial. You can use this star schema for queries in the warehouse database. You can also export the star schema to OLAP Integration Server to create an OLAP database. In this lesson, you will export the star schema to OLAP Integration Server.

To do this lesson, you must have installed the OLAP Starter Kit. You also must have defined the dimension tables and fact table in Defining the rest of the tables for the star schema (optional).


Defining a star schema

In this exercise, you will define the star schema that is to contain the dimension and fact tables that you have defined in this tutorial.

To define a star schema:

  1. From the Data Warehouse Center, right-click the Warehouse Schemas folder.
  2. Click Define.

    The Define Warehouse Schema notebook opens.

  3. In the Name field, type the schema name:
    Tutorial Schema
    
  4. In the Administrator field, type your name as the contact for the schema.
  5. In the Description field, type the description of the schema:
    This is the TBC star schema
    
  6. Select the Use only one database check box.
  7. From the Warehouse target database list, select TUTWHS.

    For more information about the fields on this page, see the "Defining a warehouse schema" in the online help.

  8. Click OK to define the warehouse schema.

    The star schema is added to the tree underneath the Warehouse Schemas folder.


Opening the schema

In this exercise, you will open the star schema so that you can add the dimension tables and fact table to the schema in the next exercise.

To open the Tutorial Schema:

  1. Expand the Warehouse Schemas tree.
  2. Right-click on Tutorial Schema.
  3. Click Open.

Adding tables to the schema

In this exercise, you will add the dimension tables and fact tables that you defined in this tutorial--the LOOKUP_PRODUCT, LOOKUP_SCENARIO, LOOKUP_TIME, and FACT_TABLE tables--to the star schema.

To add the dimension tables and fact table to the star schema:

  1. Click the Add Data icon:
    Figure alldata not displayed.
  2. Click the canvas at the spot where you want to place the tables.

    The Add Data window opens.

  3. Expand the Warehouse Targets tree until you see a list of tables underneath the Tables folder.
  4. Select the LOOKUP_MARKET table.
  5. Click > to add the LOOKUP_MARKET table to the Selected source and target tables list.
  6. Repeat step 4 and step 5 to add the LOOKUP_PRODUCT, LOOKUP_SCENARIO, LOOKUP_TIME, and FACT_TABLE tables.
  7. Click OK. The tables that you selected are displayed on the window.
  8. Arrange the tables in the window so that the FACT_TABLE is in the center and there is one LOOKUP table in each corner.

Autojoining the tables

In this exercise, you will use the primary and foreign keys that you defined in Defining keys on target tables to automatically join the dimension tables and the fact tables.

To autojoin the tables:

  1. Click the Save icon in the toolbar to save your work:
    Figure wc_save not displayed.
  2. Select the LOOKUP_PRODUCT, LOOKUP_SCENARIO, LOOKUP_TIME, and FACT_TABLE tables by holding down the Ctrl key and clicking each table.
  3. Click the Autojoin icon in the toolbar:
    Figure autojn not displayed.

    The Data Warehouse Center draws green lines between the primary keys in the dimension tables and the foreign keys in the fact table. You can change these lines until you save. (To remove these lines, multiple-select them, right-click, and click Remove.

  4. Click the Save icon on the toolbar to save your work:
    Figure wc_save not displayed.

    The green autojoin lines become black.

  5. Close the Warehouse Schema Model window.

Exporting the star schema

In this exercise, you will export the star schema for use in OLAP Integration Server.

Before you begin:
Before you begin this section, you must have installed the OLAP Starter Kit, or the full DB2 OLAP Server(R) product.

To export the star schema:

  1. From the Data Warehouse Center window, right-click the Warehouse icon.
  2. Click Export Metadata --> OLAP Integration Server.

    The Export Warehouse Schema notebook opens.

  3. From the Available warehouse schemas list, select Tutorial Schema.
  4. Click >

    The Tutorial Schema moves to the Selected warehouse schemas list.

  5. Click the Integration Server tab.
  6. In the Catalog name field, type the name of the catalog for the Multidimensional analysis part of the tutorial:
    OLAPCATD
    
  7. In the Catalog table schema name field, type the schema name associated with the OM_INFO table.

    To find the name of the schema associated with the OM_INFO table:

    1. From the DB2 Control Center, expand the tree until you see the OLAPCATD database under Databases.
    2. Click on Tables and find the OM_INFO table in the Name column on the right. The schema associated with the OM_INFO table is visible in the Schema column.
    3. Return to the Export Warehouse Schema notebook and enter the schema name.
  8. In the Time dimension table field, select the LOOKUP_TIME table.

    You cannot change the dimension type after the schema is stored in the OLAP Integration Server catalog.

  9. In the User ID field, type the user ID under which you created the TBC_MD sample database. This is the user ID for the sample OLAP Integration Server catalog.
  10. In the Password field, type the password for the user ID. This is the password for the sample OLAP Integration Server catalog.
  11. In the Verify password field, type the password again.


    The GEOGRAPHIES SELECT statement on the SQL Statement page.

  12. Select Overwrite the existing model.
  13. Click OK to export the selected warehouse schemas to the OLAP Integration Server catalog.

    The notebook closes, and a progress indicator is displayed until the export is complete. When all the specified warehouse schemas are exported, the Export Information window opens, displaying success information or failure information about the export. Click Close to close this window.


What you just did

In this lesson, you created a star schema that consists of the LOOKUP_MARKET, LOOKUP_PRODUCT, LOOKUP_SCENARIO, LOOKUP_TIME, and FACT_TABLE tables. You exported the star schema to OLAP Integration Server.


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