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.

1. 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
2. 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.

1. 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.