Creating Metadata Using Oracle Enterprise Manager - OLAP

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 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.

Procedure: Accessing OLAP Management

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

  1. Open Oracle Enterprise Manager 10g Grid Control in your browser.
    The login page is displayed.
  2. Enter a user name and password for Enterprise Manager.
    The Grid Control home page is displayed.
  3. Click the Targets tab.
    The Hosts page is displayed.
  4. Click the Database tab.
    The Databases page is displayed.
  5. Click the link for the database you want to manage.
    The Oracle Database home page is displayed.
  6. Click the Administration link.
    The Database Administration page is displayed.
  7. Look for the Warehouse heading. Links in the left column are used for Oracle OLAP. (The other Warehouse links are used only for relational warehouses that do not use the OLAP option. Do not use those links.)
    You see the types of objects that you can create: Cubes, OLAP dimensions, and measure folders. These links are 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 based on database dimension objects. You can use the Dimension Creation wizard or supply information directly in the Create Dimension dialog box.

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 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

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.
Typical levels and hierarchies for Time dimensions are suggested by the Dimension wizard, but you do not have to use them.

Procedure: Defining a Logical Dimension in the OLAP Catalog

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

  1. Start Oracle Enterprise Manager and access OLAP Management.
  2. Click the OLAP Dimensions link under Warehouse.
    If you have not already logged into the database, the Database Login page is displayed. Enter your login name and password for the database.
    The Search Objects page is displayed.
  3. Click Create.
    The Create Dimension page is displayed.
  4. Choose Help if you need 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 is stored.
  • Default aggregation operators for each dimension of each measure (such as sum or average).
  • Any calculation dependencies.

Procedure: Defining a Logical Cube in the OLAP Catalog

Follow these steps to create a cube:

  1. If you have not done so already, start Oracle Enterprise Manager and access OLAP Management.
  2. Click the Cubes link under Warehouse.
    If you have not already logged into the database, the Database Login page is displayed. Enter your login name and password for the database.
    The Search Objects page is displayed.
  3. Click Create.
    The Create Cube page is displayed.
  4. Choose Help if you need further information.

Note: If you are creating OLAP Catalog metadata for use by the BI Beans running directly against a relational schema (that is, with no analytic workspace, then your last step is to open SQL*Plus Worksheet and issue this command:

EXECUTE CWM2_OLAP_METADATA_REFRESH.MR_REFRESH

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

OLAP Topics