0

I have some difficulty to understand the difference between semi-additive and non-additive measures in a fact table. I've seen this example:

What's the difference between additive, semi-additive, and non-additive measures

But I don't understand it. I tried to read some of the Kimball's books but I don't understand it. In theory, you cannot sum over some of the dimensions but the examples that I see on the Internet seems to be similar than non-additive measures, what's the difference between them.

I need a good explanation with examples because I have an exam soon and I need to understand this :).

Thank you.

user2205174
  • 41
  • 8
  • The question you linked to has an answer that is a good explanation with examples. What is it about that answer that you don’t understand? – NickW Jun 09 '22 at 23:32
  • I don't see this definition: "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)". It's seem to me that the example is the same as the non-additive measure. – user2205174 Jun 09 '22 at 23:43
  • You can't sum over some of the dimensions(across the time dimension), but you can sum over other dimensions. What other dimensions you can sum?. – user2205174 Jun 09 '22 at 23:49

2 Answers2

1

Semi-additive measures can only be aggregated across some dimensions. E.g. quantity in stock. You can add it across products and warehouses to get the full stock. But you cannot aggregate across the time dimension.

Non—additive measures cannot be aggregated across any dimension. For example, sales tax percentage or unit price. It makes no sense adding the sales tax percentages of different facts. Or adding the unit prices.

nsousa
  • 4,448
  • 1
  • 10
  • 15
  • It's difficult for me understand this: "E.g. quantity in stock. You can add it across products and warehouses to get the full stock. But you cannot aggregate across the time dimension." It's the same stock than "quantity in stock"?. Because you can sum across products but you cannot sum over time dimension. – user2205174 Jun 09 '22 at 23:58
  • I don't understand this. You mean that semi-additive measures can be summed over producst(number of articles of same product), but you cannot sum over time. I disagree with this, you CAN sum articles of same product over time. For instance, in two days I have had 20 articles of same product(cereal), one day I had 15 articles and another day I had 5 articles. In total, I have had 15+5=20 articles. – user2205174 Jun 10 '22 at 09:04
  • 1
    If the measure in the stock fact is "todays stock" level (not transactions) then you can't add it. Yesterday I had 5 oranges. Today I have 7. This does not mean I have 12 oranges it means have 7 – Nick.Mc Jun 10 '22 at 10:09
  • Thank you, I believed that were transactions, but it is "todays stock" level. – user2205174 Jun 10 '22 at 10:57
1

Just to be clear, when describing a measure as being semi- or non-additive we are talking about whether the operation makes logical/business sense. We are not saying that you cannot perform any/certain mathematical operations on these measures, just that if you do the result you would get would have no business meaning.

Semi-additive Measures

Say you have a fact table like this, showing monthly bank balances for customers :

# Customer_id Month Balance
1 AAA 2022-01 100.00
2 AAA 2022-02 200.00
3 AAA 2022-03 90.00
4 AAA 2022-04 750.00
5 AAA 2022-05 400.00
6 BBB 2022-01 250.00
7 BBB 2022-02 68.00
8 BBB 2022-03 170.00
9 BBB 2022-04 98.00
10 BBB 2022-05 230.00

The balance is additive across customers e.g. the total customer balance for 2022-01 was 350.

The balance is not additive across months e.g. saying the balance for customer A between 2022-01 and 2022-02 was 100+200=300 is meaningless. At no point was Customer A's month-end balance 300.

So because this balance measure is additive for some dimensions (Customer) but not for others (Month) it is described as semi-additive.

Non-Additive

Say we have a fact table that shows the ratio of the month-end balance to the balance at the end of 2021 (say for Customer A the 2021 balance was 1000 and for Customer B is was 500)

# Customer_id Month Balance Ratio
1 AAA 2022-01 100.00 0.1
2 AAA 2022-02 200.00 0.2
3 AAA 2022-03 90.00 0.09
4 AAA 2022-04 750.00 0.75
5 AAA 2022-05 400.00 0.4
6 BBB 2022-01 250.00 0.5
7 BBB 2022-02 68.00 0.17
8 BBB 2022-03 170.00 0.34
9 BBB 2022-04 98.00 0.196
10 BBB 2022-05 230.00 0.46

There are no aggregations you could make to the ratio that make any sense e.g. summing or averaging the ratio by customer or month would produce a meaningless figure. Therefore the ratio is a non-additive measure

NickW
  • 8,430
  • 2
  • 6
  • 19
  • Thank you for this explanation. It solved my question. Only one thing, when you tell me that: "At no point was Customer A's month-end balance 300", you mean that you cannot see 300 in the balance of customer A. It doesn't appear 300 in the table. – user2205174 Jun 10 '22 at 10:51
  • I mean the customer did not have a month end balance of 300 at any time between 2022-01 and 2022-02 – NickW Jun 10 '22 at 11:06