|Oracle9i OLAP User's Guide
Release 2 (9.2)
Part Number A95295-01
Creating OLAP Catalog Metadata, 5 of 6
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.
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".
Follow these steps to start Oracle Enterprise Manager and access OLAP Management:
You see the main page.
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.
You see the Database Connect Information dialog box.
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.
You see the types of objects that you can create. This part of Oracle Enterprise Manager is for OLAP Management.
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:
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:
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:
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:
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.
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 (
Follow these steps to view a cube: