What's the difference between additive, semi-additive, and non-additive measures
I have searched in the net what's the difference between additive, semi-additive, and non-additive measures in a data warehouse. I have found some results but I have difficulty understanding the differences because they aren't an example. Could you please explain to me more the difference between additive, semi-additive, and non-additive measures with examples.
Answer
The numeric measures in a fact table fall into three categories. The most flexible and useful facts are fully additive; additive measures can be summed across any of the dimensions associated with the fact table.
An example of a fully additive measure is sales (purchases from a store). You can add hourly sales to get the sales for a day, week, month, quarter, or year. You can add sales across stores or regions.
Semi-additive measures can be summed across some dimensions, but not all; checking account or savings account balance amounts are common semi-additive facts.
You can recreate a balance amount from the transactions file, but it doesn't make any sense to add the balance amounts from October, November, and December (across the time dimension).
Finally, some measures are completely non-additive, such as ratios. A good approach for non-additive facts is, where possible, to store the fully additive components of the non-additive measure and sum these components into the final answer set.
Finally, you calculate the final non-additive fact.
Emphasizing that, usually, non-additives are averages and percentages and ratios and it is irrelevant to perform additional aggregations on them (for example summing 2 selling ratio of a product will not be meaningful at all), Thus non-additive facts should not be stored within the fact table and will usually be manipulation on the additive facts within the fact table.
Source: [1]