CODEWITHSUNDEEP
data-warehousemeasurefact
user2205174
user2205174

Reputation: 51

Data warehouse: difference between semi-additive and non-additive measures

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.

Upvotes: 0

Views: 1398

Answers (2)

NickW
NickW

Reputation: 9798

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

Upvotes: 2

nsousa
nsousa

Reputation: 4544

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.

Upvotes: 1

Related Questions