Data Warehouse Center Administration Guide

Chapter 13. Creating a star schema from within the Data Warehouse Center

This chapter describes how to use the Data Warehouse Center to create a star schema. You can use the star schema in the DB2 OLAP Integration Server to define the multidimensional cubes necessary to support OLAP customers. A multidimensional cube is a set of data and metadata that defines a multidimensional database.

You would populate a star schema within the Data Warehouse Center with cleansed data before you use the data to build a multidimensional cube.

An OLAP model is a logical structure that describes how you plan to measure your business. The model takes the form of a star schema. A star schema is a specialized design that consists of multiple dimension tables, which describe aspects of a business, and one fact table, which contains the facts about the business. For example, if you have a mail-order business selling books, some dimension tables are customers, books, catalogs, and fiscal years. The fact table contains information about the books that are ordered from each catalog by each customer during the fiscal year. A star schema that is defined within the Data Warehouse Center is called a warehouse schema.

Table 19 describes the tasks involved in creating the warehouse schema and loading the resulting multidimensional cube with data using the Data Warehouse Center and the DB2 OLAP Integration Server. The table lists a task and tells you which product or component you use to perform each task. Each task is described in this chapter.

Table 19. Tasks to create a star schema and populate a multidimensional cube

Task Complete task from
Data Warehouse Center DB2 OLAP Integration Server
Define warehouse targets of relational data that you will use as sources for an OLAP multidimensional cube. X
Define the warehouse schema. X
Export the warehouse schema to the DB2 OLAP Integration Server. The warehouse schema will be used as a model in the DB2 OLAP Integration Server. X
Use the model (warehouse schema) to define hierarchies for the model.
X
Use the model to define a metaoutline on which your multidimensional cube will be based.
X
Create a command script that will be used to load the multidimensional cube with data.
X
Create a batch file that runs the command script.
X
Export metadata that defines the batch file to be run from the Data Warehouse Center. Objects that make it possible to schedule the loading and testing of the multidimensional cube from the Data Warehouse Center are automatically generated.
X
Schedule the step that was created by the export process so that you can populate the multidimensional cube. X

Task	Complete task from
Data Warehouse Center	DB2 OLAP Integration Server
Define warehouse targets of relational data that you will use as sources for an OLAP multidimensional cube.	X
Define the warehouse schema.	X
Export the warehouse schema to the DB2 OLAP Integration Server. The warehouse schema will be used as a model in the DB2 OLAP Integration Server.	X
Use the model (warehouse schema) to define hierarchies for the model.		X
Use the model to define a metaoutline on which your multidimensional cube will be based.		X
Create a command script that will be used to load the multidimensional cube with data.		X
Create a batch file that runs the command script.		X
Export metadata that defines the batch file to be run from the Data Warehouse Center. Objects that make it possible to schedule the loading and testing of the multidimensional cube from the Data Warehouse Center are automatically generated.		X
Schedule the step that was created by the export process so that you can populate the multidimensional cube.	X

Designing the warehouse schema in the Data Warehouse Center

Use the Warehouse Schema Model window to generate and store warehouse schema models that are associated with a warehouse. The warehouse schema models can be easily exported as metadata to the DB2 OLAP Integration Server (as an OLAP model).

Defining the warehouse schema

Before you define the warehouse schema you must define the warehouse target tables that you will use as source tables for your warehouse schema:

When you define a target table that will be used for your warehouse schema, select the Part of an OLAP schema check box (in the Define Warehouse Target Table notebook) for target table that you plan to use as a dimension table or a fact table.
Requirement: When you define the warehouse target for a warehouse schema, the warehouse target name must match exactly the name of the physical database on which the warehouse target is defined.

See Chapter 4, Setting up access to a warehouse for more information about defining a warehouse target.

Any warehouse user can define a table in a schema, but only warehouse users who belong to a warehouse group that has access to the warehouse target that contains the tables can change the tables. See Data Warehouse Center security for more information.

To define a warehouse schema:

