Defining Dimensions

A dimension is an object that holds a list of values that provide the organization for one or more variables. A dimension value is similar to a key in a relational table; either alone or with other dimension values, it uniquely identifies a data value. For example, if you have sales data with a separate sales figure for each month, then the data has a month dimension; that is, the data is organized by month. The dimension values you add might be feb02, mar02, and apr02.

A simple dimension has a list of values that all have the same data type. The OLAP DML supports both flat and hierarchical simple dimensions:

A flat dimension exists when the values within a dimension are all at the same level. No value is the child or parent of another value.

A hierarchical dimension exists when values are in a one-to-many (parent-to-child) relationship with each other. A hierarchical dimension is a means of organizing and structuring this type of data within a single dimension. You can then use it to dimension a variable that contains data for all the levels. Some dimensions have multiple hierarchies. You specify the parent-to-child relationships of the dimension values by creating a self-relation.

Composite and conjoint dimensions can be derived from these base simple dimensions to store sparse data more efficiently in a multidimensional format.

Concat dimensions can also be based on simple dimensions or on conjoint dimensions. You can represent a hierarchy with a concat dimension that is has two or more simple flat dimensions among its base dimensions. You can use concat dimensions to easily map dimensions in an analytic workspace to columns in relational tables and thereby promote more efficient loading of data from the relational structures into the analytic workspace structures. The base dimensions of a concat dimension can be of different data types.

Determining What Dimensions to Define

If you want your analytic workspace to contain only flat dimensions, you need to define dimensions for each level of detail in your data that users will access.

For example, if your company is divided into sales districts and each district handles several store accounts, then you need to decide whether you want sales figures for every store or only for each district. As shown in the following table, the answer to this question determines the structure of your analytic workspace.

IF . . .

THEN . . .

you need Store data,

you can define a store dimension.

you always look at each district as a whole,

all you need is a district dimension.

you want to look at data both ways,

you can organize data by store and view aggregates of data by district by creating both a store and a district dimension with a relation between them.

Sometimes, you will decide to store data of varying levels of aggregation within a single variable, because this type of storage affords a quicker response time for users who want to view the data. In this case, you can define a single hierarchical dimension that has all the values of the hierarchy or you can define a concat dimension that is based on simple flat dimensions. For example, each flat dimension might have the values of one of the levels of the hierarchy.

For example, if you want to look at data both ways instead of defining both a store and a district dimension as described above, then you can define a single hierarchical dimension. This hierarchical dimension would contain all of the values for stores and districts. If you dimension a variable by this hierarchical dimension, then you can store data of varying levels of aggregation within that single variable. You can still view store data and district data separately.

You can achieve a similar result by defining a concat dimension that has as its base dimensions the store and district dimensions. The concat dimension would also contain all of the store and district values. As with a hierarchical dimension, if you dimension a variable by this concat dimension, then you can keep data of varying levels of aggregation within that variable and still view store data and district data separately.

If you already have simple flat dimensions in your analytic workspace or if you create simple flat dimensions so that you can easily map them to columns in relational dimensions, then you might use a concat dimension instead of a hierarchical dimension. Another reason to use a concat dimension instead of a hierarchical simple dimension is that all of the values of a simple dimension must be unique whereas in a concat dimension the same value can exist in two or more of the base dimensions of the concat.

How Data For Simple Flat Dimensions Is Stored

The data for a simple flat dimension is stored in a one-dimensional array. As you add values to the dimension, each new value is stored at the end of the array.

Assume that the product dimension has been defined as a TEXT data type. The first three values that are added to the dimension are TENTS, CANOES, and RACQUETS. At this point, a report of the dimension shows the following values.

PRODUCT
--------------
TENTS
CANOES
RACQUETS

The product dimensionvalues are actually stored as shown below.

Position

1

2

3

Value

TENTS

CANOES

RACQUETS

Later, the values SPORTSWEAR and FOOTWEAR are added. At this point, a report of the dimension shows the following values.