Choose 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:
- Revenue
- Cost
- Units sold
- Net profit
Use these measures to complete the fact table in the following
figure.
Figure 1. The Sales fact table references
each dimension table
The elements of the Sales Fact table are: product code, time code,
district code, customer code, revenue, cost, units sold, and net profit.
Some of these elements join the Sales fact table to the dimension
tables.
- Product code element
- Joins the Sales fact table to the Product dimension table. There are no other elements in the Product dimension table.
- Time code element
- Joins the Sales fact table to the Time dimension table. There are no other elements in the Time dimension table.
- District code element
- Joins the Sales fact table to the Geography dimension table. There are no other elements in the Geography dimension table.
- Customer code element
- Joins Sales fact table to Customer dimension table. There are no other elements in the Customer dimension.
In this model, additional space is left in the dimensional tables to add more elements. You will identify the other elements when you choose the attributes for each dimension table.