DIMENSION:
The dimension table contains dimensions of fact.
It contains keys and descriptive attributes for measurements in the fact table.
TYPES OF DIMENSION:
Conformed dimension:
Conformed dimensions mean the exact same thing with every possible fact table to which they are joined.Example:
The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts.
Junk dimension:
A junk dimension is a collection of random transactional codes flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a convenient place to store the junk attributes.Example:
Assume that we have a gender dimension and marital status dimension. In the fact table, we need to maintain two keys referring to these dimensions. Instead of that create a junk dimension that has all the combinations of gender and marital status (cross join gender and marital status table and create a junk table). Now we can maintain only one key in the fact table.
Degenerate dimension:
A degenerate dimension is a dimension that is derived from the fact table and doesn’t have its own dimension table.
Example:
A transactional code in a fact table.
Role-playing dimension:
Dimensions that are often used for multiple purposes within the same database are called role-playing dimensions.
Example:
A date dimension can be used for “date of sale”, as well as “date of delivery”, or “date of hire”.
SCD – Slowly Changing Dimension
TYPE – 1: Updates changes without preserving history.
TYPE – 2: Maintains history by storing the value as a new record.
- Effective date
- Flagging
- Versioning
TYPE – 3: Maintains history as the current and previous
version column-wise.
No comments:
Post a Comment