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.
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
DIMENSION statements.
Dimension Object Syntax 117
Chapter 5
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.
The CREATE DIMENSION system privilege is required to create
Oracle Dimension objects.
Let’s examine each section of Oracle Dimension object DDL statement
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
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
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:
LEVEL city IS (location.city)
LEVEL state IS (location.state)
LEVEL country IS (location.country)
LEVEL region IS (location.region);
Now lets create a dimension object for the normalized structure in Fig-
ure 5.4:
DROP 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

Get Oracle Data Warehouse Tuning for 10g now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.