Dimension Object Syntax
. 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
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-
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