Fact Table

Summary:  in this tutorial, we will discuss fact table, fact table types and four steps of designing a fact table in 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.

Example of fact table

In the schema below, we have a fact table FACT_SALES that has a grain which gives us a number of units sold by date, by store and by product.

All other tables such as DIM_DATE, DIM_STORE and DIM_PRODUCT are dimensions tables. This schema is known as the star schema.

Fact Table Example

 

Measure types

Fact table can store different types of measures such as additive, non-additive, semi-additive.

  • Additive – As its name implied, additive measures are measures which 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 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.
  • Periodic snapshots – Periodic snapshots fact table stores the data that is a snapshot in a period of time. The source data of 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 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 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 overview of four steps to designing a fact table described by Kimball:

  1. Choosing business process to model – The first step is to decide what business process to model by gathering and understanding business needs and available data
  2. Declare the grain – by declaring a grain means describing exactly what a fact table record represents
  3. Choose the dimensions – once grain of fact table is stated clearly, it is time to determine dimensions for the fact table.
  4. Identify facts – identify carefully which facts will appear in the fact table.

In this tutorial, we’ve examined fact table in detailed, fact table types and how to design fact table described by Kimball.

  • Was this tutorial helpful ?
  • YesNo