From the Data Warehouse Center, right-click the Warehouse Schemas folder.
Click Define.
The Define Warehouse Schema notebook opens.
In the Name field, type the schema name.
Optional: In the Administrator field, type the name of a contact for the warehouse schema.
Optional: In the Description field, type a description of the warehouse schema. If you publish the metadata for the warehouse schema to an information catalog, the information in this field is used as a value for the Short description property for the warehouse schema.
Optional: In the Notes field, type any notes that you want to add. If you publish the metadata for the warehouse schema to an information catalog, the information in this field is used as a value for the Long description property for the warehouse schema.
Optional: To show tables in only one database, select the Use only one database check box, and select the database name from the Warehouse Target Database list.
Only those warehouse schemas that are made up of tables from one database can be exported to the DB2 OLAP Integration Server.
Click OK to define the warehouse schema.
The new warehouse schema is added to the tree under the Warehouse Schemas folder.

After you define your warehouse schema, you can update it by opening the Warehouse Schema Modeler window.

Adding tables and views to the warehouse schema

Use the Add Data window to add warehouse target tables, source tables, or source views to the selected warehouse schema. You do not define the tables for the accounts and time dimensions until you export the warehouse schema to the DB2 OLAP Integration Server as described in Exporting a warehouse schema to the DB2 OLAP Integration Server.

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

Open the Add Data window:
1. Expand the object tree until you find the Warehouse Schemas folder.
2. Right-click the warehouse schema, and click Open. The Warehouse Schema Modeler window opens.
3. Click the Add Data icon in the palette, and then click the icon on the canvas at the spot where you want to place the tables. The Add Data window opens.
Expand the Warehouse Targets tree until you see a list of tables under the Tables folder.
To add tables, select the tables that you want to include in the warehouse schema from the Available Tables list, and click > All tables in the Selected Tables list contain table icons on the Warehouse Schema Modeler canvas.
Click >> to move all the tables to the Selected Tables list. To remove tables from the Selected Tables, click <. To remove all tables from the Selected Tables, click <<.
To create new source and target tables, right-click the Table folder in the Available Tables tree, and click Define. The Define Warehouse Target Table or the Define Warehouse Source Table window opens.
Click OK. The tables that you selected are displayed in the window.

Autojoining tables

Use the Warehouse Schema Model window to autojoin tables. Selected tables are autojoined if the warehouse primary key and warehouse foreign key are defined and if you click Autojoin. For more information about defining keys on target tables, see Defining a warehouse target.

Expand the object tree until you find the Warehouse Schemas folder.
Right-click the warehouse schema, and click Open. The Warehouse Schema Modeler window opens.
Select the tables that you want to autojoin holding down the Ctrl key and clicking each table.
The primary key for each table is shown with an icon for the columns that are part of the key. Use the View menu to hide or show columns on the tables. Columns must be visible in both tables to create the link. All tables do not need to be in the same state when you select them in a group.
Click the Autojoin icon on the toolbar, or click Warehouse schema --> Autojoin. Primary and foreign key relationships are shown in green.
Click the Save icon on the toolbar, or click Warehouse schema --> Save to save your work.

Adding join relationships between non-keyed columns

Use the Warehouse Schema Model window to add join relationships between non-keyed columns in tables that are displayed on the canvas. You can join any two columns (multi-column joins are not allowed).

Expand the object tree until you find the Warehouse Schemas folder.
Right-click the warehouse schema, and click Open. The Warehouse Schema Modeler window opens. Ensure that there are at least two tables defined on the canvas.
Click the Join icon on the toolbar, and then click the column in the first table, while holding the mouse button down.
Move the cursor to the column of the second table to establish the join relationship. The join relationship lines are shown in black.
Click the Save icon on the toolbar, or click Warehouse schema --> Save to save the join relationships as part of the warehouse schema.

Figure 17 shows what your warehouse schema might look like after you have defined it.

Figure 17. Warehouse Schema Model window

Exporting a warehouse schema to the DB2 OLAP Integration Server

Use the Export Warehouse Schema notebook to export warehouse schemas to the DB2 OLAP Integration Server (as OLAP models).

To export the warehouse schema:

