Types of Facts in a Multidimensional Data Model
Multi-dimensional data modeling is a data modeling technique used in data warehouses to organize data in the database in an efficient manner to analyze future trends and patterns.
Types of Facts in Multi-dimensional Data Modeling
There are three types of facts in Multi-dimensional data modeling, they are:
Additive facts: These facts can be summoned up on any dimension in a database. Example use cases are total profit, revenue, income, or quantity.
- Usecase-1:
- If a person gets a profit of 100 units by selling product A and a profit of 500 units by selling product B
- The total profit of the person = profit gained by selling product A + profit gained by selling product B
- 100 units + 500 units
- 600 units
- Usecase-2:
- If a person buys 250 units of product A and buys 300 units of product B
- The total amount of quantity the person = quantity of product A + quantity of product B
- 250 units + 300 units
- 550 units
Semi-Additive facts: These facts can be summoned up on some dimensions and can not be summoned up on other dimensions in a database. Example use cases are inventory levels and bank account balances.
- Usecase-1:
- If a person has a balance of 500 units in account A, deposits 1000 units of money in account A, and deposits 400 units of money in account A
- The total balance in account A = Initial balance + deposit of A + deposit of B
- 500 units(initial balance) + 400 units + 1000 units
- 1900 units
- Usecase-2:
- If a person has a balance of 500 units in account A, deposit 1000 units of money in account A and deposit 400 units of money in account B
- The total balance in account A = Initial balance + deposit of A
- 500 units(initial balance) + 1000 units
- 1500 units
- but the result by summing up is 500 units(initial balance) + 1000 units + 400 units = 1900 units
- The above use cases come under the category of Semi-Additive facts as in some scenarios summing up them, doesn’t give accurate results.
Non-Additive facts: These are the facts that any dimension in a database cannot summon. Example use cases are profit margin or average temperatures.
- Usecase-1:
- If a company has a profit margin on day-1 is 20% and day-2 is 80%. the current profit margin is 80%
- but, the profit margin by summing up the day-1 and day-2 will be 20% + 80% = 100%
- The above use case comes under the category of non-Additive facts as they don’t give accurate results in any dimension.
Contact Us