Altering Dimensions

You can modify a dimension using the ALTER DIMENSION statement. You can add or
drop a level, hierarchy, or attribute from the dimension using this command.

Referring to the time dimension mentioned earlier, you can remove the
attribute fis_year, drop the hierarchy fis_rollup, or remove the level fiscal_
year. In addition, you can add a new level called f_year as in the following:

ALTER DIMENSION times_dim DROP ATTRIBUTE fis_year;
ALTER DIMENSION times_dim DROP HIERARCHY fis_rollup;
ALTER DIMENSION times_dim DROP LEVEL fis_year;
ALTER DIMENSION times_dim ADD LEVEL f_year IS times.fiscal_year;

If you used the extended_attribute_clause when creating the dimension, you
can drop one attribute column without dropping all attribute columns. This is
illustrated in "Dropping and Creating Attributes with Columns" on page 10-6, which
shows the following statement:

ALTER DIMENSION product_dim
DROP ATTRIBUTE size LEVEL prod_type COLUMN Prod_TypeSize;

If you try to remove anything with further dependencies inside the dimension, Oracle
Database rejects the altering of the dimension. A dimension becomes invalid if you
change any schema object that the dimension is referencing. For example, if the table
on which the dimension is defined is altered, the dimension becomes invalid.

You can modify a dimension by adding a level containing a SKIP WHEN NULL clause,
as in the following statement:
ALTER DIMENSION times_dim
ADD LEVEL f_year IS times.fiscal_year SKIP WHEN NULL;

You cannot, however, modify a level that contains a SKIP WHEN NULL clause. Instead,
you need to drop the level and re-create it.
To check the status of a dimension, view the contents of the column invalid in the
ALL_DIMENSIONS data dictionary view. To revalidate the dimension, use the
COMPILE option as follows:

ALTER DIMENSION times_dim COMPILE;
Dimensions can also be modified or deleted using Oracle Enterprise Manager.

Posted in: Dimension