From the Data Warehouse Center, right-click the Warehouse node and click Export Metadata --> OLAP Integration Server.
The Export Warehouse Schema notebook opens.
From the Available list, select the warehouse schemas that you want to export. Only warehouse schemas defined with the Use only one database option are available to export.
Click >.
The selected schemas move to the Selected list.
Click the Integration Server tab.
In the Catalog name field, type the name of the DB2 OLAP Integration Server catalog to which you want to export the warehouse schema. The default name is the last catalog that was used to export warehouse schemas.
In the Catalog table schema name field, type the name of the catalog table schema that is used by the DB2 OLAP Integration Server catalog.
Optional: In the Time dimension table field, select the name of the Time dimension table. If you do not select a name, or if there are no tables in the list, the Regular dimension type will be used. If more than one schema is selected to be exported, this field is available only when the selected schemas have common dimension tables.
You cannot change the dimension type after the schema is stored in the DB2 OLAP Integration Server catalog.
Optional: In the Accounts dimension table field, select the name of the Accounts dimension table. If you do not select a name, or if there are no tables in the list, the Regular dimension type will be used. If multiple schemas are selected, this field is available only when the selected schemas have common dimension tables.
You cannot change the dimension type after the schema is stored in the DB2 OLAP Integration Server catalog.
In the User ID field, type the user ID that you will use to access the DB2 OLAP Integration Server catalog.
In the Password field, type the password for the user ID.
In the Verify password field, type the password again.
Select one of the following options to specify the action you want to take if the OLAP model already exists in the DB2 OLAP Integration Server catalog.
- Click Add the new data to the existing OLAP model, if the OLAP model name is the same as the warehouse schema name.
  If you select this option, only new information from the warehouse schema is added to the existing OLAP model. Existing information is not modified. For example, the fact table remains the same and you cannot change any dimensions if you select this option. You can add new dimensions.
- Click Display a message and not perform an export, if the OLAP model name is the same as the warehouse schema name.
  If you select this option, an error message is displayed and the existing model is not modified. This option is selected by default.
- Click Overwrite the existing model, if the OLAP model name is the same as the warehouse schema name.
  If you select this option, all the existing metadata about the OLAP model is deleted and replaced with the new metadata from the warehouse schema.
Click OK to export the selected warehouse schemas to the DB2 OLAP Integration Servercatalog.
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 OK to close this window.
You can view the log file that stores trace information about the export process. The file is located in the directory specified by the VWS_LOGGING environment variable. The default value of the VWS_LOGGING variable for Windows NT is \sqllib\logging, where x is the drive where the DB2 Universal Database is installed. The name of the log file is FLGNXHIS.LOG.

Working with a warehouse schema in the DB2 OLAP Integration Server

After you export the warehouse schema that you designed in the Data Warehouse Center, use the DB2 OLAP Integration Server to complete the design for your multidimensional cube.

To view the warehouse schema that you exported, open the OLAP model (warehouse schema) using the warehouse schema name that you used in the Data Warehouse Center. Make sure that you specify the warehouse target that you used to define the warehouse schema as the data source for the model. Figure 18 shows what your model looks like when you open it on the DB2 OLAP Integration Server desktop. The join relationships that you defined between the fact table and dimension tables are displayed.

Figure 18. Warehouse schema displayed as an OLAP model

From the DB2 OLAP Integration Server, you must complete the following tasks:

Create an OLAP metaoutline, which is a subset of the OLAP model (warehouse schema) on which you will base your multidimensional cube. Within the metaoutline, you define the hierarchies among the dimensions of the OLAP model. For detailed information about creating a metaoutline based on an OLAP model, see the OLAP Integration Server Model and User's Guide.
Create an outline that describes all the elements that are necessary for the Essbase database where your multidimensional cube is defined. For example, your outline will contain the definitions of members and dimensions, members, and formulas. You will also define the script that is used to load the cube with data. Then you will define a batch file from which to invoke the script. See Creating an outline and loading the data for the multidimensional cube in the DB2 OLAP Integration Server for more information.
Export the metadata that defines the batch file to the Data Warehouse Center so that you can schedule the loading of the cube on a regular basis. See Exporting the metadata to the Data Warehouse Center.

Creating an outline and loading the data for the multidimensional cube in the DB2 OLAP Integration Server

This section describes how to create an outline and associate it with a script that loads data into the multidimensional cube. After the outline is loaded with data, the resulting cube can be accessed through a spreadsheet program (such as Lotus^(R) 1-2-3^(R) or Microsoft Excel), so you can do analysis on the data.

