Dimension table contains the descriptive information about the numerical values in the fact table, i.e they contain the attributes of the facts.
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.
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