Data warehousing and tabling can become relatively complex, and those just getting started in data handling often puzzle over the differences of a fact table vs dimension table. While it may be second nature to some coders, many others have a hard time keeping the two table types separate and remembering the various attributes and uses.
We’re going to do a deep dive into fact tables, dimension tables, and the primary differences between them. We’ll explore how they are related and when you may use them, and the types of facts and dimensions that are commonly used. By the end, you’ll have discovered the critical differences between a fact table and a dimension table and will be ready to incorporate them into your data-handling project.
Fact Tables
A fact table is known as a primary table with a dimensional model, and it will contain various facts. Common uses for a fact table would be a list of sale tickets for a commerce website, a log of visitors to a website or business property, or other similar lists. Contrary to many people’s assumptions, a fact table will be a dynamic and living data list, constantly changing with each recorded event.
Dimension Table
A dimension table is a table that contains dimensions, attributes, or measurements of a fact and is joined to the fact table by a key. Dimension tables have data that has been denormalized, and the characteristics of the key-joined fact are descriptive characteristics of the fact, each having its columns. It is important to remember that dimension tables have no limits of the number of dimensions assigned to a fact. Dimensions are also able to contain one or more hierarchical references or relationships.
Critical Differences Between Fact Tables & Dimension Tables
- Fact tables will provide the facts, measurements, or metrics of a particular process in a business. In contrast, the dimension table is merely a companion to the fact table, providing additional attributes or details that can be used for data lookups and queries.
- In the traditional star or snowflake schema, the core of the schema will be the fact table. Each arm, branch, or point coming off the fact table would be a dimension table containing details or attributes about the fact at the center.
- Fact tables have a goal to be as granular and basic as possible while still retaining ease of use. On the other hand, a dimension table should be as complex and detailed as possible, including highly descriptive language, and wordy, complete, and accurate for future queries.
- For reporting purposes, a fact table would contain the labels and headers used in the report. In contrast, the dimension table would collect the data from populating the report and giving it context.
- Fact tables do not contain hierarchies. However, dimension tables often will have one or more hierarchies.
Common Types of Facts
Additive Facts – Additive facts dictate that measures will be added to all dimensions.
Semi-Additive Facts – In semi-additive facts, some dimensions will have measures added, while other dimensions will not.
Non-Additive Facts – Non-additive facts will store some basic measurement unit pertinent to a business operation, such as a sale, orders, or phone calls.
Common Types of Dimensions
Conformed Dimensions – Conformed dimensions are used in more than one schema and represent the fact to which it directly relates.
Dimension-to-Dimension Table Joinery – These dimensions may contain references to others. Dimensions like these can be modeled with outrigger dimensions.
Outrigger Dimensions – Outrigger dimensions are the dimensions of another dimension, sometimes considered a “secondary” dimension. These dimensions can be a recursive risk and should be used with caution.
Shrunken Rollup Dimensions – Shrunken rollup dimensions contain a subset of rows and columns belonging to a parent or base dimension. These are commonly used for creating an aggregation of fact tables.
Role-Playing Dimensions – These single dimensions help reference multiple times in a single fact table, linking to a logical role for the dimension.
Junk Dimensions – These are often random flags, codes, or other text or symbolic attributes that don’t belong to another category.
Degenerate Dimensions – These dimensions have no corresponding dimension and are used to collect fact tables’ snapshots.
Swappable Dimensions – Swappable dimensions can pair different versions of the same dimension with the same fact table.
Step Dimensions – Step dimensions are highly sequential operations, similar to events on a web page, and each step will often have its row in a fact table.
Discovering & Understanding the Key Differences Between Fact Tables and Dimension Tables
It may seem incredibly complex and confusing at first, but once you create a few simple fact tables and accompanying dimension tables, you’ll be able to keep them straight easily. Just remember your fact tables tell a fact, and your dimension tables give each fact dimension and detail so that it can be better understood.
Leave a Reply