DS - Dimensions

Altering Dimensions

You can modify a dimension using the ALTER DIMENSION statement. You can add ordrop a level, hierarchy, or attribute from the dimension using this command.Referring to the time dimension mentioned earlier, you can remove theattribute 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, youcan drop one attribute column without dropping all attribute columns. This isillustrated in "Dropping and Creating Attributes with Columns"...

Validating Dimensions 

The information of a dimension object is declarative only and not enforced by thedatabase. If the relationships described by the dimensions are incorrect, incorrectresults could occur. Therefore, you should verify the relationships specified byCREATE DIMENSION using the DBMS_DIMENSION.VALIDATE_DIMENSIONprocedure periodically.This procedure is easy to use and has only four parameters:

■ dimension: the owner and name.

■ incremental: set to TRUE to check only the new rows for tables of thisdimension.

■ check_nulls: set to TRUE to verify that all columns that are not in the levelscontaining a SKIP WHEN NULL clause are not null.

■ statement_id: a user-supplied unique identifier to identify the result of eachrun of the procedure.The following example validates the dimension TIME_FN in the sh schema:@utldim.sqlEXECUTE...

Viewing Dimensions 

Dimensions can be viewed through one of two methods:

■ Using Oracle Enterprise Manager

■ Using the DESCRIBE_DIMENSION ProcedureUsing Oracle Enterprise ManagerAll of the dimensions that exist in the data warehouse can be viewed using OracleEnterprise Manager. Select the Dimension object from within the Schema icon todisplay all of the dimensions. Select a specific dimension to graphically display itshierarchy, levels, and any attributes that have been defined.Using the DESCRIBE_DIMENSION ProcedureTo view the definition of a dimension, use the DESCRIBE_DIMENSION procedure inthe DBMS_DIMENSION package. For example, if a dimension is created in the shsample schema with the following statements:CREATE DIMENSION channels_dimLEVEL channel IS (channels.channel_id)LEVEL channel_class IS (channels.channel_class)HIERARCHY...

Creating Dimensions 

Before you can create a dimension object, the dimension tables must exist in thedatabase possibly containing the dimension data. For example, if you create acustomer dimension, one or more tables must exist that contain the city, state, andcountry information. In a star schema data warehouse, these dimension tables alreadyexist. It is therefore a simple task to identify which ones will be used.Now you can draw the hierarchies of a dimension as shown in Figure. Forexample, city is a child of state (because you can aggregate city-level data up tostate), and country. This hierarchical information will be stored in the database object dimension.In the case of normalized or partially normalized dimension representation (adimension that is stored in more than one table), identify how these tables...