Fact Table vs Dimension Table

Introduction to Fact Table vs Dimension Table

Fact Table vs Dimension Table are the main components of any work related to business intelligence. These tables are used for detailed analyses and help to conclude the business value and in decision making. These tables are important for creating a schema. These tables are used for different goals and contain different sets of data. Fact table contains attributes of various dimension tables whereas dimension table contains dimensions consulting which operations are done in the fact table. Let us discuss each in detail with their differences.

Fact table

It is a table that has values of the attributes of the dimension table. It contains quantitative information in a denormalized form. It basically contains the data that needs to be analyzed. Fact tables mostly have two columns, one for foreign keys that helps to join them with a dimension table and others that contains the value or data that need to be analyzed. It mostly contains numeric data. It grows vertically and it contains more records and fewer attributes.

Characteristics of Fact Table

Keys: Fact table consists of a key that is the combination or concatenation of all primary keys of various dimension tables associated with that fact table. Such key is called a concatenated key which uniquely identifies the row of the fact table.
Fact Table Grain: Grain of the table means the level of the detail or the deepness of the information that is stored in the fact table. The level must be the highest for designing an efficient fact table.
Additive Measures: Attributes in the fact table can be fully additive, semi-additive or non- additive. Fully additive or additive measures are those that are added to all dimensions. In semi-additive, measures are added to some dimensions and not to all and non-additive measures are those which stores basic unit of measurement of any business process.
Sparse Data: Some records present in the fact table contain attributes with null values or measures i.e. these records do not give or provide any information.
Degenerated Dimensions: The dimensions or attributes present in the fact table which cannot be added or which are not additive are called a degenerated dimension.
Outrigger Dimensions: The dimensions that have reference to any other dimension table are called as outrigger dimensions.
Shrunken Rollup Dimensions: The dimensions which are the subdivision of columns and rows of the base dimension are called Shrunken Rollup dimensions.

Dimension Table

A dimension table contains the dimensions along which the values of the attributes are taken in the fact table. Dimension tables are small in size, contains only several thousand rows but the size can be increased occasionally. These tables are associated with a fact table through foreign keys. These tables are de-normalized. The dimension table contains hierarchical relationships and grows horizontally.

Characteristics of Dimension Table

Keys: Every dimension table needs to have a primary key that helps to uniquely identify each record of the dimension table.
Attributes: Dimension table contains many attributes and therefore the dimension table appears to grow horizontally.
Attribute Values: The values in the dimension table are mostly in textual format and not in numeric format.
Relation Between Attributes: Attributes present in the dimension table are generally not directly related to each other but still are a part of the same dimension table.
Normalization: Dimension table is not normalized because normalization splits the data and creates additional tables which decrease the efficiency of the query execution as it must pass through these additional tables when it wants to recover measurements from the fact table for any corresponding attribute in the dimension table.
Drilling Down, Rolling Up: Attributes present in dimension table permits to derive details through traversing from the higher level to lower level or it also allows rolling up from lower level to the higher level of the attributes.
Records: Dimension table has less number of records and more number of attributes.

Head to Head Comparison Between Fact Table vs Dimension Table (Infographics)

Below are the Top 12 differences between Fact Table vs Dimension Table.

Key Differences Between Fact Table vs Dimension Table

Let us discuss some of the major differences between Fact Table vs Dimension Table.

Fact Table contains the values or measurements of the attributes of the dimension table.
Fact table comprises of fewer attributes and more records whereas the dimension table comprises of lesser records and more attributes.
The fact table grows vertically while the dimension table grows horizontally.
Fact Table contains a concatenated key while the dimension table contains a primary key.
Dimension tables must be created before the fact table is created.
Any schema contains more dimension tables and less fact tables.