See the online help for the DB2 OLAP Integration Server for detailed information on fields and controls in a window.

To create a database outline from the DB2 OLAP Integration Server desktop:

Open the metaoutline that you created based on the OLAP model (warehouse schema).
Click Outline --> Member and Data Load. The Essbase Application and Database window opens.
In the Application Name field, select the name of the OLAP application that will contain the Essbase database into which you want to load data. You can also type a name.
In the Database Name field, type the name of the OLAP database into which you want to load data.
Type any other options in the remaining fields, and click Next.
Type any other options in the Command Scripts window, and click Next.
Click Now in the Schedule Essbase Load window.
Click Finish.

The OLAP outline is created. Next, you must create the load script.

To create the load script:

Open the metaoutline for the warehouse schema.
Click Outline --> Member and Data Load. The Essbase Application and Database window opens.
In the Application Name field, select the name of the OLAP application that will contain the database into which you want to load data. You can also type a name.
In the Database Name field, type the name of the OLAP database into which you want to load data.
Type any other options in the remaining fields and click Next.
Type any other options in the Command Scripts window and click Next.
Click Only Save Load Script in the Schedule Essbase Load window.
Click Save Scripts. The Save Script As window opens.
Type a name and file extension for the command script file.
Click Finish.

The new command script that loads the multidimensional cube with data is created in the ..\IS\Batch\ directory. The command script contains the following items:

The name of the DB2 database that contains the source data for the cube.
The Essbase database that will store the cube.
The OLAP catalog name that will be used for the cube.
The instructions that load the data into the cube.
Any calculation options that you specified when you defined the script.

Figure 19 shows an example of a command script named my_script.script. The line-break on the LOADALL entry is not significant. You can type the entry all on one line.

Figure 19. OLAP command script: my_script.script

LOGIN oisserv SETSOURCE "DSN=tbc;UID=user;PWD=passwd;" SETTARGET "DSN=essserv;UID=user;PWD=passwd" SETCATALOG "DSN=TBC_MD;UID=user;PWD=passwd;" LOADALL "APP=app1;DBN=db1;OTL=TBC Metaoutline;FLT_ID=1;OTL_CLEAR=N; CALC_SCRIPT=#DEFAULT#;" STATUS

After you create the outline and command script, you must create a batch file that invokes the script. The batch file is used as a parameter for the Data Warehouse Center step that runs the script to load the multidimensional cube.

To create the batch file, use a text editor and enter commands to invoke the script. You might create a file similar to the one in Figure 20 to run my_script.script. Do not enter the line break in this example.

Figure 20. Bat file that invokes command script: my_script.bat

C:\IS\bin\olapicmd < "C:\IS\Batch\my_script.script" > "C:\IS\Batch\my_script.log"

The my_script.log log file shows information about the metadata that is exported to the Data Warehouse Center. It also shows if the export process was successful.

Exporting the metadata to the Data Warehouse Center

Use the DB2 OLAP Integration Server Administration Manager to export the metadata for the batch file (that loads the multidimensional cube) to the Data Warehouse Center. The export process creates objects in the Data Warehouse Center that make it possible load and test the cube.

Before you export the metadata, make certain you have already defined the tables for your warehouse schema as described in Designing the warehouse schema in the Data Warehouse Center.

To export metadata to the Data Warehouse Center, start from the DB2 OLAP Integration Server desktop:

Click Tools --> Administration Manager. The Administration Manager window opens.
Figure 21. Administration Manager window
Expand the Applications folder, and right-click the database whose metadata you want to export.
Click Actions --> Export to Data Warehouse Center. The Export to Data Warehouse Center window opens.
In the OLAP Model Name field, type the name of the model (warehouse schema) on which your metaoutline is based.
In the Catalog DSN field, type the name of the catalog database source name in the DB2 OLAP Integration Server that contains the model on which your metaoutline is based.
In the User Name field, type the user ID that you use to connect to the catalog database.
In the Password field, type the password for the user ID.
In the Table Schema field, type the table schema that is used for tables in DB2 OLAP Integration Server catalog.
In the Control Database field, type the name of the warehouse control database to which you want to export the metadata.
In the User Name field (for Data Warehouse Center), type the user ID that you use to connect to the warehouse control database.
In the Password field, type the password for the user ID.
In the Table Schema field, type the table schema that you use for the tables in your warehouse schema.
In the Batch file to invoke OLAP Integration Server script field, type the fully qualified name for the batch file that runs the command script to load the multidimensional cube.
For example, type c:\is\batch\my_script.bat.
Click OK.
The metadata for the batch file is exported to the Data Warehouse Center.

