Introduction to OLAP Metadata - OLAP

Metadata is used throughout Oracle OLAP to define a logical multidimensional model:

• To describe the source data as multidimensional objects for use by the analytic workspace build tools.
  There are several methods of creating this type of metadata, as described in this chapter.
• To identify the components of logical objects in an analytic workspace for use by the refresh, aggregation, and enablement tools. Database standard form describes this metadata, which is generated by the workspace creation tools. Refer to Appendix A for a description of standard form.
• To describe relational views of analytic workspaces as multidimensional objects for use by OLAP applications.
  The application determines the type of metadata that is needed. The BI Beans require OLAP Catalog metadata, which is described in this chapter.

You only need to describe your source data; the OLAP tools can generate the equivalent metadata for the analytic workspace and the workspace views. The logical model is transformed along with the data. Figure shows the metadata transformations performed by the OLAP tools. These metadata types are discussed in this chapter.

Transformation of the Logical Model

Creating Metadata for Your Source Data

Defining the logical model is the first stage of metadata creation; the second stage is mapping the logical objects to physical data sources. Different types of metadata have different requirements for the storage format of the source data; you must choose the method that is appropriate for your data source. Moreover, there are multiple methods of creating metadata, including graphical user interfaces and PL/SQL APIs.

For Source Data in a Basic Star or Snowflake Schema

The CWM1 write APIs, which are used by the OLAP Management tool, 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 additional requirements, then you can use either Oracle Enterprise Manager or the CWM2 APIs, depending on your personal preference. The OLAP Management tool in Oracle Enterprise Manager 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 shows the tools for creating OLAP Catalog metadata.

Tools for Creating OLAP Catalog Metadata for Source Data

This chapter introduces the OLAP Management tool in Oracle Enterprise Manager 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 transformation.

For Other Schema Configurations

If you are using Oracle Warehouse Builder already to transform your data, then generating an analytic workspace takes only a few additional steps. Warehouse Builder provides a graphical interface for designing a logical model, and deploys the model as metadata. When you use the OLAP Bridge in Warehouse Builder, it generates CWM1 metadata from its Design Repository. Warehouse Builder also creates and populates an analytic workspace, and enables it for use by the BI Beans. 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.

Creating Metadata for Your Analytic Workspace

The tools for creating analytic workspaces comply with the requirements of database standard form, and transform the source metadata into standard form metadata. You do not need to perform any extra steps to maintain the standard form metadata when you use the OLAP tools to maintain the analytic workspace. You can make changes to the logical model in the metadata for the data source, and the refresh tool makes the appropriate changes to the standard form metadata. However, if you make manual changes to your analytic workspace, such as adding a measure, then you are responsible for making the appropriate changes to the standard form metadata.

Creating Metadata for Your Applications

Applications that use the BI Beans require OLAP Catalog metadata, and those that use Discoverer require an End User Layer. Both types of metadata require the data source to be in relational tables or views for SQL access. Thus, the enablers in Analytic Workspace Manager for these types of applications generate views of analytic workspace objects in the format required by the metadata, and then generate the metadata itself. The enablers transform the standard form metadata provided in the analytic workspace; you do not need to redefine the logical model.