What is fact table in data warehouse?
October 25, 2016
DataMart Vs Data Warehouse
October 25, 2016
Show all

What is dimension table in data warehouse?

Dimension table contains the descriptive information about the numerical values in the fact table, i.e they contain the attributes of the facts.

  • For example the dimension tables for a customer application might include attributes such as customer_name, customer_address, customer_contact, customer_preference etc.
  • Data in dimension table is de-normalized, it typically has a large number of columns. The dimension table typically contains fewer rows of data than the fact table.


There are multiple types of Dimensions.

Based on the characteristics of the tables dimension tables are classified into following types.
Slowly Changing Dimension(SCD):

A slowly changing dimension is a dimension whose attribute or attributes for a record change slowly over a time. Three types of slowly changing dimensions are there.
SCD1: Stores the current data, no history is preserved.
SCD2: Stores the both current and historical data.
SCD3: Stores the current and most recent historical data.

The SCD’s are not limited to only these types but most of the business situations fall into these three types.
Fast Changing Dimension:

A dimension is considered to be a fast changing dimension if one or more of its attributes changes frequently and in many rows. A fast changing dimension can grow very large if we use the type2 approach to track numerous changes. Fast changing dimensions are also called as Rapidly Changing Dimensions.
Role-Playing Dimension:

  • Role-playing occurs when a single physical dimension appears several times in a fact table, each represented as a separate logical table with unique column names through views.
  • A single dimension which is expressed differently in a fact table using views is called as role-playing dimension.
  •  Single dimension appears several times in the same fact table like Orderdate, date, shipdate.
Junk/Garbage Dimension: A junk dimension is a dimension  that consists of low cardinality columns (or) any information not related to the business is called as Junk dimension.
For example codes, flags, indicators, etc.

Audit Dimension: A table which stores statistical information about data warehousing objects.
Degenerated Dimension: A dimension values which will not hold any meaning full information on its own like ordered, trained etc.
It is a dimension which will be stored in a fact table.
Confirmed Dimension: It is a dimension which can be shared by two or more facts, Which stores the data information.

The example of Confirmed dimension is Time dimension