Identifying the Dimensions and Hierarchies

Home | Previous Page | Next Page Dimensional Databases > Building a Dimensional Data Model > Building a Dimensional Data Model >

Identifying the Dimensions and Hierarchies

After you determine the granularity of the fact table, it is easy to identify the primary dimensions for the data model because each component that defines the granularity corresponds to a dimension. Figure 40 shows the relationship between the granularity of the fact table and the dimensions of the data model.

Figure 40. The Granularity of the Fact Table Corresponds to the Dimensions of the Data Model

begin figure description - This figure is described in the surrounding text. - end figure description

With the dimensions (customer, product, geography, time) for the data model in place, the schema diagram begins to take shape.

Tip:

At this point, you can add additional dimensions to the primary granularity of the fact table, where the new dimensions take on only a single value under each combination of the primary dimensions. If you see that an additional dimension violates the granularity because it causes additional records to be generated, then you must revise the granularity of the fact table to accommodate the additional dimension. For this data model, no additional dimensions need to be added.

You can now map out dimension elements and hierarchies for each dimension. Figure 41 shows the relationships among dimensions, dimension elements, and the inherent hierarchies.

Figure 41. The Relationships Between Dimensions, Dimension Elements, and the Inherent Hierarchies

In most cases, the dimension elements need to express the lowest possible granularity for each dimension, not because queries need to access individual low-level records, but because queries need to cut through the database in precise ways. In other words, even though the questions that a data warehousing environment poses are usually broad, these questions still depend on the lowest level of product detail.