7 Using the OLAP Catalog

This chapter describes methods of creating a logical dimensional model. It includes the following sections:

Choosing a Method for Creating OLAP Catalog Metadata
Overview of the OLAP Catalog
Creating Metadata Using Enterprise Manager Database Control
Case Study: Creating Metadata for the GLOBAL Star Schema
Creating Metadata Using PL/SQL

Choosing a Method for Creating OLAP Catalog Metadata

You can use either Oracle Enterprise Manager Database Control or the CWM2 PL/SQL package to define a logical dimensional model in OLAP Catalog metadata. Both methods have restrictions on the format of the data sources, as described in the following topics. If your data sources do not conform to their requirements, then use Oracle Warehouse Builder to generate both a star schema and OLAP Catalog metadata.

For Source Data in a Basic Star or Snowflake Schema

The CWM1 write APIs, which are used by the OLAP Management tool in Database Control, create a database dimension object for each logical OLAP dimension. The database dimension object imposes the following restrictions on dimension tables and the related fact tables of a star or snowflake schema:

All hierarchies must be level-based; the schema cannot use parent-child dimension tables.
Multiple hierarchies defined for a dimension must have the same base level.
Level columns cannot contain NULLs.
Fact data must be unsolved, that is, it is stored only at the lowest level of the hierarchy, and all the data for a cube must be stored in a single fact table.

If your source data is a star or snowflake schema and conforms to these requirements, then you can use either Database Control or the CWM2 APIs, depending on your personal preference. The OLAP Management tool in Database Control provides a graphical user interface. The CWM2 APIs enable you to generate a SQL program that you can easily modify and port to other databases.

If your source data is a star or snowflake schema that does not conform with these requirements, then use the CWM2 APIs.

Figure 7-1 shows the tools for creating OLAP Catalog metadata.

Figure 7-1 Tools for Creating OLAP Catalog Metadata for Source Data

Diagram of tools used to create CWM metadata for source data

Description of "Figure 7-1 Tools for Creating OLAP Catalog Metadata for Source Data"

This chapter introduces the OLAP Management tool in Enterprise Manager Database Control and the CWM2 APIs.

For Dimension Tables with Complex Hierarchies

If your source data is a star or snowflake schema, but the dimension tables include any of the following variations, then use the CWM2 APIs:

Level columns containing NULLs, such as skip-level hierarchies
Multiple hierarchies with different base levels (sometimes called ragged hierarchies)
Multiple hierarchies with values mapped to different levels
Embedded total dimensions
Parent-child dimensions

If your schema contains parent-child dimension tables, then you must convert them to level-based dimension tables. The CWM2 write APIs include a package for this transforming symmetrical parent-child dimension tables.

For Other Schema Configurations

If you are using Oracle Warehouse Builder already to transform your data, then generating a metadata takes only an extra step. Warehouse Builder provides a graphical interface for designing a logical model, and deploys the model as metadata. It generates CWM2 metadata from its Design Repository.

If your data is stored in flat files or SQL tables, then you can use a manual method described in this guide. This method enables you to use the OLAP Catalog, but requires you to write data loading programs in the OLAP DML.

If you are upgrading from Oracle Express, then you may be able to automate the conversion process.

Overview of the OLAP Catalog

The OLAP Catalog defines logical dimensional objects and maps them to physical data sources. The logical objects are cubes, measures, dimensions, and so forth as described in "The Logical Dimensional Data Model". The physical data sources are the columns of a relational star or snowflake schema.

OLAP Catalog Components

The OLAP Catalog includes the following:

Metadata model tables: A set of relational tables within the database that instantiate the OLAP metadata model. These tables define all the OLAP metadata objects: dimensions, measures, cubes, measure folders, and so on. Within the metadata definitions are references to the actual data sources.
Write API: A set of PL/SQL packages for creating and editing OLAP metadata. These packages contain procedures for inserting, updating, and deleting rows in the model tables.
Read API: A set of relational views within the database that provide information about the metadata registered in the model tables.

