Additive, Semi-Additive, Non-Additive

In data warehousing, a fact table contains measurements or facts, which can be categorized into three types: additive, semi-additive, and non-additive.

1. Additive Fact: Additive facts can be summed up across all dimensions of the fact table. For example, sales or revenue can be summed up by any dimension like time, product, region, etc.

Examples

• Sales revenue

• Quantity sold

• Profit

• Number of website visits

• Number of clicks on a banner ad

2. Semi-Additive Fact: Semi-additive facts can be summed up across some dimensions, but not all. For example, a bank balance can be summed up across time, but it cannot be summed up across accounts.

Examples

• Bank balance

• Stock price

• Inventory levels

• Number of employees

• Number of students enrolled in a course

3. Non-Additive Fact: Non-additive facts cannot be summed up across any fact table dimension. For example, ratios such as profit margin or averages like temperature cannot be summed up across any size.

Examples

• Profit margin

• Gross margin percentage

• Average temperature

• Average customer satisfaction rating

• Percentage of market share

It is essential to identify the type of fact as it determines how the fact table will be aggregated and also impacts the design of the data warehouse.