The Definitive Guide to Fact Tables

In order to have a basic understanding of a fact table, in a data warehouse system, it is the table that contains all the business information, which can be put under analysis and reporting activities if need be. 

Fact tables contain fields that represent the direct facts along with foreign fields that connect the fact table with additional dimension tables within the data warehouse system. 

Inside a data Warehouse system, there can be one fact table or many, depending on the model type that is utilized to design the data warehouse. 

Before there is any confusion between fact table vs dimension table, let us clarify fact tables with this definitive guide. 

Types of Fact Tables  

Fact tables can be categorized under three fundamental measurement events:

■ Transactional

■ Periodic Snapshot

■ Accumulating Snapshots

Transaction Fact Table: This is a basic view of business operations used to represent an occurrence of an event at any instantaneous moment in time. 

The facts that are measured are only valid for that particular instant and only for that particular event. The grain associated with the transaction table specifies one row per line in a transaction. 

Typically, it contains the data of the detailed level, which can cause it to have a huge number of dimensions associated with it, capturing the measurement at the most atomic level of dimension. 

This assists the table with providing a robust dimensional grouping, roll up & drill-down reporting capabilities to the users. In fact, it can be large enough to even contain billions of records.

Snapshot Fact Table:

The snapshot fact table offers a look at the state of things at a particular instance of time, usually including more semi-additive facts and non-additive facts. 

Snapshot fact tables make it easier to review the cumulative performance of its organization at regular and predictable intervals of time, like the performance of an activity at the end of each day or a week or month.

This makes it different from the transaction fact table, where a new row is added for the occurrence of each event. 

But for clarity, snapshot fact tables (or periodic snapshots) are dependent on the transaction fact table to get the detailed data to appear in the transaction fact table. 

Accumulating Fact Tables: These represent the activity of any process that has a well-defined starting point and endpoint. For the most part, accumulating snapshots have multiple data stamps that represent the predictable events that occur during the lifetime. 

Occasionally you will find that there is an extra column containing the date that reflects when the row was last updated. 

Fact Tables at Work in a Data Warehouse  

The way that fact tables work in a data warehouse is that they represent the metrics related to an event, with the meaning of any fact table having a table name to present it or the composition of the primary key of the fact table. 

Deciding the primary key is the biggest choice to make because the use of foreign keys gives insight into the grain or granularity of the table.

Four steps that can be taken to design it are:

1. Selecting a business process to model.

2. Declaring the grain.

3. Choosing the dimensions.

4. Identifying facts.

Characteristics of Fact Table  

These are the characteristics of the fact table:

Additive Measures: Attributes present in additive measures can be fully additive, non-additive, or even semi-additive. Fully additive or additive measures get added to all the dimensions. 

semi-additive measures are added to some of the dimensions and not to all the dimensions, and non-additive measures are stored as fundamental units of measurement for a business process.

Fact Table Grain: The grain of a fact table depicts the level of the detail or the depth of the data that is inside that table. The more the level of detail there is, the more efficient the table is.

Keys: It has a primary key which represents the accumulation of all the primary keys of all dimension tables that are linked with it, as is known as a concatenated key that helps to uniquely identify the row in the fact table. 

Shrunken Rollup Dimensions: Shrunken Rollup dimensions are conformed dimensions that are a subset of rows and or columns of a base dimension, which are also required when constructing aggregate fact tables. 

Aggregate fact tables are special fact tables in a data warehouse that contain new metrics derived from one or more aggregate functions from other specialized functions that output totals derived from a grouping of the base data.

Sparse Data: Sparse data refers to records that have attributes containing null values or measures and provide no information. 

In closing, the advantage of fact tables can range from the fact that they contain quantitative information for analysis and performance metrics that are usually normalized to fact tables containing various measures like additive, semi-additive and non-additive.

Similar Stories