Types of Fact Tables

AIM Report Writing | Quick Tip


In the Kimball approach, when planning an enterprise data warehouse, the “data-marts” are organized by the data source system (Supply Chain, Marketing Campaign, Customer Loyalty), rather than the type of user (Human Resources, Accounting, Operations), as in the Inmon approach.

These “data-marts” are generally organized into a star schema using fact and dimension tables. The fact tables store numerical (measurable) data at the lowest grain required by the business purpose. The fact tables also contain multiple foreign keys that establish a relationship to dimension tables that store the attributes of the facts such as date and time, customer, employee, product, and geographic location.

When planning fact tables, there are 3 types of fact tables a developer will encounter. These types of fact tables include a transaction fact table, a periodic snapshot fact table, and an accumulating snapshot fact table.

A transaction fact table records a specific point in time and represents a complete action, or event such as the sale of a product. Measures of this type of fact may include quantity and extended price. The dimension attributes may include date of sale, product, store, customer, sales person, and promotion. A transaction fact table can be very large growing to terabytes and even petabytes.

A periodic snapshot fact table applies to a specific period of time such as inventory levels for a month. The measures such as quantity and total dollar value represent a snapshot of that period of time (month) and are taken at the same interval such as the end of every month. A periodic snapshot fact table can also be very large growing to terabytes and even petabytes.

An accumulating snapshot fact table represent a known process that is comprised of stages, or steps such as purchasing a new insurance policy. Measures may include period premium and discounts. Insurance policies, as an example, may go through steps that need to record the date those steps were completed such as policy order date, policy underwriting date, and policy effective date. An accumulating snapshot fact table is generally much smaller than the other types of fact tables because new dates are written into existing columns and not inserted as new rows.

Types of Fact Tables.png