Choosing the Right Metadata Creation Method

There are two tools for creating OLAP metadata:

• Oracle Enterprise Manager
• OLAP Catalog APIs (CWM2)

The tool that you can use depends on the structure of your data warehouse.

Basic Star or Snowflake Schema

OLAP Management is a tool in Oracle Enterprise Manager that provides an easy-to-use graphical interface to release 1 of the OLAP Catalog (CWM). This tool creates a database DIMENSION object for all logical dimensions defined in the metadata, which imposes the following requirements on the dimension tables:

• Dimension values must be stored in dimension tables in 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 data warehouse complies with all of these requirements, then you can use either Enterprise Manager or the CWM2 APIs for defining OLAP metadata. No preprocessing steps are required.

Your ITT tool may have created metadata already. If so, you should check it using the OLAP Management tool to see if the metadata structures are "OLAP enabled."

Dimension Tables with Complex Hierarchies

Release 2 of the OLAP Catalog (CWM2) supports the following variations in dimension tables:

• NULL values in level columns, such as in a skip-level hierarchy
• Data partitioning by hierarchy
• Different lowest levels for different hierarchies (sometimes called ragged hierarchies)
• Values mapped to different levels for multiple hierarchies
• For level-based dimensions, fact data can be either completely solved or completely unsolved. For parent/child dimensions, fact data must be completely unsolved.

If your dimension tables have any of these characteristics, then you must use the CWM2 APIs to create OLAP metadata. No preprocessing is required.

Multidimensional Data and Parent-Child Dimensions

If your data warehouse stores data in analytic workspaces or has parent/child (instead of level-based) dimensions, then you must use the CWM2 APIs to create OLAP metadata. For both types of warehouse storage, OLAP metadata creation is a two-step process:

1. Create relational views of the data. These views take the place of fact tables and dimension tables where they do not exist (in the case of multidimensional data) or are structurally unsuitable (in the case of parent-child dimension tables).
   • For dimension tables with parent-child relations, use the CWM2_OLAP_PC_TRANSFORM package to generate the views, as described in Chapter 16.
   • For multidimensional data stored in analytic workspaces, use the CWM2_OLAP_AW_ACCESS package, as described in Chapter 15.

     Note: When you use the CWM2_OLAP_AW_ACCESS package, multidimensional data is represented with the same metadata structures that represent relational data, and access to the analytic workspace is transparent to the client. You can also create metadata that directly represents the multidimensional objects in an analytic workspace. See Multidimensional Data Structures in the OLAP Catalog.

2. Use the OLAP Catalog CWM2 PL/SQL procedures to generate the metadata for all of the data regardless of whether it is stored in relational tables or analytic workspaces.

   See Also: Chapter 3, "Developing OLAP Applications" for a more detailed explanation of the technology underlying views of analytic workspace data. Chapter 15, "CWM2_OLAP_AW_ACCESS" for specific information about creating these views.

Multidimensional Data Structures in the OLAP Catalog

If some or all of your warehouse solution is stored in analytic workspaces, you may wish to create metadata that directly represents the multidimensional objects defined within the workspaces. This can be done using the CWM2 APIs.

Objects within analytic workspaces are defined and manipulated by the OLAP Data Manipulation Language (DML).

To create metadata for analytic workspace objects, use the procedures in the CWM2_OLAP_AW_OBJECT package. To map this metadata to objects in an analytic workspace, use the procedures in the CWM2_OLAP_AW_MAP package.

See Also: Chapter 25, "CWM2_OLAP_AW_OBJECT" for specific information about creating CWM2 metadata for analytic workspace objects, and Chapter 26, "CWM2_OLAP_AW_MAP" for specific information about mapping the metadata to objects in analytic workspaces. See Oracle9i OLAP Developer's Guide to the OLAP DML and the Oracle9i OLAP DML Reference help for specific information about the OLAP DML.