Creating Dimensions

The CWM2_OLAP_DIMENSION package contains procedures that establish dimension entities within the OLAP 2 Catalog.

Note: When you create an OLAP metadata entity, you are simply adding a row to an OLAP Catalog table that identifies all the entities of that type. Creating an entity of metadata does not fully define a dimension or a cube, nor does it involve any mapping to warehouse dimension tables or fact tables.

Completing the Dimension's Metadata

Creating dimensions is the first step in creating the OLAP metadata for a dimension. Typically you will create hierarchies and dimension attributes after creating the dimension and before creating the dimension levels and level attributes. Once the levels and level attributes are defined, you can map them to columns in one or more warehouse dimension tables.

Once you have created a dimension, you will need to call procedures in the following packages to fully define the dimension's metadata:

• CWM2_OLAP_DIMENSION_ATTRIBUTE to create dimension attributes
• CWM2_OLAP_HIERARCHY to create hierarchical relationships for the dimension's levels
• CWM2_OLAP_LEVEL to create levels and assign them to hierarchies
• CWM2_OLAP_LEVEL_ATTRIBUTE to create level attributes and assign them to dimension attributes
• CWM2_OLAP_TABLE_MAP to establish the mapping to columns in one or more dimension tables

Verifying Dimensional Metadata

To be valid, a dimension must have at least one level. If the dimension is a time dimension, it must also have the END DATE and TIME SPAN dimension attributes mapped for all levels.

All the dimension's levels and level attributes must be mapped to columns in dimension tables. Each level must map to one or more columns in one or more tables. If a level maps to multiple columns, they must be in the same table. Each level attribute must map to a single column in the same table as its associated level. If any of these columns is inaccessible, the dimension is not valid.

To test the validity of your dimensional metadata, use the VALIDATE_DIMENSION procedure in the CWM2_OLAP_VALIDATE package.