Difference Between Fact Table and Dimension Table

Reading Time: 9 minutes

Fact Table:

In a dimensional model, a fact table is a primary table that holds the measures, metrics and other quantifiable information..

A Fact Table contains

  1. Measurements/facts
  2. 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.
  • Dimensions store the textual descriptions of the business. With help of dimension you can easily identify the measures.
  • 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

 

Difference between Dimension table vs. Fact table

Parameters Fact Table Dimension Table
Definition Measurements, metrics or facts about a business process. Companion table to the fact table contains descriptive attributes to be used as query constraining.
Characteristic Located at the center of a star or snowflake schema and surrounded by dimensions. Connected to the fact table and located at the edges of the star or snowflake schema
Design Defined by their grain or its most atomic level. Should be wordy, descriptive, complete, and quality assured.
Task Fact table is a measurable event for which dimension table data is collected and is used for analysis and reporting. Collection of reference information about a business.
Type of Data Facts tables could contain information like sales against a set of dimensions like Product and Date. Evert dimension table contains attributes which describe the details of the dimension. E.g., Product dimensions can contain Product ID, Product Category, etc.
Key Primary Key in fact is mapped as foreign keys to Dimensions. Foreign key to the facts table
Storage Helps to store report labels and filter domain values in dimension tables. Load detailed atomic data into dimensional structures.
Hierarchy Does not contain Hierarchy Contains Hierarchies. For example Location could contain, country, pin code, state, city, etc.

Type of facts

Type of facts Explanation
Additive Additive facts can be used with any aggregation function like Sum(), Avg() etc. Example is Quantity, sales amount etc.
Semi-Additive Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others. For example, Consider bank account details. You cannot apply the Sum() on the bank balance that does not give useful results but min() and max() function may return useful information
Non-Additive Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table. For example of non-additive fact is any kind of ratio or percentage. Non numeric facts can also be a non-additive facts. Some real-world examples include sales, phone calls, and orders.

Types of Dimensions:

Types of Dimension Definition
Conformed Dimensions Conformed dimensions is the very fact to which it relates. This dimension is used in more than one-star schema or Datamart.
Outrigger Dimensions A dimension may have a reference to another dimension table. These secondary dimensions called outrigger dimensions. This kind of Dimensions should be used carefully.
Shrunken Rollup Dimensions Shrunken Rollup dimensions are a subdivision of rows and columns of a base dimension. These kinds of dimensions are useful for developing aggregated fact tables.
Dimension-to-Dimension Table Joins Dimensions may have references to other dimensions. However, these relationships can be modeled with outrigger dimensions.
Role-Playing Dimensions A single physical dimension helps to reference multiple times in a fact table as each reference linking to a logically distinct role for the dimension.
Junk Dimensions It a collection of random transactional codes, flags or text attributes. It may not logically belong to any specific dimension.
Degenerate Dimensions Degenerate dimension is without corresponding dimension. It is used in the transaction and collecting snapshot fact tables. This kind of dimension does not have its dimension as it is derived from the fact table.
Swappable Dimensions They are used when the same fact table is paired with different versions of the same dimension.
Step Dimensions Sequential processes, like web page events, mostly have a separate row in a fact table for every step in a process. It tells where the specific step should be used in the overall session.

Types of Fact Tables

Fact-less Fact Tables

A fact table that does not contain any measure is a fact-less fact table. This table will only contain keys from different dimension tables. This is often used to resolve a many-to-many cardinality issue

Centipede Fact Table

Centipede fact table is a normalized fact table. Modeller may decide to normalize the fact instead of snow flaking dimensions tables

Conformed Fact Tables

They are measures re-used across multiple dimension models. For example, KPI such as profit, revenue etc

Snapshot Fact Tables

This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table

Cumulative Fact

This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative fact table

Types of Dimension Tables

Slowly Changing Dimensions (SCD)

This is the popular dimension type. Attributes of a dimension that would undergo changes over time. It depends on the business requirement whether particular attribute history of changes should be preserved in the data warehouse. This is called a slowly changing attribute and a dimension containing such an attribute is called a slowly changing dimension. Eg. Home Address doesn’t change often, its a SCD attribute

Rapidly Changing Dimensions

A dimension attribute that changes frequently is a rapidly changing attribute. If you don’t need to track the changes, the rapidly changing attribute is no problem, but if you do need to track the changes, using a standard slowly changing dimension technique can result in a huge inflation of the size of the dimension. One solution is to move the attribute to its own dimension, with a separate foreign key in the fact table. This new dimension is called a rapidly changing dimension. Eg. Body Temperature is a rapidly changing attribute

Junk Dimensions

A junk dimension is a single table with a combination of different and unrelated attributes to avoid having a large number of foreign keys in the fact table. Junk dimensions are often created to manage the foreign keys created by rapidly changing dimensions. For example, attributes such as flags, weights, BMI (body mass index) etc

Inferred Dimensions

While loading fact records, a dimension record may not yet be ready. One solution is to generate a surrogate key with null for all the other attributes. This should technically be called an inferred member, but is often called an inferred dimension

Conformed Dimensions

A dimension that is used in multiple locations is called a conformed dimension. A conformed dimension may be used with multiple fact tables in a single database, or across multiple data marts or data warehouses. Conformed dimension example would be Customer dimension, i.e. both marketing and sales department can use Customer dimension for their reporting purpose

Degenerate Dimensions

A degenerate dimension is when the dimension attribute is stored as part of fact table, and not in a separate dimension table. These are essentially dimension keys for which there are no other attributes. In a data warehouse, these are often used as the result of a drill through query to analyze the source of an aggregated number in a report. You can use these values to trace back to transactions in the OLTP system. For example, receipt number does not have dimension table associated with it. Such details are just for information purpose

Role Playing Dimensions

A role-playing dimension is one where the same dimension key – along with its associated attributes – can be joined to more than one foreign key in the fact table. For example, a fact table may include foreign keys for both ship date and delivery date. But the same date dimension attributes apply to each foreign key, so you can join the same dimension table to both foreign keys. Here the date dimension is taking multiple roles to map ship date as well as delivery date, and hence the name of role playing dimension. For example, you can use a date dimension for “date of sale”, as well as “date of delivery”, or “date of hire”

Shrunken Dimensions

A shrunken dimension is a subset of another dimension. For example, the orders fact table may include a foreign key for product, but the target fact table may include a foreign key only for productcategory, which is in the product table, but much less granular. Creating a smaller dimension table, with productcategory as its primary key, is one way of dealing with this situation of heterogeneous grain. If the product dimension is snowflaked, there is probably already a separate table for productcategory, which can serve as the shrunken dimension

Static Dimensions

Static dimensions are not extracted from the original data source, but are created within the context of the data warehouse. A static dimension can be loaded manually – for example with status codes – or it can be generated by a procedure, such as a date or time dimension