Viewing Dimensions

Dimensions can be viewed through one of two methods:

■ Using Oracle Enterprise Manager
■ Using the DESCRIBE_DIMENSION Procedure

Using Oracle Enterprise Manager
All of the dimensions that exist in the data warehouse can be viewed using Oracle
Enterprise Manager. Select the Dimension object from within the Schema icon to
display all of the dimensions. Select a specific dimension to graphically display its
hierarchy, levels, and any attributes that have been defined.

Using the DESCRIBE_DIMENSION Procedure

To view the definition of a dimension, use the DESCRIBE_DIMENSION procedure in
the DBMS_DIMENSION package. For example, if a dimension is created in the sh
sample schema with the following statements:

CREATE DIMENSION channels_dim
LEVEL channel IS (channels.channel_id)
LEVEL channel_class IS (channels.channel_class)
HIERARCHY channel_rollup (
channel CHILD OF channel_class)
ATTRIBUTE channel DETERMINES (channel_desc)
ATTRIBUTE channel_class DETERMINES (channel_class);
Execute the DESCRIBE_DIMENSION procedure as follows:
SET SERVEROUTPUT ON FORMAT WRAPPED; --to improve the display of info
EXECUTE DBMS_DIMENSION.DESCRIBE_DIMENSION('SH.CHANNELS_DIM');

You then see the following output results:

EXECUTE DBMS_DIMENSION.DESCRIBE_DIMENSION('SH.CHANNELS_DIM');
DIMENSION SH.CHANNELS_DIM
LEVEL CHANNEL IS SH.CHANNELS.CHANNEL_ID
LEVEL CHANNEL_CLASS IS SH.CHANNELS.CHANNEL_CLASS
HIERARCHY CHANNEL_ROLLUP (
CHANNEL CHILD OF
CHANNEL_CLASS)
ATTRIBUTE CHANNEL LEVEL CHANNEL DETERMINES
SH.CHANNELS.CHANNEL_DESC
ATTRIBUTE CHANNEL_CLASS LEVEL CHANNEL_CLASS DETERMINES
SH.CHANNELS.CHANNEL_CLASS

Using Dimensions with Constraints

Constraints play an important role with dimensions. Full referential integrity is
sometimes enabled in data warehouses, but not always. This is because operational
databases normally have full referential integrity and you can ensure that the data
flowing into your data warehouse never violates the already established integrity
rules.
It is recommended that constraints be enabled and, if validation time is a concern, then the NOVALIDATE clause should be used as follows:

ENABLE NOVALIDATE CONSTRAINT pk_time;

Primary and foreign keys should be implemented also. Referential integrity
constraints and NOT NULL constraints on the fact tables provide information that query rewrite can use to extend the usefulness of materialized views.

In addition, you should use the RELY clause to inform query rewrite that it can rely
upon the constraints being correct as follows:
ALTER TABLE time MODIFY CONSTRAINT pk_time RELY;

Posted in: Dimension