EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

What is Fact Table?

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Data Warehouse Tutorial » What is Fact Table?

What is Fact Table

Overview of Fact Table

A Fact table in a Data Warehouse system is nothing but the table that contains all the facts or the business information, which can be subjected to analysis and reporting activities when required. These tables hold fields that represent the direct facts, as well as the foreign fields that are used to connect the fact table with other dimension tables in the Data Warehouse system. A Data Warehouse system can have one or more fact tables, depending on the model type used to design the Data Warehouse.

Characteristics of Fact Table

Below are the characteristics of the fact table:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • Keys: It has a key or a primary key which is the accumulation of all the primary keys of all dimension tables linked with it. That key is known as a concatenated key that helps to uniquely identify the row.
  • Fact Table Grain: The grain of a table depicts the level of the detail or the depth of the information that is contained in that table. More the level, the more the efficiency of the table.
  • Additive Measures: Attributes present in this can be fully additive, non-additive or semi-additive. Fully additive or additive measures are added to all the dimensions. Semi- additive are those measures that are added to some of the dimensions and not to all the dimensions and non-additive measures are stored fundamental units of measurement for a business process.
  • Sparse Data: There are records that have attributes containing null values or measures. They provide no information.
  • Shrunken Rollup Dimensions: Shrunken Rollup dimensions are the subdivisions of the base dimension.

Types of Fact Table

It is categorized under three fundamental measurement events:

What is Fact1.4

  • Transactional
  • Periodic Snapshot
  • Accumulating Snapshots

Let us understand this based on the measurement in brief.

1. Transaction Fact Table

This is a fundamental and basic view of business operations. It is used to represent an occurrence of an event at any instantaneous point in time. The facts measure are valid only for that particular instant and only for that event. The grain which is associated with the transaction table specifies as “one row per line in a transaction”. Usually, it contains the data of the detailed level, which leads it to have a large number of dimensions associated with it. It captures the measurement at the most basic or atomic level of dimension. This helps the table to give robust dimensional grouping, roll up & drill-down reporting capabilities to the users. It is dense and sparse. It can be large, maybe containing billions of records. Let us see an example of sales in a grocery shop.

what is Fact -1.1

2. Snapshot Fact Table

The snapshot gives the state of things at a particular instance of time or “picture of the moment”. It normally includes more non-additive and semi-additive facts. It helps to review the cumulative performance of the business at regular and predictable intervals of time. In this, the performance of an activity at the end of each day or a week or a month or any other time interval is represented, unlike the transaction fact table where a new row is added for the occurrence of every event. But snapshot fact tables or periodic snapshots are dependent on the transaction fact table to get the detailed data present in the transaction fact table. The periodic snapshot tables are mostly dense and can be large as transaction fact tables. Let us see an example of the periodic snapshot of the sales of the same grocery shop as in the transaction fact table.

what is Fact -1.2

3. Accumulating Fact Tables

These are used to represent the activity of any process that has a well defined and clear starting and end. Accumulating snapshots mostly have multiple data stamps that represent the predictable phases or events that occur during the lifetime. Sometimes there is an extra column containing the date that shows when the row was last updated. Let us see an example.

Accumulating -1.3

How does it work in a Data Warehouse?

It basically represents the metrics related to an event. The meaning of any fact table must be represented by the table name or the composition of the primary key of the table. The most important choice to make is to decide the primary key. A surrogate key can also be created to work as a primary key. If a user does not want to use a surrogate key then he may use the composition of foreign keys which points to the combination of dimensions related to the fact. The use of foreign keys gives an idea about the grain or granularity of the table.

Four steps that can be followed to design is described by Kimball:

  1. Selecting a business process to model.
  2. Declaring the grain.
  3. Choosing the dimensions.
  4. Identifying facts.

Advantages of Fact Table

  • It contains quantitative information for analysis.
  • It containing performance metrics are usually normalized.
  • It can contain different measures like additive, semi-additive and non-additive.

Conclusion

In this article, we discussed its types, working, and advantages. It is a central part of a star or snowflake schema. It is connected to dimension tables to give the structure of a star or a snowflake.

Recommended Article

This has been a guide to What is Fact Table? Here we discuss the characteristics and various types of fact tables along with their Advantages. You may also look at the following articles to learn more –

  1. What is OSI Model?
  2. What are the Features of DOS?
  3. What is Usability Testing?
  4. Top Advantages of Kimball Methodology

Statistical Analysis Training (10 Courses, 5+ Projects)

15 Online Courses

10 Hands-on Projects

140+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Data Warehouse Tutorial
  • Basic
    • What is Data Warehouse
    • Data Warehouse tools
    • Career in Data Warehousing
    • Benefits of Data Warehouse
    • Data Warehouse Architecture
    • Data Warehouse Design
    • Data Warehouse Implementation
    • Data Warehouse Features
    • Data Warehouse Modeling
    • Data Warehouse Software
    • Types of Data Warehouse
    • 10 Popular Data Warehouse Tools
    • Data Lake Architecture
    • Three Tier Data Warehouse Architecture
    • Data Warehouse Process
    • Database Parallelism
    • What is OLTP
    • What is OLAP
    • OLAP Tools
    • Types of OLAP
    • Operations in OLAP
    • MOLAP
    • HOLAP
    • Data Warehouse Schema
    • Data Warehouse Components
    • Snowflake Schema
    • Snowflake Architecture
    • What is Star Schema
    • Galaxy Schema
    • What is Fact Table
    • Kimball Methodology
    • Data Warehouse Testing
    • Operational Data Stores
  • ETL
    • What is Data Mart
    • What is Data Cube
    • What is a Data Lake
    • What is Data Integration
    • What is ETL
    • What is ETL Testing
    • ETL Testing Tools
    • ETL architecture
    • Dimension Table
    • Multidimensional Data Model
    • Fact Constellation Schema
    • ETL Process
  • Interview Questions
    • Data Warehouse Interview Questions
    • ETL Interview Questions
    • ETL Testing Interview Questions
    • Data Warehousing Interview Questions

Related Courses

Business Intelligence Course

All in One Data Science Course

Data Visualization Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Statistical Analysis Training (10 Courses, 5+ Projects) Learn More