What is a Fact Table?

News

Fact Table discussion.

A fact table is the central table in a star schema of a data warehouse. A fact table stores quantitative information for analysis and is often deformalized.

In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is located at the center of a star schema or a snowflake schema surrounded by dimension tables. … The grain of a fact table represents the most atomic level by which the facts may be defined.

A fact table works by joining dimension tables. A fact table has a copy of the facts to be analyzed, and a dimension table stores data about the ways in which the data in the fact table can be analyzed. Therefore, the fact table consists of two types of columns.  One type is the foreign keys column which allows joins with dimension tables, and the measures columns contain the data that is being analyzed.

In my snicker candy bar example, the Mars, Inc, sells products to Wal-Mart. Every sale is a fact that happens, and the fact table is used to record these facts. For example:

 

Vendor ID

Product ID

Customer ID

Unit Sold

4

17

W2

1

4

17-A

W3

10

8

21

A3

2

8

4

A1

1

Now we can add a dimension table about customers:

CustomerID

Name

Region

Revenue

Product

Country

W2

Wal-Mart

Midwest

11

3

1

A3

Target

Midwest

3

5

1

C1

Lowes

Eastern

5

7

3

 

In this example, the customer ID column in the fact table is the foreign key that joins with the dimension table. By following the links, you can see that row 3 of the fact table records the fact that customer 2, Target, bought two items on day 8. The company would also have a product table and a time table to determine what Target bought and exactly when.

When building fact tables, there are physical and data limits. The ultimate size of the object as well as access paths should be considered. Adding indexes can help with both. However, from a logical design perspective, there should be no restrictions. Tables should be built based on current and future requirements, ensuring that there is as much flexibility as possible built into the design to allow for future enhancements without having to rebuild the data.

Difference Between Fact Table and Dimension Table

Fact Table:

A fact table is a primary table in a dimensional model. A Fact Table contains

  • Measurements/facts
  • Foreign key to dimension table

Dimension table:

  • A dimension table contains dimensions of a fact.
  • They are joined to fact table via a foreign key.
  • Dimension tables are de-normalized tables.
  • The Dimension Attributes are the various columns in a dimension table
  • Dimensions offers descriptive characteristics of the facts with the help of their attributes
  • No set limit set for given for number of dimensions
  • The dimension can also contain one or more hierarchical relationships

 

 

 

 

 

Dr. Rigoberto Garcia
Dr. Rigoberto Garcia has been serving in the Information Technology industry for more than a three decades and a half decades. As the Founders of Software Solutions Corporation™ in February 1995 and SSAI Institute of Technology, September 2019, his vision has always been to serve the community while creating meaningful contributions to society and the industry, to better the human condition. Managing customer solutions implementations, is only a tiny part of his daily accomplishments. He's a writer with more than 52 titles ranging from project management to poetry. With his subject matter expertise, has made him a valuable in the public field for project at NASA, United States Airforce, Boeing and SpaceX. He has a proven track of delivery in the private sector, serving Blue Cross & Blue Shield, General Casualty, General Motors, Archer Daniel Midland, University of Upper Iowa, Texas A & M and many other institutions around the globe. He is an expert researcher, certified instructor and leader. Currently he acts as the CEO of Software Solutions Corporation and its Chief Cloud and Security Architect.
https://softwaresolutioncorp.com

Leave a Reply

Your email address will not be published. Required fields are marked *