Fact table definition and its types in data warehousing with examples
What is a Fact Table?
A fact table stores quantitative information for analysis and is often denormalized. A fact table works with dimension tables, and it holds the data to be analyzed and a dimension table stores data about the ways in which the data can be analyzed.
A fact table or a fact entity is a table or entity in a star or snowflake schema that stores measures that measure the business, such as sales, cost of goods, or profit.
The fact table is a central table in the data schemas.
A fact table consists of two types of columns. The foreign keys column allows to join with dimension tables and the measure columns contain the data that is being analyzed.
We can notice that the orders table is the fact table which contain the foreign key as you see and the measurements columns like quantity and amount to calculate any information across all tables if needed.
Fact table and entity types
There are three types of fact tables and entities:
Transaction
A transaction fact table or transaction fact entity records one row per transaction.
A Transaction table is the most basic and fundamental view of business operations. These fact tables represent an event that occurred at an instantaneous point in time. A row exists in the fact table for a given customer or product only if a transaction has occurred.
so, let’s take an example:
if you have a shop or supermarket, you will use the transaction fact table to record each transaction happened over the day, if you have 100 customers purchased over the day, you will have 100 records at transaction fact table.
Periodic
A periodic fact table or periodic fact entity stores one row for a group of transactions that happen over a period of time.
so, let’s take an example:
if you have a shop or supermarket, ang you have only three categories, let’s say bike, car and bicycle, you will use the periodic fact table if you want to know like what the total sales of each category over month or week or quarter is.
Accumulating
An accumulating fact table or accumulating fact entity stores one row for the entire lifetime of an event. An example of an accumulating fact table or entity records is the timeline of the hiring process at any company , we all know that the hiring process have many stages and every stage have it’s time , let’s say we have three stages A , B and C , A takes 3 days , B takes 6 days and A takes 2 days , so you will use the accumulating fact table if you want to know the whole time of an process.
i hope you understand the concept of fact table and its types, if you have any problems of not understand any part pf the article, please tell me in LinkedIn.
