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"...

Read More

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...

Read More

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...

Read More

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...

Read More

What are Dimensions?

A dimension is a structure that categorizes data in order to enable users to answerbusiness questions. Commonly used dimensions are customers, products, and time.For example, each sales channel of a clothing retailer might gather and store dataregarding sales and reclamations of their Cloth assortment. The retail chainmanagement can build a data warehouse to analyze the sales of its products across allstores over time and help answer questions such as:■ What is the effect of promoting one product on the sale of a related product that isnot promoted?■ What are the sales of a product before and after a promotion?■ How does a promotion affect the...

Read More

Parallelization Rules for SQL Statements

A SQL statement can be parallelized if it includes a parallel hint or if the table or index being operated on has been declared PARALLEL with a CREATE or ALTER statement.In addition, a DDL statement can be parallelized by using the PARALLEL clause.However, not all of these methods apply to all types of SQL statements.Parallelization has two components: the decision to parallelize and the DOP. Thesecomponents are determined differently for queries, DDL operations, and DMLoperations.To determine the DOP, Oracle looks at the reference objects:■ Parallel query looks at each table and index, in the portion of the query beingparallelized, to determine which is the reference table. The basic rule is to pick thetable or index with the largest DOP.■ For parallel DML (INSERT, UPDATE, MERGE, and DELETE),...

Read More

Types of Parallelism

The following types of parallelism are discussed in this post:■ Parallel Query■ Parallel DDL■ Parallel DML■ Parallel Execution of Functions■ Other Types of ParallelismParallel QueryYou can parallelize queries and subqueries in SELECT statements. You can alsoparallelize the query portions of DDL statements and DML statements (INSERT,UPDATE, and DELETE). You can also query external tables in parallelParallel Queries on Index-Organized TablesThe following parallel scan methods are supported on index-organized tables:■ Parallel fast full scan of a nonpartitioned index-organized table■ Parallel fast full scan of a partitioned index-organized table■ Parallel index range scan of a partitioned index-organized tableThese scan methods can be used for index-organized tables with overflow areas andfor...

Read More