In a decision-support schema, the fact table contains elements called facts, which correspond to the measures that users will use to create indicators in their dashboards. These facts can then be aggregated according to the different data dimensions to be analyzed.
When designing your dimensional schema, it is essential to start by defining the type of fact table, which goes hand in hand with defining its granularity.
One method used to determine the type of fact table is to identify the type of event that it describes, as presented below.
Transaction fact table
Transaction fact (TF) tables are based on discreet events. Each of their rows corresponds to a “point in time” or to a fleeting event. This often corresponds to the atomic level of detail in source operational systems.
This temporal information must be available for the periodic refreshing of the data warehouse and there is no sense in modifying it.
Possible examples include:
- a customer who purchases a product in a store
- a web page visitor
- an employee who answers a call to the technical support department
Reading Transaction fact table: Star Schema. Christopher Adamson. pages 260-265
Reading Transaction fact table: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. pages 228-229
“Accumulating snapshot” fact table
“Accumulating snapshot” (AS) fact tables are based on changing events. Each of their rows corresponds to an event that changes over time. These changing events may last several days/weeks and change state several times.
Temporal information is partially available when the data warehouse is periodically refreshed according to the state of progress of the event.
Possible examples include:
- an online order as it progresses through different phases: ordered, picked, shipped, delivered
- a student applying for admission to a university: carried out, undergoing processing, accepted/rejected
Reading Accumulating snapshot fact table: Star Schema. Christopher Adamson. pages 274-287
Reading Accumulating snapshot fact table: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. pages 231-232
“Periodic snapshot” fact table
“Periodic snapshot” (PS) fact tables are based on recurring events. Each of their rows correspond to an event that is repeated at regular intervals, and summarizes what has happened over a period of time.
Therefore, periodic snapshot fact tables are not updated during each refresh of the data warehouse.
Examples may include:
- stocktaking which is carried out every evening
- the monthly consolidation of a bank balance
- the quantity of rain that has fallen during one hour
Recurring events are often used to aggregate discrete events (the evening stocktake takes account of all of the day’s sales), or events whose measurement is naturally observed periodically (the hourly rainfall totals recorded by the rainfall sensor at a weather station).
Reading Periodic snapshot fact table: Star Schema. Christopher Adamson. pages 265-274
Reading Periodic snapshot fact table: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. pages 229-231