Oracle Data Warehouse Tuning for 10g

116

5.2

Dimension Object Syntax



Improved Performance

. Using dimensional object hierarchies can

help to improve query, query rewrite, and optimizer performance.

We will attempt to demonstrate all of the factors in this list later on in

this chapter.

Note:

When building dimensional hierarchies with dimension objects,

always attempt to build hierarchical structures from a business require-

ments perspective. Business people need to use the data in your data ware-

house. Programmers and database administrators only build data

warehouses; they do not actually use the data to retrieve real-world applica-

ble results.

Let’s take a brief look at the negative perspective of using dimension

objects in data warehouses.

Negative Aspects of Dimension Objects

The danger when using dimension objects is that not completely satisfying

all the dimensional relationships in a schema can result in the return of spu-

rious results. One very obvious example of this situation is shown in Figure

5.6, where some countries are not listed with state names; thus, the state

exists for the United States, or the province for Canada, but states as such

do not exist for European countries. Some countries do not have states or

provinces but, perhaps, other structures, such as counties or even principal-

ities. The obvious confusion that results can also confuse query rewrite and

ultimately the optimizer, resulting in potentially spurious results from que-

ries submitted by end users.

Before experimenting with the performance aspects of using Oracle

Dimension objects, we need to examine the syntax details and descriptive

explanations of dimension object syntax, allowing a clearer understanding

of exactly what dimension objects are and what they can be used to achieve.

5.2 Dimension Object Syntax

Syntax for managing dimension objects is shown in Figure 5.3 in the form

of the CREATE DIMENSION, ALTER DIMENSION, and DROP

DIMENSION statements.

5.2

Dimension Object Syntax 117

Chapter 5

5.2.1 CREATE DIMENSION Syntax

The CREATE DIMENSION statement allows creation of dimension

objects. The idea is to link normalized dimensions in separate entities into

hierarchies of level separated dimensions. The point of using the dimension

object is that the optimizer can use query rewrite using the dimension

objects, in much the same way that it can use materialized views to rewrite

queries: to read more compact physical data sets.

Note:

The CREATE DIMENSION system privilege is required to create

Oracle Dimension objects.

Let’s examine each section of Oracle Dimension object DDL statement

syntax.

Level Clause

Dimensions are constructed in hierarchies. This hierarchical structure is

somewhat akin to an upside-down tree, except with fewer branches than a

BTree index. The level clause allows definition of the level of a dimension

within a hierarchy. The level of a dimension within a hierarchy is the layer

in which the dimension resides, between or including the root or primary

parent, down to the lowest layered child dimension objects.

Figure 5.3

Dimension object

syntax.

118

5.2

Dimension Object Syntax

There are a number of restrictions, which, as usual, are basic common

sense. Generally, dimension objects are used to identify relationships

between completely normalized or different dimensional entities. Figure

5.4 shows a denormalized version on the left of the diagram, and the nor-

malized structure on the right of the diagram, using the data warehouse

LOCATION table.

Let’s begin creating a dimension object by identifying normalizable rela-

tionships within a single dimensional entity, the LOCATION entity shown

in Figure 5.4:

CREATE DIMENSION dm_location

LEVEL city IS (location.city)

LEVEL state IS (location.state)

LEVEL country IS (location.country)

LEVEL region IS (location.region);

Now let’s create a dimension object for the normalized structure in Fig-

ure 5.4:

DROP DIMENSION dm_location;

CREATE DIMENSION dm_location

LEVEL city IS (city.city_id)

LEVEL state IS (state.state_id)

LEVEL country IS (country.country_id)

LEVEL region IS (region.region_id);

Figure 5.4

Normalizing a

dimension.