A data warehouse dimension provides the means to “slice and dice” data in a data warehouse. Dimensions provide structured labeling information to otherwise unordered numeric measures. A dimensional data element is similar to a categorical variable in statistics.
For example, “Customer”, “Date”, and “Product” are all dimensions that could be applied meaningfully to a sales receipt.
The primary function of dimensions is threefold: to provide filtering, grouping and labeling. For example, in a data warehouse where each person is categorized as having a gender of male, female or unknown, a user of the data warehouse would then be able to filter or categorize each presentation or report by either filtering based on the gender dimension or displaying results broken out by the gender.
Each dimension in a data warehouse may have one or more hierarchies applied to it. For the “Date” dimension, there are several possible hierarchies: “Day > Month > Year”, “Day > Week > Year”, “Day > Month > Quarter > Year”, etc.
Types of Dimension
Confirmed Dimension: Dimension connecting to multiple facts is called confirmed dimension. This dimension does not change with time. Some examples are time dimension, customer dimension and product dimension
Junk Dimension: Consolidated dimension from several (two or more) smaller dimension is called junk dimension.
Degenerated Dimension: Fact containing attribute from dimension is called degenerated dimension. Fact generally contains measure, but since one or more attribute of (to be) dimension is present inside this fact, it is treated as dimension.
Slowly Changing Dimensions (SCD): If the data in a dimension table changes over time then it is called as Slowly Changing Dimension.
Simple example is Address attribute in a Customer Dimension which may be changing over time. Based on the business needs this particular information is stored.
Slowly changing dimensions are generally classified into three types.
SCD1: Only maintain Updated/Current values.
Ex: a customer address modified we update existing record with new address.
SCD2: Maintain historical and current information by using A) Effective Date B) Versions C) Flags or combination of all three of those.
SCD3: By adding new columns to target table to maintain both historical and current information