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:<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Nature: <\/mark>Shared and consistent<\/mark> dimensions across multiple data marts or warehouses. 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.<\/p>\n\n\n\n Nature<\/mark>: Dimensions used in multiple roles within the same fact table<\/mark>. 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.<\/p>\n\n\n\n Nature<\/mark>: Combination<\/mark> of low-cardinality flags or attributes into a single dimension<\/mark>. 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).<\/p>\n\n\n\n Nature<\/mark>: Captures changes<\/mark> to dimension data over time<\/mark>. Type 1 Slowly Changing Dimension (SCD1):<\/strong> 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.<\/p>\n\n\n\n Nature<\/mark>: Smaller, focused<\/mark> dimension tables for specific contexts<\/mark>. 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.<\/p>\n\n\n\n Nature<\/mark>: Normalized dimension tables<\/mark> with hierarchies stored in separate related tables. 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.<\/p>\n\n\n\n Nature<\/mark>: Created by deriving new<\/mark> attributes or hierarchies from existing<\/mark> ones. 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.<\/p>\n\n\n\n Nature<\/mark>: Attributes in the fact table<\/mark> that would be in a dimension table but are not useful for analysis across multiple facts<\/mark>. 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 […]<\/p>\n","protected":false},"author":1,"featured_media":338,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[],"class_list":["post-328","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-warehouse"],"_links":{"self":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/328","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/comments?post=328"}],"version-history":[{"count":14,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/328\/revisions"}],"predecessor-version":[{"id":463,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/328\/revisions\/463"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media\/338"}],"wp:attachment":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media?parent=328"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/categories?post=328"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/tags?post=328"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Cardinality<\/mark>: Typically high<\/mark> cardinality.
Example<\/mark>: Customer dimension shared across sales and marketing data marts.
Use Case<\/mark>: Ensures a standardized view of entities, promoting consistency in reporting across the organization<\/mark>.<\/p>\n\n\n\nRole-Playing Dimensions<\/h5>\n\n\n\n
Cardinality<\/mark>: High<\/mark> cardinality with different perspectives.
Example<\/mark>: Date dimension used for both order date and ship date<\/mark>.
Use Case<\/mark>: Allows for analysis from different viewpoints<\/mark> within the same fact table.<\/p>\n\n\n\nJunk Dimensions<\/h5>\n\n\n\n
Cardinality<\/mark>: Low to moderate<\/mark> cardinality.
Example<\/mark>: Combining Boolean flag<\/mark>s like „Promotion Applied“ and „Discount Applied.“<\/mark>
Use Case<\/mark>: Reduces the number of dimension tables, simplifying the schema and improving query performance<\/mark>.<\/p>\n\n\n\nSlowley Changing Dimensions (SCD)<\/h5>\n\n\n\n
Cardinality<\/mark>: May involve high cardinality for historical versions<\/mark>.<\/p>\n\n\n\n
Example<\/mark>: 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<\/mark>.
Use Case<\/mark>: Appropriate<\/mark> for situations where historical changes are not important<\/mark>, and the focus is on the current state of the data.
Type 2 Slowly Changing Dimension (SCD2):<\/strong>
Example<\/mark>: 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<\/mark>, capturing the changes over time.
Use Case<\/mark>: Useful when it’s important to track historical changes<\/mark> and maintain a historical view of the data.
Type 3 Slowly Changing Dimension (SCD3):<\/strong>
Example<\/mark>: 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<\/mark> address. This allows for a limited history, showing the current and previous addresses.
Use Case:<\/mark> Suitable<\/mark> for scenarios where a limited historical perspective<\/mark> is needed, and maintaining a full history is not a priority. It provides a compromise between simplicity and historical tracking.<\/p>\n\n\n\nMini Dimension<\/h5>\n\n\n\n
Cardinality<\/mark>: Moderate to high depending <\/mark>on the scope.
Example<\/mark>: Creating a mini-dimension for high-value customers<\/mark> within the overall customer dimension.
Use Case<\/mark>: Improves query performance by reducing unnecessary attributes<\/mark> for certain analyses.<\/p>\n\n\n\nSnowflake Dimension<\/h5>\n\n\n\n
Cardinality<\/mark>: Moderate to high<\/mark> depending on the level of normalization.
Example<\/mark>: Normalizing a „Location“ dimension into separate „Country,“ „Region,“ and „City“ tables<\/mark>.
Use Case<\/mark>: Reduces redundancy in storage<\/mark> but may increase complexity in queries.<\/p>\n\n\n\nDerived Dimensions<\/h5>\n\n\n\n
Cardinality<\/mark>: Same as or dependent on the base dimension<\/mark>.
Example<\/mark>: Deriving<\/mark> a „Quarter<\/mark>“ attribute from<\/mark> a „Date<\/mark>“ dimension.
Use Case<\/mark>: Adds flexibility<\/mark> by introducing calculated attributes for analysis.<\/p>\n\n\n\nDegenerated Dimensions<\/h5>\n\n\n\n
Cardinality<\/mark>: Varies, but it can be high<\/mark>. For example, invoice numbers, order numbers, or transaction IDs could have high cardinality.
Example<\/mark>: Invoice number, order number, or transaction ID in a sales fact table<\/mark>.
Use Case<\/mark>: Avoids the need for a separate dimension table<\/mark> for certain attributes associated directly with the fact table<\/mark>.<\/p>\n","protected":false},"excerpt":{"rendered":"