Difference between Fact Tables and Dimension Tables

The Fact Table and Dimension Table are critical components in the creation of a schema. The fact table's record comprises attributes from various dimension tables. The fact table allows the user to assess the business aspects, which aids him in making decisions to improve his business. In contrast, the dimension tables assist fact tables in collecting dimensions along which measurements should be taken. The main distinction between these tables is that the dimension table has attributes and measurements taken in the fact table.

In this article, you will learn about the difference between Fact Table and Dimension Table. But before discussing the differences, you must know about Fact Tables and Dimension Tables.

What is a Fact Table?

The fact table is a table that has the values of the dimension table's attributes. It contains quantitative data that has been denormalized. It essentially holds the data that must be evaluated. Fact tables typically have two columns: one for foreign keys that allow them to be joined with dimension tables and another for the value or data that has to be evaluated. It is largely made up of numbers. It expands vertically, with more records and fewer properties.

Characteristics of Fact Table

There are various characteristics of the Fact Table. Some main characteristics of the Fact Table are as follows:

1. Concatenated Key

The fact table includes Concatenated key, which is the concatenation of the main keys of all dimension tables. The fact table's concatenated key must uniquely identify each row.

2. Additive Measures

Fact table attributes might be fully additive, semi-additive, or non-additive. Fully additive measures are those that are included in all dimensions. Semi-additive measures are utilized to add measurements to some dimensions. On the other hand, non-additive measures are utilized to store the basic unit of measurement of any organization process.

3. Degenerated Dimensions

Degenerated dimensions are those dimensions or attributes that cannot be added or are not additive.

4. Fact Table Grain

The level or depth of the data that is stored in the fact table is known as the grain of the table. An efficient fact table should be created at the highest level.

5. Sparse Data

Some data records in the fact table include attributes with null values or measurements, indicating that they do not provide any data.

6. Shrunken Rollup dimensions

These are those dimensions that are the subdivision of rows and columns of the base dimension.

7. Outrigger dimensions

Outrigger dimensions are those dimensions that contain a relation to another dimension table.

What is Dimension Table?

The dimension Table is an important part of the Start Schema. A dimension table includes the dimensions along which the attributes' values are taken in the fact table. Dimension tables are small and have many thousand rows, but their size can be expanded occasionally. These tables are connected to a fact table via foreign keys, and these dimension tables are denormalized. The dimension table is hierarchical in nature and expands horizontally.

Characteristics of Dimension Table

There are various characteristics of the Dimension Table. Some main characteristics of the Dimension Table are as follows:

1. Attributes and Keys

Each Dimension table must include a primary key that uniquely finds every table record. It is typically observed that the dimension table has many attributes. As a result, it looks to be wide, and when you create a dimension table, you will find that it spreads horizontally.

2. Attribute Values

The values of attributes in dimension tables are rarely numeric; instead, the values of attributes are usually in textual format.

3. Normalization

Dimension table is not normalized as normalization splits the data and produces new tables, which reduces query execution efficiency since it must travel through these other tables when it needs to retrieve measurements from the fact table for any equivalent attribute in the dimension table.

4. Relation Among Attributes

Attributes in the dimension table are often unrelated to one another, although they are all part of the same dimension table.

5. Drilling Down, Rolling Up

Dimension table attributes enable you to obtain details by going from higher-level aggregated attributes to lower-level attributes. For instance, if you wish to find the overall sale in an area, you may drill down to see sales by city, state, and zip code. You may even roll up to find total sales by zip code, city, and state.

6. Records

The dimension table contains fewer records and more attributes.

Key Differences between Fact Table and Dimension Table

There are various key differences between Fact Table and Dimension Table. Some main key differences between Fact Table and Dimension Table are as follows:

1. The fact table includes facts, metrics, and measurements about an organization's process. On the other hand, the dimension table is a companion to the fact table and provides descriptive attributes that may be used as query constraints.
2. The fact tables have fewer attributes and more records than dimension tables. On the other hand, the dimension tables have fewer records and more attributes.
3. Each dimension table has a primary key that is used to find every record in the table. In contrast, the fact table has a concatenated key, which is a mixture of all primary keys of the entire dimension table.
4. A fact table is described by its grain or most atomic level. On the other hand, the dimension table should be wordy, complete, detailed, and of high quality.
5. Fact table attributes contain both numerical and textual attributes. In contrast, dimension table attributes only contain textual attributes.
6. There is no hierarchy in the fact table. On the other hand, the Dimension table has hierarchies.
7. The fact table is located in the middle of the snowflake or star schema. In contrast, the dimension table is situated on the edges of the snowflake or star schema.

Head-to-head comparison between Fact Table and Dimension Table

Here, you will learn the head-to-head comparisons between Fact Table and Dimension Table. The main differences between Fact Table and Dimension Table are as follows:

Conclusion

Fact tables and dimension tables are not the same things. Dimension tables feature descriptive attributes that help support data-driven decision-making, whereas fact tables offer information and various metrics relevant to your organization. Both are equally useful in the creation of a schema, but the dimension table must come first. A fact table cannot be created without dimensions.