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.
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.