Two versions of the OLAP Catalog are currently in use, CWM1 (also called CWM-Lite) and CWM2. Each version has its own metadata model tables, write API, and read API. However, applications can query a set of union views that contains all of the OLAP Catalog metadata, regardless of the write API used to generate it.

About CWM1

CWM1 is available through the OLAP Management tool of Enterprise Manager Database Control. You can use CWM1 only to describe a schema that complies with the requirements listed in "Choosing a Method for Creating OLAP Catalog Metadata". You can then use the OLAP Catalog to access the relational schema directly through OracleBI Beans.

You can view CWM1 metadata in the OLAP Management tool of Database Control.

About CWM2

CWM2 is available as a set of PL/SQL packages. You can use CWM2 to describe a star or snowflake schema that does not comply with the requirements for CWM1.

You can view CWM2 metadata by querying the read API.

Steps for Creating OLAP Metadata

Whether you create OLAP metadata programmatically or by using a graphic interface, you follow the same basic steps.

To create OLAP metadata:

Create logical dimensions. Specify the levels, attributes, and hierarchies associated with each one. ("Procedure: Defining a Logical Dimension in the OLAP Catalog")
Create logical cubes and specify their edges (dimensions). ("Procedure: Defining a Logical Cube in the OLAP Catalog")
Create logical measures that represent the fact data. Associate each measure with a cube. ("Procedure: Defining a Logical Cube in the OLAP Catalog")
Map the logical entities to the source data. ("Procedure: Defining a Logical Cube in the OLAP Catalog")

Creating Metadata Using Enterprise Manager Database Control

If your data warehouse complies with the requirements listed in "For Source Data in a Basic Star or Snowflake Schema", you can create OLAP metadata using the OLAP Management tool in Enterprise Manager Database Control.

You generate the SQL statements that create the metadata primarily by following the steps presented by a wizard or by completing a property sheet. If you wish, you can display the SQL statements before executing them.

Procedure: Accessing OLAP Management

Follow these steps to access OLAP Management:

Open Enterprise Manager Database Control in your browser.

The login page is displayed.
Enter a user name and password.
For the Grid Control edition of Enterprise Manager, then do the following from the Grid Control home page:
1. Click the Targets tab.
  
  The Hosts page is displayed.
2. Click the Database tab.
  
  The Databases page is displayed.
3. Click the link for the database you want to manage.
The Database home page is displayed.
Click the Administration tab.

The Administration page is displayed.
Look for the Warehouse heading. Links in the left column are used for Oracle OLAP: Cubes, OLAP Dimensions, and Measure Folders. These links are for OLAP Management.

The other Warehouse links are used only for relational warehouses that do not use the OLAP option. Do not use those links.

Defining Metadata for Dimension Tables

When creating OLAP metadata, you must first define the metadata objects for the dimension tables. These metadata objects are logical dimensions based on database dimension objects.

Information That You Supply for Dimensions

To define a dimension, you provide all the information that will be needed to label and aggregate the measures dimensioned by it, including:

The name of the dimension
The name of each level, and the columns that contain the data for each one
Join keys for levels that are stored in separate tablesThe name of each hierarchy, and the order of levels in each one
The name of each attribute, and the columns that contain data for each one
A display name and description for the dimension and each of its hierarchies, levels, and attributes

Time Dimension

Business analysis is performed on historical data, so fully defined time periods are vital. Your Time dimension table must have columns for period end dates and time span. This information supports time-series analysis, such as comparisons with earlier time periods. If your schema does not have these columns, then you can define Time as a normal dimension, but it will not support time-based analysis.

Procedure: Defining a Logical Dimension in the OLAP Catalog

Follow these steps to create a dimension and its associated levels, hierarchies, and attributes:

Start Enterprise Manager Database Control and access OLAP Management, as described in "Procedure: Accessing OLAP Management".
Click the OLAP Dimensions link under Warehouse.

The Dimensions page is displayed.
Click Create.

The Create Dimension page is displayed.
Choose Help for further information.

