What are the types of facts in a data warehouse?

Data warehousing combines data and information from multiple sources into a single, comprehensive database.

Facts in data warehousing represent numerical or measurable data that describe a business event or transaction. They are stored in a fact table, a central repository for facts, and facilitate efficient analysis.

Types of facts

Following are the three types of facts:

1. Additive facts

2. Semi-additive facts

3. Nonadditive facts

Additive facts

Additive facts support mathematical operations like addition, subtraction, multiplication, and division, allowing aggregation across all associated dimensions.

Example

Consider a sales fact table containing each transaction's total sales amount. This additive fact can be aggregated across dimensions such as time (e.g., monthly, quarterly, yearly), product category, or sales region.

This sales fact table includes transaction details such as the transaction date, product, category, sales amount, and quantity sold. It represents individual sales transactions that can be aggregated across dimensions for analysis and decision-making.

Semi-additive facts

Semi-additive facts exhibit different aggregation behavior across dimensions, allowing aggregation across some but not all. These facts capture time-dependent information but lack meaningful aggregation capabilities across certain dimensions. Semi-additive facts require special treatment during aggregation, as they do not exhibit uniform behavior across all dimensions.

Example

An inventory fact table may include a "current stock quantity" field. While this fact can be aggregated over time (e.g., summing stock quantities daily, weekly, or monthly), it cannot be aggregated across dimensions such as product or location, as the stock levels are specific to each individual item or store.

This inventory fact table captures the stock levels for individual products at specific locations, allowing aggregation over time but not across dimensions such as product or location.

Nonadditive facts

In a data warehouse, nonadditive facts are data elements that do not align with simple aggregation rules.Count, sum, min and max, average, first and last. Nonadditive facts require more complex calculations and considerations, whereas fundamental arithmetic operations like summing or averaging can easily aggregate additive facts. Aggregating nonadditive facts is more difficult because their values are affected by the context or combination of dimensions.

Example

It often involves ratios, percentages, averages of proportions, or values derived from calculations.

The customer satisfaction score is a nonadditive fact that represents the rating indicating the level of customer satisfaction for a particular product or category. Surveys or feedback systems typically provide the scores.

The nonadditive nature of customer satisfaction scores arises because they cannot be meaningfully summed or averaged across dimensions such as product or category. Aggregating satisfaction scores across different products or categories is not possible.

Conclusion

Understanding the different types of facts in data warehouses is essential for effective data analysis and decision-making. Data warehousing is a complex and evolving field, and the specific types of facts may vary depending on the industry and business requirements.