According to Kimball methodology includes 3 main types of fact tables:
Transaction – the most common type of fact table, used to model a specific business process (typically) at the most granular/atomic level.
E.g Daily Transactions of Sales of an item in a store
E.g Daily Transactions of Sales of an item in a store
Periodic Snapshot – used to model the status of a business process at a specific point in time on a regularly recurring interval. For example, a periodic snapshot fact table might be used to track account balances on a monthly basis. In this case, a “snapshot” of the account balance would be taken at the end of each month – which represents the net of all withdrawal and deposit transactions occurring during the month. Inventory is another common scenario that makes use of periodic snapshots for tracking quantity on hand (by item) at the end of each month. In both examples, the primary “fact” (account balance and quantity on hand) in the two tables are “semi-additive” – which simply means they can’t be aggregated over time.
E.g Transactions over a period of month let's say a month
E.g Transactions over a period of month let's say a month
Accumulating Snapshot – model events in progress for business processes (e.g. Claims Processing for an Insurance Company) that involve a predefined series of steps (e.g. claim submitted, claim reviewed, claim approved/rejected). These tables prove useful in measuring/analyzing the duration between steps in a complete process and discovering bottlenecks.
e.g Transactions show the information of the business which has clearly defined from begin to the end
In addition to the primary fact table types (above), there are a few additional fact tables to consider:
Factless Fact Tables – used to model the events (e.g. student attendance) or conditions (e.g. product promotions). One common misconception is that these tables do not contain facts – which is only partially true. For example, if we are using a factless fact table to model “student attendance” (a textbook example of factless fact tables – pun intended!), our table may also include – in addition to the keys mapping to the student, class, and date dimensions – a single fact called “attendance_count” with the value of 1 for every row. This is not necessary, but it makes it clear what we are measuring and might even make life easier for downstream development of a cube and/or reports.
Note: a factless fact table used to model conditions is known as a coverage fact table in the Kimball methodology.
