Calculating Fact Table Grain and Additivity

In this lesson, you will learn how to define the "grain" of your fact table and categorize your metrics to ensure your reports perform correct mathematical aggregations.

Defining the Grain

The grain is the absolute lowest level of detail represented by a single row in your fact table. Think of it as answering the question: "What does one row represent?"

If you are building a retail sales model, your grain might be one line item on a receipt. If you define the grain too broadly (e.g., total daily sales per store), you lose the ability to drill down into specific products. Always design for the "atomic" level—the smallest, most granular event—to ensure your model remains flexible for future business questions.

Understanding Additivity

Once you have your grain, you must determine how to summarize your metrics. We categorize metrics into three types based on how they behave when aggregated across dimensions (like Time or Geography):

1. Fully Additive: These metrics can be summed up across any dimension.
   • Example: Sales_Amount. You can add this by day, by store, or by customer.
2. Semi-Additive: These can be summed across some dimensions, but not all. The most common "restricted" dimension is Time.
   • Example: Inventory_Quantity. You can sum inventory across all products in a store, but you cannot sum inventory across time (adding Monday's inventory to Tuesday's inventory doesn't give you a meaningful total).
3. Non-Additive: These metrics cannot be summed at all. You must use different calculations like averages or ratios.
   • Example: Profit_Margin_Percentage or Unit_Price. If you add the prices of two items together, the result is meaningless.

Logic in Practice

When building your views or BI layer, your aggregation logic depends on these rules:

-- Fully Additive: SUM is safe
SELECT Store_ID, SUM(Sales_Amount) FROM Fact_Sales GROUP BY Store_ID;

-- Semi-Additive: Use Last_Value or similar for Time
-- (Summing inventory over time creates double counting!)
SELECT Product_ID, MAX(Inventory_Quantity) FROM Fact_Inventory GROUP BY Product_ID;

By identifying whether your metrics are fully, semi, or non-additive, you prevent the common analytical error of reporting "impossible" numbers to your stakeholders.

Key Takeaway: Define your grain at the atomic level to maintain detail, and always verify if a metric is additive, semi-additive, or non-additive before applying aggregation logic.