A Short Introduction to Dimensional Modeling
To begin, let's clarify what a star schema is and why dimensional modeling matters in modern data analytics.
A star schema is best explained visually:
Looking at the diagram, it's easy to see why it's called a "star schema." The structure resembles a star, with a central fact table connected to surrounding dimension tables.
A star schema consists of two main object types:
- Fact tables – These describe business processes by containing quantitative data about events. Examples include sales transactions, bank transactions, stock levels, HR data (e.g., salaries, headcounts), or event logs like building entries.
- Dimensions – These tables provide descriptive context, such as transaction dates, customer information, account details, product attributes, or geographical location.
Because multiple dimensions are involved, the approach is also known as dimensional data modeling. However, the term "star schema" is more intuitive and widely adopted.
This modeling method offers several benefits:
- Easy for business users to understand and use.
- Clear separation between transactional and descriptive data.
- Can handle complex business scenarios.
- Industry standard for reporting models in data warehousing and BI tools like Power BI, Tableau, and Looker.
In fact, the star schema remains the de facto standard for analytical modeling in 2025, especially with the rise of cloud data platforms and semantic layers that rely on these principles.
Defining a dimension might seem straightforward, but dimensions come in various types, each serving a unique purpose. Let's explore them.
The Main Types of Dimensions
There are three primary types of dimensions:
- Regular dimensions – The most common type, describing core business entities like customer, product, or geography.
- Junk dimensions – Combine multiple unrelated attributes into one dimension, avoiding a proliferation of small tables.
- Degenerate dimensions – Store frequently changing or transactional data directly in the fact table, such as a transaction number.
Let's dive deeper into each type.
Regular Dimensions
Regular dimensions are the most prevalent dimension type. They contain descriptive attributes that characterize a business entity, such as customers, products, or locations.
A regular dimension follows these rules:
- Each row represents a unique occurrence of an entity.
- Each row is uniquely identifiable (typically via a surrogate key).
- Data is denormalized—meaning it includes redundant information, unlike normalized schemas in 3rd normal form (3NF). For more details, check the References section for links on 3NF and modeling data in normalized form.
Additionally, a regular dimension may include:
- Hierarchies (e.g., category → subcategory → product).
- Additional attributes (e.g., color, size, or brand).
- Historization fields to track changes over time (e.g., start/end dates for slowly changing dimensions).
Here's an excerpt from the Product dimension in the ContosoRetailDW sample dataset:
In practice, regular dimensions are the backbone of most star schemas. They are designed to be intuitive for business users and are often stored as physical tables in data warehouses or as views in semantic layers.
For example, a Customer dimension might include columns like CustomerID, Name, Segment, City, Country, and Email. Each row corresponds to one customer, and the table is denormalized with all relevant attributes for reporting.
Junk Dimensions
When a fact table has many miscellaneous attributes—such as payment method, delivery type, or promotion flag—creating a separate dimension for each would lead to a cluttered schema. Instead, these attributes are grouped into a single junk dimension.
A junk dimension consolidates low-cardinality flags and codes that don't belong to any particular business entity. For instance, in a sales fact table, you might have columns like:
- OrderType (e.g., "Online," "In-store")
- DeliveryMethod (e.g., "Standard," "Express")
- PromotionApplied (e.g., "Yes," "No")
Instead of creating three separate dimensions, you combine all possible combinations of these attributes into one junk dimension table. This reduces the number of joins and keeps the schema lean.
The primary benefit of a junk dimension is schema simplification. It minimizes the number of dimension tables, making the model more manageable. However, the drawback is that the junk dimension has no inherent business meaning; it's purely a technical construct.
When to use: Use a junk dimension when you have multiple small, unrelated attributes that are used for filtering or grouping in reports. This approach is particularly useful in scenarios with many flags and codes, as seen in retail, finance, or logistics.
For example, in an e-commerce database, you could use a junk dimension to capture order source, payment type, and delivery preference, allowing analysts to filter by any combination without cluttering the fact table.
Degenerate Dimensions
Sometimes, a fact table contains attributes that are neither purely factual nor descriptive, such as a transaction number, order number, or invoice number. These are called degenerate dimensions.
A degenerate dimension is a dimension that is stored directly in the fact table, without a separate dimension table. It typically represents a unique identifier that provides context but has no other attributes.
For instance, consider a sales fact table with columns:
- OrderID
- LineNumber
- ProductID
- Quantity
Here, OrderID and LineNumber are degenerate dimensions—they are transactional identifiers that are not linked to any dimension table. They are used to uniquely identify rows and allow for detailed analysis (e.g., grouping by order).
When to use: Use a degenerate dimension when you need to retain transactional or event-level identifiers in the fact table for granularity and traceability. This is common in sales, logistics, and financial transactions where order numbers or ticket IDs are crucial for reporting.
The key advantage is that it reduces the number of joins and improves query performance. However, it's important to note that degenerate dimensions do not contain descriptive attributes; they are purely identifiers. To get more details, you would need to join to other tables, but often, the identifier itself is sufficient for analysis.
In a banking context, for example, a transaction ID would be a degenerate dimension; it uniquely identifies each transaction but doesn't provide descriptive info like account type or branch location—that's left to other dimensions.
How to Choose the Right Dimension Type
Selecting the appropriate dimension type depends on the nature of your data and reporting needs. Here's a quick guide:
- Regular dimensions are your go-to for most business entities. They are suitable when you have descriptive attributes like names, categories, or dates.
- Junk dimensions are ideal for grouping low-cardinality flags and status fields that are unrelated to each other. They prevent schema bloat and keep models clean.
- Degenerate dimensions are best for preserving transactional identifiers in fact tables, especially when detailed, row-level analysis is required.
By mixing these types, you can optimize your star schema for performance, maintainability, and user comprehension.
Conclusion
Understanding the different types of dimensions is essential for effective star schema design. Regular dimensions form the core, junk dimensions simplify schemas by grouping miscellaneous attributes, and degenerate dimensions preserve transactional identifiers. By applying these concepts, you can build robust data models that support complex analytical queries and deliver value to business users.
As data continues to grow in complexity, mastering dimensional modeling remains a critical skill for data professionals. Whether you're designing a warehouse or creating a semantic layer for self-service BI, these dimension types will help you structure data in a way that is both efficient and intuitive.
For further reading, explore the References below on 3rd normal form and normalized modeling.
References
For more on 3rd normal form (3NF) and normalized data modeling, please refer to the two links provided in the original article (insert links here).