Types of FACT tables
Fact tables can be classified broadly into 3 types:
The accumulating fact table (also referred to as the accumulating snapshot fact table) is used to capture a series of events in a well defined process. For example: When a student enrolls into a University,there are a series of steps that have to take place for the student to be admitted. We can record these steps until the student is finally admitted to the university. There will be multiple date value columns to identify when different steps in the process were completed. We revisit the grain and keep updating it to show the different states.
On the same lines we can look at classifying measures as:
Fact tables can be classified broadly into 3 types:
- Transactional Fact Table
A transaction fact table gives you information "one row" at a time. This is a single specific event that happens in the transaction.This fact table has multiple dimensions associated with it. Example: Take an order receipt from your store. Every item that you purchase is recorded as a row in this transactional fact table.
- Periodic Snapshot Fact table
A periodic snapshot fact table as the name suggests are accessed at specific periods in time. These contain measures which might be semi additive.For example: Lets say that the store you purchase your groceries from needs an aggregated report of the daily sales that the shop generates.In such scenarios we utilize the Periodic Snapshot Fact table. This is done in scenarios where you need point in time data. Accessing bank statements on a quarterly basis can also be used as an example for periodic snapshot fact tables.
- Accumulating Fact Table
The accumulating fact table (also referred to as the accumulating snapshot fact table) is used to capture a series of events in a well defined process. For example: When a student enrolls into a University,there are a series of steps that have to take place for the student to be admitted. We can record these steps until the student is finally admitted to the university. There will be multiple date value columns to identify when different steps in the process were completed. We revisit the grain and keep updating it to show the different states.
On the same lines we can look at classifying measures as:
- Additive: Can be added across the different rows( example: Sum of Sales quantity)
- Non Additive: Measures that are stand alone and are only specific to a fact(example: Color of a product)
- Semi Additive: Measures that can be added across some dimensions(example: Adding sales quantity for a specific year to report yearly).
We'll look at dimensions in a later post..Cheers !
No comments:
Post a Comment