Fact Table And Dimension Table | Types, Examples, Key Differences

In a system, a fact table consists of facts of the system as it's content data, whereas a dimension table comprises of the content of the fact table, which in turn helps build a connection between the respective fact table and the dimension table. Both the tables consist of data in textual format. However, the data in the fact table can comprise numbers along with text as well. In the process of gathering data, a dimension table is created before the fact table.

What is a Fact Table?

A fact table type has values of attributes in the dimension table. It is a primary table in dimensional model. It has data in numeric form, the data that needs to be analyzed. There are two key columns in a fact table. The first column consists of foreign keys that will help connect the data with the dimension-type table, while the second column consists of descriptive attributes and the data to be analyzed. It consists of data in de-normalized form, the lowest possible form. It usually consists of metrics of business process. The fact table is also known as the reality table.

Types of Fact Table

Transaction Fact Table: It is a basic approach to the business process. It is usually used to represent an event which occurs at primary level.
Snapshot Fact Table: It contains many non-additive and semi-additive facts and is mostly used to describe and analyze the state of things at a particular time.
Accumulated Fact Table: The activities of the process with a beginning and end are represented by the accumulated fact table.

Characteristics of Fact Table

Keys: Fact table consists of concatenation of all primary keys of the dimension table. This key is unique and identifies a row in the fact table.
Data grain: Fact table consists of data in textual format as well as numbers. Data grain indicates the depth of information stored in the fact table. Data grain must be performed at the highest level. The more the data grain the better the fact-type table.
Sparse data: There is some data in a fact-type table which has a null measure or it measures to zero. This data has no unit of measurement because it does not indicate any quantity. As such, there is no need to store such data as it does not convey any information.
Additive measures: Attribute format of fact table can be fully additive or semi-additive. Fully additive measures are the ones added to all dimensions. Semi-additive measures are the ones which are added to some dimensions and not all dimensions. Semi-additive measures store basic unit of measurement of business process.
Degenerated dimension: These measure types of dimensions of the fact table are the ones which are non-additive measures or which cannot be added. They are known as degenerated dimensions.

What is Dimension Table?

A dimension-type table consists of the dimensions along which the measurement is taken, in fact-type table. It is a combination of dimensions and the value of attributes. It is a major part of star schema. Dimension-type table is interconnected to fact-type table by the use of keys. There is no limit set for a given number of dimensions. The various columns of the dimension-type table are basically attributes of dimension table. This table type is known as de-normalized tables. This table type grows horizontally and has hierarchical relationships. The size of the dimension table is small with thousands of rows, but the size can, however, grow later on.

Types of Dimension Table

Slowly Changing Dimention Table: The dimension attributes here change slowly with time rather than changing at regular interval of time.
Conformed Dimension Table: This type of dimension table is shared across various different data businesses by the same company. The company shares the same dimension table for other purposes without making changes.
Junk Dimension Table: In order to avoid various foreign keys, in fact table, this table is created with different and unrelated attributes. It helps in avoiding confusion.
Degenerate Dimension Table: In degenerate dimension , the dimension attribute is not a separate table and is sorted as a part of the fact table.
Roleplay Dimension Table: This type of table has multiple and varied relationships with fact table. It has multiple purposes to be included with the same database.

Characteristics of Dimension Table

Attributes and keys: Every dimensional table must have a unique key to identify the record of the dimension table. Attributes of Dimension table are ample in number and thus, when dimension table is created, then it is spread horizontally, the table size is broad. These attributes are used by truth table to calculate the metric.
Attribute values: The majority of the values in dimension tables are in textual format. In very rare cases, the format is numeric.
Relation between attributes: Attributes present in the dimension table are not usually related to each other but are still present in the dimension table.
Normalization: Normalization of the dimension table is not required. Many intermediate tables would be created upon normalizing a dimension table. During query resolution, the person will have to go through the additional table if dimension tables are normalized.
Drooling down , rolling up: The required details can be obtained from the table by moving from an upward direction to a downward or by traversing from a downward to upward direction.
Records: Dimension table has more attributes and a lower number of records.
Multiple hierarchy: The types of measure of dimension table must be multi-hierarchical. It will help the user to drill down the level of aggregation easily.

Key Differences Between Fact Table and Dimension Table

Let's have a look at the head to head comparison table between fact table and dimension table.

Parameters	Fact Table	Dimension Table
Definition	It consists of measurements, metrics and data of the business process.	It is a companion table to fact table, contains attributes which are used to deduce the facts of fact table.
Design	It is mostly defined by data grain.	It is quite descriptive, contains words at length
Characteristics	It is usually located at the center of snowflake schema and is surrounded by dimension table.	It is usually located at the edges of the snowflake schema.
Task	Fact table analysis is measurable data analysis (from dimension table) used for analysis and reporting.	Dimension table data is collected for analysis.
Key	It has primary key for each dimension which acts as foreign keys to dimension.	It has primary key which is unique to identify each dimension.
Type of Data	It has data in textual format as well as numeric format.	It has data in textual format only.
Hierarchy	It does not contain any hierarchy.	It contains hierarchy.
Table Growth	This table grows vertically(vertical table).	This table spreads wide, grows horizontally(horizontal table).

Example of Fact Table and Dimension Table

Summing Up

It can be concluded that a fact table has fewer attributes and consists of measurements of attributes of business dimensions. It has primary keys which are useful in identifying and building connections between respective fact and dimension tables. A fact table grows vertically and does not have any hierarchy. Located at the center of the schema, it is defined by data grain. However, a dimension table has more attributes and is a companion to the fact table. The data in the dimension table is only in textual format. It grows horizontally and includes hierarchy.

Shreeya Thakur

Sr. Associate Content Writer at Unstop

I am a biotechnologist-turned-content writer and try to add an element of science in my writings wherever possible. Apart from writing, I like to cook, read and travel.