Table of Contents
Fact table & Types of fact tables
Facts
A fact table is the one which consists of the measurements, metrics or facts of business process.These measurable facts are used to know the business value and to forecast the future business.
- Fact tables are often defined by their grain. The grain of a fact table represents the most atomic level by which the facts may be defined.
- A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables.
- The primary key of a fact table is usually a composite key that is made up of all of its foreign keys.
- It is located at the center of a star schema or a snowflake schema surrounded by dimension tables.
- Where multiple fact tables are used, these are arranged as a fact constellation schema.
- Fact tables contain the content of the data warehouse and store different types of measures like additive, non additive, and semi additive measures.
- Fact tables provide the (usually) additive values that act as independent variables by which dimensional attributes are analyzed.
- The grain of a SALES fact table might be stated as “Sales volume by Day by Product by Store”.
- Each record in this fact table is therefore uniquely defined by a day, product and store. Other dimensions might be members of this fact table (such as location/region) but these add nothing to the uniqueness of the fact records.
- It is located at the center of a star schema or a snowflake schema surrounded by dimension tables.
- Where multiple fact tables are used, these are arranged as a fact constellation schema.
Measure Types
- Additive – Measures that can be added across any dimension. Additive facts are facts that can be summed up through all of the dimensions in the fact table. A sales fact is a good example for additive fact.
- Non Additive – Measures that cannot be added across any dimension. Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table. Eg: Facts which have percentages, ratios calculated.
- Semi Additive – Measures that can be added across some dimensions. Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others. Eg: Daily balances fact can be summed up through the customers dimension but not through the time dimension.
Fact Table
- A fact table might contain either detail level facts or facts that have been aggregated.
- Special care must be taken when handling ratios and percentage. One good design rule is to never store percentages or ratios in fact tables but only calculate these in the data access tool. Thus only store the numerator and denominator in the fact table, which then can be aggregated and the aggregated stored values can then be used for calculating the ratio or percentage in the data access tool.
- In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called “factless fact tables”, or “junction tables”.
Factless Fact Table
- A Factless fact table is fact table that does not contain fact.
- It contain only dimension keys and it captures events that happen only at information level but not included in the calculation level.
- A factless fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual information.
- Factless fact table are used for tracking a process or collecting stats. They are so called because, the fact table does not have aggregatable numeric values or information.
- The “Factless fact tables” can for example be used for modeling many-to-many relationships or capture events.
- Factless fact tables offer the most flexibility in data warehouse design.
For example, think about a record of student attendance in classes. In this case, the fact table would consist of 3 dimensions: the student dimension, the time dimension, and the class dimension. This factless fact table would look like the following:
Types of fact tables
There are four fundamental measurement events, which characterize all fact tables.
- Transactional
- Periodic snapshots
- Accumulating snapshots
- Temporal snapshots
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 high number of dimensions associated with.
Periodic snapshots
The periodic snapshot, as the name implies, takes a “picture of the moment”, where the moment could be any defined period of time.
Example:- a performance summary of a salesman over the previous month.
A periodic snapshot table is dependent on the transactional table, as it needs the detailed data held in the transactional fact table in order to deliver the chosen performance output.
Accumulating snapshots
This type of fact table is used to show the activity of a process that has a well-defined beginning and end.
Example:- the processing of an order. An order moves through specific steps until it is fully processed. As steps towards fulfilling the order are completed, the associated row in the fact table is updated.
An accumulating snapshot table often has multiple date columns, each representing a milestone in the process.
Therefore, it’s important to have an entry in the associated date dimension that represents an unknown date, as many of the milestone dates are unknown at the time of the creation of the row.
Temporal snapshots
By applying Temporal Database theory and modelling techniques the Temporal Snapshot Fact Table allows to have the equivalent of daily snapshots without really having daily snapshots.
It introduces the concept of Time Intervals into a fact table, allowing to save a lot of space, optimizing performances while allowing the end user to have the logical equivalent of the “picture of the moment” he is interested in.
Steps in Designing a Fact Table
- Identify a business process for analysis (like sales).
- Identify measures of facts (sales dollar).
- Identify dimensions for facts (product dimension, location dimension, time dimension, organization dimension).
- List the columns that describe each dimension (region name, branch name, business unit name).
- Determine the lowest level (granularity) of summary in a fact table (e.g. sales dollars).
