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

Choosing the Measures for the Fact Table

The measures for the data model include not only the data itself, but also new values that you calculate from the existing data. When you examine the measures, you might discover that you need to make adjustments either in the granularity of the fact table or the number of dimensions.

Another important decision you must make when you design the data model is whether to store the calculated results in the fact table or to derive these values at runtime.

The question to answer is, "What measures are used to analyze the business?" Remember that the measures are the quantitative or factual data that tell how much or how many. The information that you gather from analysis of the sales business process results in the following list of measures:

Use these measures to complete the fact table in Figure 42.

Figure 42. The Sales Fact Table References Each Dimension Table
begin figure description - This figure is described in the surrounding text. - end figure description

Using Keys to Join the Fact Table with the Dimension Tables

Assume, for the moment, that the schema of Figure 42 shows both the logical and physical design of the database. The database contains the following five tables:

Each of the dimensional tables includes a primary key (product, time_code, customer, district_code), and the corresponding columns in the fact table are foreign keys. The fact table also has a primary (composite) key that is a combination of these four foreign keys. As a rule, each foreign key of the fact table must have its counterpart in a dimension table. Furthermore, any table in a dimensional database that has a composite key must be a fact table, which means that every table in a dimensional database that expresses a many-to-many relationship is a fact table.

Tip:
The primary key should be a short numeric data type (INT, SMALLINT, SERIAL) or a short character string (as used for codes). Do not use long character strings as primary keys.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]