5/5/17

Fact Table Types in Data warehousing

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_noQuantityItem_noPriceTotal
100121034.69.2
100142762392
100137685081524
10011210613156
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_noItem_noQuantityAmountStatusUpdated_at
100113462500Order placed6/11/2014
100113462500Confirmed6/11/2014
100113462500Shipping completed6/13/2014
100113462500Delivered6/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_noTotal_creditAvailable_creditTotal_outstanding
*********12341000001000000
*********12341000005600044000
*********12341000003400066000
*********1234100000136098640
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.

4.  Factless fact table

1 comment:

  1. Thanks a bunch for sharing.

    ReplyDelete