Data Warehouse Requirements

The CWM2 APIs support and extend Enterprise Manager's warehouse requirements.

Basic Star or Snowflake Schema

Enterprise Manager creates OLAP metadata for star and snowflake schemas. It creates a database dimension object for each logical OLAP dimension. The database dimension object imposes the following restrictions on dimension tables and related fact tables:

• 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 these requirements, you can use either Enterprise Manager or the CWM2 APIs to create OLAP metadata.

Dimension Tables with Complex Hierarchies

If your dimension tables include any of the following variations, you must use the CWM2 APIs to create OLAP metadata:

• Level columns containing NULLs, such as in skip-level hierarchies
• Hierarchies with different lowest levels (sometimes called ragged hierarchies)
• Values mapped to different levels for multiple hierarchies

Solved and Unsolved Fact Data

Fact data is unsolved when it is stored at the lowest level of aggregation. Fact data is solved when it is stored with embedded totals.

Enterprise Manager creates OLAP metadata for unsolved fact data. With the CWM2 APIs, you can create OLAP metadata for both solved and unsolved fact data.

The CWM2 APIs also support multiple fact tables per cube. In this case, the data associated with a given combinations of hierarchies can be stored in a separate fact table. All the fact tables associated with a cube must have the same column structure.

Multidimensional Data

With the CWM2 APIs, you can create and populate analytic workspaces from a star schema and generate relational views of the resulting workspaces. You can then create OLAP metadata based on these views. Use the following procedure.

1. Use the CWM2_OLAP_AW_CREATE package, as described in Chapter 9 to create the workspace and create relational views of the data. These views take the place of fact tables and dimension tables.
2. Use other CWM2 APIs to create OLAP metadata based on these views.

   Note: If your data is stored in an analytic workspace that was created in some other way, for example by using OLAP Worksheet or the DBMS_AW package, you can use the CWM2_OLAP_AW_ACCESS package to generate views of the workspace. Then use other CWM2 APIs to create OLAP metadata based on these views.

   See Also: Chapter 3, "Developing OLAP Applications" and Chapter 12, "OLAP_TABLE" for more detailed explanations of the technology underlying views of analytic workspace data.

Parent-Child Dimensions

If the dimensions of your data are stored in parent-child dimension tables, then you must convert them to level-based dimensions before creating OLAP metadata. Use the following procedure:

1. Use the CWM2_OLAP_PC_TRANSFORM package, as described in Chapter 25 to convert the parent-child dimensions to a level-based dimensions.
2. Use other CWM2 APIs to create OLAP metadata based on the level-based dimensions.