02-3-Meaning of Facts and Additive
(quick read)
Additional Explanation
Facts are measurements that users will apply arithmetic calculations to
•What is the average of …
•What is the total of …
Facts are numeric values or measures in the fact table.
Students generally find it easy to understand that a fact or measure must be additive or semi-additive, but when it comes to designing the Data Warehouse, they forget to apply this condition to the measure. Doing so will help determine if it is just numeric data or a true measure. For example is Selling Price (referring to the selling price of a product) a measure?
1 One description that might help
ADDITIVE
A fact is additive if it is meaningful to add that fact over any dimension or any level
• Consider the Total Dollar Sales amount. Consider each of the combinations of Store, Date or time period, Product or category of product; it makes sense to sum that measure over any of these.
• Total Dollar Sales amount is called additive or fully additive
SEMI-ADDITIVE
Facts that measure a level (or intensity) may not be additive.
Consider quantity on hand (QOH) for a product in a warehouse at a point in time.
• We could meaningfully sum this over products if the products were very similar (cars, fridges)
• We could meaningfully sum the QOH of the same product over several warehouses
• But it doesn’t make sense to sum this over time. Quantity on hand is semi-additive
A fact is semi-additive if it is meaningful to sum it over some combination of dimensions or some level, but not all.
NON-ADDITIVE
A fact is non-additive if it cannot be meaningfully summed over any dimension
Consider a fact table with student averages
• Meaningful values are obtained by averaging these, not by summing up all the averages
• Averages would be non-additive
Most non-additive values will not appear in a DW
2 Alternate explanation
Most measures are additive, but semi-additive measures exist in the business environment, as well. Periodic measurements, like the end of day balance in bank accounts; level measurements like QOH are not fully additive. Semi-additive measures are additive across some dimensions within the cubes, but are not additive across one or more of the dimensions of the cube.
Example: The QOH of inventory can be additive for the dimension attributes in Stores, Warehouses, Provinces or Regions as long as they are in combination with individual products.
Example of Quantity ON Hand of individual products by stores
PRODUCT STORE1 STORE2 etc…
1000 35 43 etc…
2000 10 120
Etc
Example of Quantity On Hand of individual products by province
PRODUCT ONTARIO QUEBEC
1000 27,321 1,270
2000 4,235 2,130
Etc …
Notice in the above sample reports that there is value in the displayed result as long as individual products are also shown.
Example where QOH might not be meaningful
For example suppose management asked what is the total quantity on hand of stock in a store or a group of stores? The answer might be 15 million. That is not a valuable result. It could be made up of 14 million apples and 1 million bags of milk. Or it could be made up of over 40,000 different products. There is no analysis to be done as the value or result of summarizing the units on hand has no meaning.
An end of day account balance might be helpful if accumulated over the month and an average balance determined. It may have value to know the minimum or maximum balance in the time period. However the sum or total of all the daily balances in a month is meaningless.
Sample Fact Table
Only Total Units Sold is semi additive as long as it includes any combination with individual products. On occasion it might be additive by sub category as long as the sub category deals in very similar products such as 1 liter ice creams
02-3-Meaning of Facts and Additive by rt -- 20 May 2015 1 of 3