Dimension tables can be categorized into different types based on the nature of the data they represent. Here are some common types of dimension tables:
Conformed Dimensions
Conformed dimensions are dimensions that are shared and consistently used across multiple data marts or data warehouses within an organization. They provide a standardized view of the business entities, ensuring consistency and accuracy in reporting across different parts of the organization.
Nature: Shared and consistent dimensions across multiple data marts or warehouses.
Cardinality: Typically high cardinality.
Example: Customer dimension shared across sales and marketing data marts.
Use Case: Ensures a standardized view of entities, promoting consistency in reporting across the organization.
Role-Playing Dimensions
In data warehousing, role-playing dimensions are versatile entities that wear multiple hats within the same fact table. They enable a single dimension, such as time or geography, to serve different analytical perspectives, enhancing the depth and flexibility of data analysis within the data warehouse.
Nature: Dimensions used in multiple roles within the same fact table.
Cardinality: High cardinality with different perspectives.
Example: Date dimension used for both order date and ship date.
Use Case: Allows for analysis from different viewpoints within the same fact table.
Junk Dimensions
Junk dimensions are created by combining several low-cardinality flags or attributes into a single dimension table. This is useful when dealing with multiple boolean attributes, reducing the number of dimension tables and simplifying the schema.
Nature: Combination of low-cardinality flags or attributes into a single dimension.
Cardinality: Low to moderate cardinality.
Example: Combining Boolean flags like „Promotion Applied“ and „Discount Applied.“
Use Case: Reduces the number of dimension tables, simplifying the schema and improving query performance.
Slowley Changing Dimensions (SCD)
Slowly Changing Dimensions (SCD) represent dimension tables that capture changes to data over time. SCD types include Type 1 (overwrite the old data with the new), Type 2 (maintain historical versions of the data), and Type 3 (maintain limited history with some changes).
Nature: Captures changes to dimension data over time.
Cardinality: May involve high cardinality for historical versions.
Type 1 Slowly Changing Dimension (SCD1):
Example: Suppose you have a „Product“ dimension, and the price of a product changes. With SCD Type 1, you simply update the existing record in the dimension table with the new price. This means that historical records do not retain the old price; they are overwritten with the latest information.
Use Case: Appropriate for situations where historical changes are not important, and the focus is on the current state of the data.
Type 2 Slowly Changing Dimension (SCD2):
Example: Continuing with the „Product“ dimension, if a product’s category changes, you create a new record in the dimension table with the updated category, maintaining the existing record for historical reference. Each record in the dimension table has an effective start and end date, capturing the changes over time.
Use Case: Useful when it’s important to track historical changes and maintain a historical view of the data.
Type 3 Slowly Changing Dimension (SCD3):
Example: In the „Customer“ dimension, if a customer’s address changes, you keep the current address in the main dimension table and create additional columns to store the previous address. This allows for a limited history, showing the current and previous addresses.
Use Case: Suitable for scenarios where a limited historical perspective is needed, and maintaining a full history is not a priority. It provides a compromise between simplicity and historical tracking.
Mini Dimension
Mini-dimensions are used when dealing with large, complex dimensions where only a subset of attributes is relevant for a particular fact table. They involve creating smaller, focused dimension tables containing only the necessary attributes for a specific context.
Nature: Smaller, focused dimension tables for specific contexts.
Cardinality: Moderate to high depending on the scope.
Example: Creating a mini-dimension for high-value customers within the overall customer dimension.
Use Case: Improves query performance by reducing unnecessary attributes for certain analyses.
Snowflake Dimension
While not a type of dimension table per se, the concept of snowflake dimensions involves normalizing dimension tables by breaking them into multiple related tables. In a snowflake schema, hierarchies are stored in separate tables, reducing redundancy but potentially increasing complexity in queries.
Nature: Normalized dimension tables with hierarchies stored in separate related tables.
Cardinality: Moderate to high depending on the level of normalization.
Example: Normalizing a „Location“ dimension into separate „Country,“ „Region,“ and „City“ tables.
Use Case: Reduces redundancy in storage but may increase complexity in queries.
Derived Dimensions
Derived dimensions are created by deriving new attributes or hierarchies from existing dimension attributes. For example, creating a „Month Name“ attribute from a „Date“ dimension.
Nature: Created by deriving new attributes or hierarchies from existing ones.
Cardinality: Same as or dependent on the base dimension.
Example: Deriving a „Quarter“ attribute from a „Date“ dimension.
Use Case: Adds flexibility by introducing calculated attributes for analysis.
Degenerated Dimensions
Degenerate dimensions are attributes in the fact table that would otherwise be in a dimension table but are not useful for analysis across multiple facts. They are typically single-attribute dimensions associated directly with the fact table.
Nature: Attributes in the fact table that would be in a dimension table but are not useful for analysis across multiple facts.
Cardinality: Varies, but it can be high. For example, invoice numbers, order numbers, or transaction IDs could have high cardinality.
Example: Invoice number, order number, or transaction ID in a sales fact table.
Use Case: Avoids the need for a separate dimension table for certain attributes associated directly with the fact table.
