Summary: in this tutorial, we will discuss fact tables, fact table types, and four steps of designing a fact table in the dimensional data model described by Kimball.
A fact table is used in the dimensional model in data warehouse design. A fact table is found at the center of a star schema or snowflake schema surrounded by dimension tables.
A fact table consists of facts of a particular business process e.g., sales revenue by month by product. Facts are also known as measurements or metrics. A fact table record captures a measurement or a metric.
An example of a fact table
In the schema below, we have a fact table FACT_SALES that has a grain that gives us the number of units sold by date, by store, and product.
All other tables such as DIM_DATE, DIM_STORE and DIM_PRODUCT are dimensions tables. This schema is known as the star schema.
Measure types
A fact table can store different types of measures such as additive, non-additive, semi-additive.
- Additive – As its name implied, additive measures are measures that can be added to all dimensions.
- Non-additive – different from additive measures, non-additive measures are measures that cannot be added to all dimensions.
- Semi-additive – semi-additive measures are the measure that can be added to only some dimensions and not across other.
Types of fact tables
All fact tables are categorized by the three most basic measurement events:
- Transactional – Transactional fact table is the most basic one that each grain associated with it indicated as “one row per line in a transaction”, e.g., every line item appears on an invoice. Transaction fact table stores data of the most detailed level, therefore, it has a high number of dimensions associated with it.
- Periodic snapshots – Periodic snapshots fact table stores the data that is a snapshot in a period of time. The source data of the periodic snapshots fact table is data from a transaction fact table where you choose a period to get the output.
- Accumulating snapshots – The accumulating snapshots fact table describes the activity of a business process that has a clear beginning and end. This type of fact table, therefore, has multiple date columns to represent milestones in the process. A good example of accumulating snapshots fact table is the processing of a material. As steps towards handling the material are finished, the corresponding record in the accumulating snapshots fact table gets updated.
Designing fact table steps
Here is an overview of four steps to designing a fact table described by Kimball:
- Choosing business process to a model – The first step is to decide what business process to model by gathering and understanding business needs and available data
- Declare the grain – by declaring a grain means describing exactly what a fact table record represents
- Choose the dimensions – once the grain of the fact table is stated clearly, it is time to determine dimensions for the fact table.
- Identify facts – identify carefully which facts will appear in the fact table.
In this tutorial, we’ve examined fact tables in detail, fact table types, and how to design fact tables described by Kimball.