See the log file for information about the metadata that is exported to the Data Warehouse Center.

See Loading the multidimensional cube from the Data Warehouse Center for information about the objects that are created in the Data Warehouse Center.

Loading the multidimensional cube from the Data Warehouse Center

When you export metadata from the DB2 OLAP Integration Server, the following Data Warehouse Center objects are created and associated with the target tables in the warehouse schema:

A subject area named OLAP cubes
A process within the subject area named in the following format:
```
servername.applicationname.databasename.outlinename
```
servername
The name of the OLAP server.
applicationname
The name of the OLAP server application that contains the database that is identified by databasename.
databasename
The name of the OLAP server database that contains the outline that is identified by outlinename.
outlinename
The name of the OLAP server outline whose metadata you exported.
A step named in the same format as the process.
The step uses the batch file (whose metadata you exported) as a parameter. When you click the Parameters tab in the step's Properties notebook, the Parameter value column displays the fully qualified name of the batch program that calls the command script you created in the DB2 OLAP Integration Server. The value in the Parameter value column matches the batch file you specified in the Batch file to invoke OLAP Integration Server script field of the DB2 OLAP Integration Server Export to Data Warehouse Center window. For example, the Parameter value column, might show c:\is\batch\my_script.bat.
When you run the step, the batch file invokes the script to load the multidimensional cube.

When you select the process, the tables that comprise the warehouse schema are displayed in the right pane of the Data Warehouse Center. When the step is run, the warehouse schema tables are used as source tables to build and populate the multidimensional cube. The dimension tables are used as sources for the members of the OLAP model and the fact table is the source for the measures (data in the multidimensional cube). Figure 22 shows the objects that are created displayed in the Data Warehouse Center.

Figure 22. Objects created when metadata is exported from the DB2 OLAP Integration Server

See Metadata mappings between the DB2 OLAP Integration Server and the Data Warehouse Center for detailed information on how DB2 OLAP Integration Server metadata maps to Data Warehouse Center metadata.

Creating a schedule to populate the multidimensional cube

You can schedule the step that loads the multidimensional cube, and you can promote it so that it runs on a regular basis. To schedule and promote the step:

Right-click the step, and click Schedule. The Schedule notebook opens.
Use the Schedule page to define the intervals at which you want the step to run.
Use the Task Flow page of the Schedule notebook to specify that this step should run only upon successful completion of the step that you defined to create the warehouse target tables for the warehouse schema (described in Designing the warehouse schema in the Data Warehouse Center). For more information about scheduling a step to run based on the success or failure of another step, see Scheduling warehouse processes.
Right-click the step, and click Mode. You can click either Test or Production to run the step.

After you successfully schedule and test the step, the multidimensional cube that was built using your warehouse schema is populated. Figure 23 shows the Work in Progress window as a multidimensional cube is being populated.

Figure 23. Work in Progress window with a step that is populating a cube

Publishing metadata about your warehouse schema

You can use the Publish Data Warehouse Center Metadata notebook to publish to the information catalog the metadata that describes the tables in your warehouse schema. See Publishing Data Warehouse Center metadata for more information.

A warehouse schema maps to a star schema in the Information Catalog Manager. You use a command line interface to publish DB2 OLAP Integration Server metadata to the information catalog. When you publish DB2 OLAP Integration Server metadata objects, a linked relationship is created between a "dimensions within a multi-dimensional database" object type and a table object in the information catalog. If you publish your warehouse schema to the information catalog and publish the DB2 OLAP Integration Server metadata, you will have a complete depiction of the metadata for the OLAP model. See the Information Catalog Manager Administration Guide for more information about publishing DB2 OLAP Integration Server metadata.

[ Top of Page | Previous Page | Next Page ]