Defining Metadata for Fact Tables

After you have defined the metadata objects for the dimension tables, you can create metadata objects for the fact tables. These metadata objects are measures and cubes. A cube is a collection of identically dimensioned measures. Cubes and measures are defined entirely in the OLAP metadata; there are no corresponding database objects.

Information That You Supply for Cubes

When you define a cube, you identify information such as the following:

The name of the cube and the fact table associated with it. All measures in a cube must be from a single fact table.
The names of the dimensions and the levels in the dimension hierarchies that will be used in the cube.
The names of the measures and the columns in the fact table where the values for each measure are stored.
Default aggregation operators for each dimension of each measure (such as sum or average).
Any dimension dependencies.

Procedure: Defining a Logical Cube in the OLAP Catalog

Follow these steps to create a cube:

If you have not done so already, start Enterprise Manager Database Control and access OLAP Management, as described in "Procedure: Accessing OLAP Management".
Click the Cubes link.

The Cubes page is displayed.
Click Create.

The Create Cube page is displayed.
Choose Help for further information.
When you are done creating metadata, open SQL*Plus and issue this command:
```
EXECUTE CWM2_OLAP_METADATA_REFRESH.MR_REFRESH;
```
Create materialized views as described in Chapter 8.

Case Study: Creating Metadata for the GLOBAL Star Schema

The Global star schema conforms to all of the requirements of CWM1, so you can use the OLAP Management tool in Enterprise Manager Database Control.

The following procedures explain how to define just one dimension and one cube in the Global schema. However, you can follow this example by creating the metadata in a different schema.

See Chapter 2 for instructions for installing the Global schema.

Defining a Logical Time Dimension for the Global Schema

The TIMES_DIM table supports a single Calendar hierarchy with three levels (Month, Quarter, and Year) as described in Table 7-1.

Table 7-1 Global TIME Dimension Mapping: CALENDAR Hierarchy

TIME Objects in GLOBAL	GLOBAL.TIME_DIM Columns
`CALENDAR` hierarchy, `MONTH` level	`MONTH_ID`
`CALENDAR` hierarchy, `QUARTER` level	`QUARTER_ID`
`CALENDAR` hierarchy, `YEAR` level	`YEAR_ID`
`MONTH` Long Description attribute `MONTH` Short Description attribute	`MONTH_DSC`
`QUARTER` Long attribute Attribute `QUARTER` Short attribute Attribute	`QUARTER_DSC`
`YEAR` Long Description attribute `YEAR` Short Description attribute	`YEAR_DSC`
`MONTH` Time_Span attribute	`MONTH_TIMESPAN`
`QUARTER` Time_Span attribute	`QUARTER_TIMESPAN`
`YEAR` Time_Span attribute	`YEAR_TIMESPAN`
`MONTH` End_Date attribute	`MONTH_END_DATE`
`QUARTER` End_Date attribute	`QUARTER_END_DATE`
`YEAR` End_Date attribute	`YEAR_END_DATE`

These are the steps to define a logical Time dimension, using the general instructions in "Procedure: Defining a Logical Dimension in the OLAP Catalog".

On the Dimensions page, choose Create.

The Create Dimension page is displayed.
On the General tab, do the following:
- For Name, type time.
- For Schema, type global (or click the flashlight icon to display the Search and Select dialog).
- For Type, select Time.
On the Levels tab, click Add to display the Add Level page. Then do the following:
1. For Name, type year.
2. For Type, choose Year from the drop-down menu.
3. For Table, type GLOBAL.TIME_DIM.
4. Click Populate Columns.
5. Move YEAR_ID from Available Columns to Selected Columns.
6. Click OK.
7. Repeat these steps for the Quarter and Month levels, making the appropriate changes.
On the Hierarchies tab, click Add to display the Add Hierarchy page. Then do the following:
1. For Name, type calendar.
2. Choose Move All.
3. Use the up- and down-arrow keys to order the levels like this:
```
YEAR
QUARTER
MONTH
```
4. Click OK.
On the Attributes tab, add the Time_Span and End_Date attributes. The Long_Description and Short_Description attributes are already defined. On the Add Attribute or Edit Attribute pages, select all levels and map them to the columns shown in Table 7-1.
On the OLAP Options tab, type whatever descriptions you want to add.
Click OK to create the Time dimension.

