Creating Metadata Using Oracle Enterprise Manager

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

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.

Note: If you prefer to execute PL/SQL programs directly or your schema does not conform to the requirements of the OLAP Management tool, refer to "Creating Metadata Using PL/SQL".

Procedure: Accessing OLAP Management

Follow these steps to start Oracle Enterprise Manager and access OLAP Management:

1. Open the Oracle Enterprise Manager console.

   You see the main page.

2. Expand Databases by clicking the plus sign next to it.

   You see the list of service names for Oracle databases for which you have defined a connection.

   If the database that you want to manage is not listed, then from the Navigator menu choose Add Database to Tree. You will need to supply the host name, port number, and SID.

3. Expand the database that you want to manage.

   You see the Database Connect Information dialog box.

4. Type in your user name (one with the appropriate credentials) and password for that database.

   Tip: Select the Save as preferred credentials box if you wish to eliminate this step in future sessions. Your user name and encrypted password will be saved in a local file. For security, make sure that only you can run Oracle Enterprise Manager with your stored credentials. Later, if you wish to change this information, then choose Edit Local Preferred Credentials from the Configuration menu.

   The database folder will expand to show the various tools available for administering the database.

5. Expand OLAP.

   You see the types of objects that you can create. This part of Oracle Enterprise Manager is for OLAP Management.

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. You can use the Dimension Creation Wizard or supply information directly in the Create Dimension dialog box.

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 tables that contain the data for the dimension
• The name of each level, and the columns that contain the data for each level
• The number and order of levels in each hierarchy
• Join keys for levels that are stored in separate tables
• The columns that contain attributes for the levels
• A display name and description for the dimension and each of its hierarchies, levels, and attributes

Business analysis is performed on historical data, so fully defined time periods are vital. Special support for time dimensions is built into the metadata to allow for time-dependent analyses, such as comparisons with earlier time periods.

Your time dimension table must have columns for end-date and time-span, as described in "Time Dimensions". Typical levels and hierarchies for time dimensions are suggested by the Dimension Wizard, but you do not have to use them.

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

1. Start Oracle Enterprise Manager and access OLAP management, as described in "Procedure: Accessing OLAP Management".
2. To create a new dimension, right click on Dimensions, then choose one of the following:
   • Create Using Wizard to run the Dimension Wizard

     or

   • Create to edit a new dimension property sheet
3. Choose Help if you need additional 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. 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 is stored.
• Default aggregation operators for each dimension of each measure (such as sum or average).
• Any calculation dependencies.

Cubes and measures are defined entirely in the OLAP metadata; there are no corresponding database objects. In Oracle OLAP, measures are created in which to cache the data for analysis and display.

Follow these steps to create a cube:

1. Start Oracle Enterprise Manager and access OLAP Management, as described in "Procedure: Accessing OLAP Management".
2. Right-click on Cubes, then choose one of the following:
   • Create Using Wizard to run the Cube Wizard

     or

   • Create to edit a new cube property sheet
3. Choose Help if you need additional information.

Viewing Cubes

The Cube Viewer allows you to see the cube that you created in the same way that end-users might see it -- with the data presented in a BI Beans crosstab, as described in "Crosstabs". Moreover, you can select the data that you want to see by using the query builder.

Note: Only cubes created in Enterprise Manager are visible in the Cube Viewer. Enterprise Manager's OLAP Management feature uses the OLAP Catalog Release 1 APIs (CWM) and can only access metadata in the CWM metadata repository.

Procedure: Viewing cubes

Follow these steps to view a cube:

1. Start Oracle Enterprise Manager and access OLAP Management, as described in "Procedure: Accessing OLAP Management".
2. Expand the OLAP tree so that you can see the list of cubes.
3. Right-click on the cube you want to examine, then choose Cube Viewer.
4. If you need additional information, then search for the Help topic "Viewing a Cube's Data."