Designing fact tables
Dimension tables may be important, but the facts are what really matter. So, before going into fact tables, let's look at the facts first. We already learned about facts in the dimensional modeling section, but we need to look at facts in a bit more detail. When we do, we can distinguish three types of facts:
- Additive facts
- Semi-additive facts
- Non-additive facts
We will discuss them in turn.
Understanding additive facts
Additive facts are numerical facts that you can add together to create facts at a higher aggregation level. Almost all reports use aggregated facts. For instance, a report showing sales by month is an aggregated report. All individual sales transactions of the same month are put together to form one new row. The sales are calculated by adding all the sales amounts together to form the month's sales. The overall sales are then calculated by adding all the sales amounts of all the months together.
Next to...