Menu
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.
848
A semi-additive measure is one that is to be summed for some dimensions, but should not be summed across some other dimensions. For the dimensions over which the measure is not additive, a different aggregation rule must be specified.
Additive facts are those facts which give the correct result by an addition operation. Examples of such facts could be number of items sold, sales amount etc. Non-additive facts can also be added, but the addition gives incorrect results. Some examples of non-additive facts are average, discount, ratios etc.
An additive measure uses SUM to aggregate over any attribute. The sales amount is a perfect example of an additive measure. Indeed, the sales amount for all customers is the sum of the individual sales for each customer; at the same time, the amount over a year is the sum of the amounts for each month.
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.
The most flexible and useful facts are fully additive; additive measures can be summed across any of the dimensions associated with the fact table. Semi-additive measures can be summed across some dimensions, but not all; balance amounts are common semi-additive facts because they are additive...
Semi-Additive measures: These are those specific class of fact measures which can be aggreagated across all dimension and their hieracchy except the time dimension 3. Non-additive measures: These are those specific class of fact measures which cannot be aggreagated across all/any dimension and their hieracchy
The sales amount is a perfect example of an additive measure. Indeed, the sales amount for all customers is the sum of the individual sales for each customer; at the same time, the amount over a year is the sum of the amounts for each month. A non-additive measure does not use SUM over any dimension.
Instead, the measure should report the last balance of the quarter. Therefore when aggregating balances, the measure must use a different behavior when aggregating over time or over other dimensions. This makes the measure semi-additive: sometimes it is additive, sometimes it is not.
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.
122
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
