7/25/2019 Informatica task manager

1/2

ALTERING DIMENSIONS4:28 AM DIVJEEVYou 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" on page 10-6, whichshows the following statement:

ALTER DIMENSION product_dimDROP ATTRIBUTE size LEVEL prod_type COLUMN Prod_TypeSize;

If you try to remove anything with further dependencies inside the dimension, Or

acleDatabase rejects the altering of the dimension. A dimension becomes invalid if youchange any schema object that the dimension is referencing. For example, if thetableon 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_dimADD LEVEL f_year IS times.fiscal_year SKIP WHEN NULL;

You cannot, however, modify a level that contains a SKIP WHEN NULL clause. Inste

ad,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 theALL_DIMENSIONS data dictionary view. To revalidate the dimension, use theCOMPILE option as follows:

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

Posted in: DimensionYou might also like:Creating Dimensions

What are Dimensions?Data Warehousing ObjectsViewing DimensionsLinkwithinRelated Posts Plugin for WordPress, Blogger...PLEASE SHARE

Twitter Delicious Facebook Digg Stumbleupon Favorites More

7/25/2019 Informatica task manager

2/2

DW FORUM

Interact with other DW-BI professionals in our new DW-BI ForumPICK THE TOPPER!!

Pick the Topper!!DEAR READER

At Tutorial Blogs we value your opinion - Please Respond-Click Here

LABELS

Change Data Capture (3)Constraints (3)Cost-Based Optimization (1)Data Warehouse (8)Dimension (5)Error Logging and Handling (1)ETL (13)Extraction (4)Index (10)Loading (2)Materialized Views (12)

MERGE (1)Parallel Execution (4)Partitioning (18)Purging Data (1)Query Rewrite (6)Schema (1)Snowflake Schema (1)Star Query (1)Star Schema (1)Star Transformation (2)Transformation (3)