Dimensions of the Data Model

A dimension represents a single set of objects or events in the real world. Each dimension that you identify for the data model gets implemented as a dimension table. Dimensions are the qualifiers that make the measures of the fact table meaningful, because they answer the what, when, and where aspects of a question. For example, consider the following business questions, for which the dimensions are italicized:

• What accounts produced the highest revenue last year?
• What was our profit by vendor?
• How many units were sold for each product?

In the preceding set of questions, revenue, profit, and units sold are measures (not dimensions), as each represents quantitative or factual data.

Dimension Elements

A dimension can define multiple dimension elements for different levels of summation. For example, all the elements that relate to the structure of a sales organization might comprise one dimension. Figure 38 shows the dimension elements that the accounts dimension defines.

Figure 38. Dimension Elements in the Accounts Dimension

Dimensions are made up of hierarchies of related elements. Because of the hierarchical aspect of dimensions, users are able to construct queries that access data at a higher level (roll up) or lower level (drill down) than the previous level of detail. Figure 38 shows the hierarchical relationships of the dimension elements: accounts roll up to territories, and territories roll up to regions. Users can query at different levels of the dimension, depending on the data they want to retrieve. For example, users might perform a query against all regions and then drill down to the territory or account level for detailed information.

Dimension elements are usually stored in the database as numeric codes or short character strings to facilitate joins to other tables.

Each dimension element can define multiple dimension attributes, in the same way dimensions can define multiple dimension elements.

Dimension Attributes

A dimension attribute is a column in a dimension table. Each attribute describes a level of summary within a dimension hierarchy. The dimension elements define the hierarchical relationships within a dimension table; the attributes describe dimension elements in terms that are familiar to users. Figure 39 shows the dimension elements and corresponding attributes of the account dimension.

Figure 39. Attributes That Correspond to the Dimension Elements

Because dimension attributes describe the items in a dimension, they are most useful when they are text.

Dimension Tables

A dimension table is a table that stores the textual descriptions of the dimensions of the business. A dimension table contains an element and an attribute, if appropriate, for each level in the hierarchy. The lowest level of detail that is required for data analysis determines the lowest level in the hierarchy. Levels higher than this base level store redundant data. This denormalized table reduces the number of joins that are required for a query and makes it easier for users to query at higher levels and then drill down to lower levels of detail. The term drilling down means to add row headers from the dimension tables to your query. Table 9 shows an example of a dimension table that is based on the account dimension.