As we know fact table is for storing a fact or measure, based on the type of data, the level of rollup/granular and the frequency of data loading there is four fact types table. Based on the business need the type of fact table will be selected.
1. Transactional
The fact table will contain data’s in very detail level without any rollup/aggregation the way how transactional database stores.
For example, retail shop invoice table, every line item will be loaded into fact table as a single row.
| Invoice_no | Quantity | Item_no | Price | Total |
| 1001 | 2 | 103 | 4.6 | 9.2 |
| 1001 | 4 | 276 | 23 | 92 |
| 1001 | 3 | 768 | 508 | 1524 |
| 1001 | 12 | 106 | 13 | 156 |
Here the quantity and total facts can be rolled up at different dimensions. It would be the most common type of fact where you perform analysis from multiple dimensions views.
2. Accumulating
Accumulating refers storing multiple entries for a single record to track the changes throughout the workflow.
For example, placing an order in online eCommerce application, the order will go through multiple workflow levels. Every workflow change will be loaded into fact table as a single record.
| Invoice_no | Item_no | Quantity | Amount | Status | Updated_at | |
| 1001 | 1346 | 2 | 500 | Order placed | 6/11/2014 | |
| 1001 | 1346 | 2 | 500 | Confirmed | 6/11/2014 | |
| 1001 | 1346 | 2 | 500 | Shipping completed | 6/13/2014 | |
| 1001 | 1346 | 2 | 500 | Delivered | 6/15/2014 |
It will give the track of the record when the order has been placed and when you have received the order.
3. Periodic snapshot
The data will be extracted and loaded for a particular period of a time. It describes what would be the state of the record in that specific period.
For example, consider a banking system which loads below three measures into a fact table for every third of the month for analysis.
The credit card total credit limit
Available credit limit
Total outstanding amount
| Creditcard_no | Total_credit | Available_credit | Total_outstanding |
| *********1234 | 100000 | 100000 | 0 |
| *********1234 | 100000 | 56000 | 44000 |
| *********1234 | 100000 | 34000 | 66000 |
| *********1234 | 100000 | 1360 | 98640 |
With this fact, we cannot do roll up or sum on all above three facts. Hence the periodic snapshot fact table will store only semi-additive or nonadditive fact.