When you have successfully created a dimension, it appears on the Dimensions page.

Defining a Logical Price and Cost Cube for the Global Schema

The PRICE_AND_COST_HISTORY_FACT table has a multi-column primary key, composed of two surrogate keys from two dimension tables, and two measures (UNIT_COST and UNIT_PRICE), as shown in Table 7-2.

Table 7-2 Global PRICE_AND_COST_CUBE Mapping

PRICE_AND_COST_HISTORY_FACT Columns	Logical Objects
`ITEM_ID`	`PRODUCT` dimension
`MONTH_ID`	`TIME` dimension
`UNIT_PRICE`	`UNIT_PRICE` measure
`UNIT_COST`	`UNIT_COST` measure

These are the steps to define a logical Price cube, using the basic steps listed in "Procedure: Defining a Logical Cube in the OLAP Catalog"

On the Cubes page, choose Create.

The Create Cube page is displayed.
On the General tab, do the following:
1. For Cube Name, type PRICE_CUBE.
2. For Display Name, type Price Cube.
3. For Schema, type GLOBAL.
4. For Description, type your own description.
5. For Fact Type, choose Table.
6. For Fact Schema, type GLOBAL.
7. For Fact Table, type PRICE_AND_COST_HISTORY_FACT.
On the Dimension tab, add the PRODUCT and TIME dimensions. Identify the appropriate foreign key columns in the fact tables, as shown in Table 7-2.
On the Measure tab, add the UNIT_PRICE and UNIT_COST measures.
On the Aggregation tab, choose MAX or another aggregation operator of your own choosing for both dimensions.

Creating Metadata Using PL/SQL

The CWM2 PL/SQL packages contain stored procedures that can create OLAP metadata for a variety of schema designs, as described in "Choosing a Method for Creating OLAP Catalog Metadata".

Before using these packages, make sure that you have performed any required preprocessing steps.

CWM2 Packages for Creating OLAP Dimensions

The following packages contain procedures that create metadata for dimension tables:

CWM2_OLAP_DIMENSION contains procedures for creating dimensions.
CWM2_OLAP_HIERARCHY contains procedures for creating hierarchies for dimensions.
CWM2_OLAP_LEVEL contains procedures for creating levels for dimensions and for associating levels with hierarchies.
CWM2_OLAP_LEVEL_ATTRIBUTE contains procedures for creating level attributes and associating them with levels.
CWM2_OLAP_DIMENSION_ATTRIBUTE contains procedures for creating dimension attributes and associating them with dimensions.

CWM2 Packages for Creating Cubes

The following packages contain procedures that create metadata for fact tables:

CWM2_OLAP_CUBE contains procedures for creating the dimensional structure of cubes.
CWM2_OLAP_MEASURE contains procedures for creating measures and associating them with cubes.

CWM2 Package for Mapping Metadata

The CWM2_OLAP_TABLE_MAP package contains procedures that map logical metadata entities to their physical data source. The data may be stored in relational tables, or it may be represented by relational views.

CWM2 Package for Creating Level-Based Dimension Tables

The CWM2_OLAP_PC_TRANSFORM package contains a procedure for transforming symmetrical parent-child dimension tables to level-based dimension tables. This conversion is necessary if the dimension will be accessed by OracleBI Beans.

CWM2 Packages for Classification and Validation

The following packages contain procedures for creating measure folders and validating OLAP metadata:

CWM2_OLAP_CATALOG provides procedures for creating and maintaining measure folders.
CWM2_OLAP_VALIDATE provides procedures for validating OLAP Catalog metadata.
CWM2_OLAP_METADATA_REFRESH provides procedures for refreshing metadata tables that support queries by OracleBI Beans against relational schemas.