Data warehouse

Dimensions in Data Warehouse

Dimension Table
  • Dimension table contain s 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 marketing analysis application might include attributes such as time period, marketing region and product type.
  • Data in dimension table is denormalized, it typically has a large number of columns. The dimension table typically contains fewer rows of data than the fact table.
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.
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.
Time Dimension: It is a dimension which can be shared by two or more facts, Which stores the data information.
The example of Time dimension is confirmed dimension

Powered by Blogger.