Table of Contents
Dimensional modeling is an essential technique for optimizing data warehouses for fast query performance and business analysis. But it‘s also shrouded in mystery with confusing terms like "fact constellations", "degenerate dimensions" and "slowly changing dimensions".
In this guide, I‘ll explain dimensional modeling in simple terms with plenty of examples. I‘ll also bust some common myths and misconceptions.
Whether you‘re a business analyst learning about data warehousing or a developer building one, you‘ll have a firm grasp of dimensional modeling by the end of this guide.
What is Dimensional Modeling and Why It Matters
Dimensional modeling structures data in a data warehouse for analysis using simple tables called facts and dimensions. Facts contain the quantitative metrics like sales amount. Dimensions describe facts, like customer name, product, date, and store location.
By organizing data into facts and dimensions, dimensional modeling provides powerful analytical capabilities not possible in a traditional transactional database. Let‘s examine why:
Easy Data Exploration: Dimensions use simple descriptors clearly defined for business users using granular attributes and hierarchies. This enables intuitive exploration and drilling down into data.
Optimized Queries: Queries involve very few table joins, boosting performance. Typical queries hit 1 fact table joined to 5 or lesser dimensions only.
Flexible Analytics: You can analyze metrics at the right level, from daily, monthly to quarterly or yearly. This is defined during model design unlike rigid transactional models.
Agility: Adding new reports, metrics or breakdowns is easy by extending the star schema. Changes happen only at the edges by modifying dimensions or facts independently.
Simply said, dimensional modeling creates an analytical representation of data perfectly optimized for business reporting.
While most concepts are straightforward, dimensional modeling has its own lexicon and some subtleties that can confuse newcomers. So let‘s get our terminologies right…
Demystifying Dimensional Modeling Jargon
As we just discussed, facts and dimensions are the building blocks of dimensional modeling. Now let‘s peel the onion and see what makes them tick:
Facts are numeric metrics like sales dollars or quantity sold. Facts can track events at the transaction level or snapshots over time. Facts are optimized for queries with no overhead from constraints, indexes or dependencies typical in transactional systems.
Dimensions describe facts to give them context and meaning. Think of dimensions as lookup tables with descriptive attributes. Examples are customers, products, market regions or application modules. Well-designed dimensions are denormalized with hierarchies and relationships clearly defined.
Now that we have facts and dimensions covered, let‘s move on to some special dimension types you may encounter:
- Conformed dimensions: Same dimension reused across multiple business processes like customer.
- Role-playing dimension: Single dimension used multiple ways like order date and ship date.
- Junk dimension: Consolidates random flags and attributes not belonging in any other dimension.
- Slowly changing dimension (SCD): Dimensions that change over time requiring special handling when data history must be preserved.
We‘ll revisit slowly changing dimensions later in detail.
Here are some other dimensional modeling terms you may hear:
Star schema refers to structure containing one fact table referenced by multiple dimensions. Extended to create a snowflake schema by normalizing dimensions into multiple child tables.
Fact constellation models enterprise data with multiple fact tables sharing conformed dimensions. This enables a single unified view combining metrics from multiple business processes.
Granularity denotes the level of detail present in a fact table, from daily events to summarized monthly/quarterly snapshots. Query performance also depends significantly on granularity.
That covers the basic vocabulary. Let‘s now dispel some common myths you may come across…
Busting Dimensional Modeling Myths
Like any widely used technique, dimensional modeling also has a fair share of myths and misinterpretations among professionals. Let me dispel some big ones right away:
Myth: Dimensional models only store summarized data
Reality: Facts can store very granular data at the transaction level or periodic snapshots. The granularity depends on analytical needs.
Myth: Dimensions are created only for reporting
Reality: Dimensions centralize reference data used across multiple processes. They ensure consistency in terminology and business logic across BI reports, analytics as well as transactional systems.
Myth: Dimensional models are static and inflexible
Reality: Dimensions can accommodate new attributes flexibly thanks to their denormalized nature. Entirely new dimensions can also be added without affecting existing ones.
Now that we have a good grasp of the concepts and cleared some common myths, let‘s move on to some real-world examples.
Hands-on Examples
Let‘s illustrate dimensional modeling concepts using some concrete examples you can apply in your projects.
We will start by looking at slowly changing dimensions in detail with example scenarios.
Tackling Slowly Changing Dimensions
Slowly changing dimensions (SCDs) refer to dimensions where attributes change over time, unlike static reference data. Special handling is required when you must track historical changes.
Here are the standard SCD types and how each models changing data:
Type 1: Overwrites history, old values are lost. Use when tracking only current data.
Type 2: Creates new record for each change, retains full history. Large storage overhead if many attributes change frequently.
Type 3: Alters schema to store historical values in separate columns. Compact but custom code needed to query historical values.
Let‘s see some examples of slowly changing dimensions in action:
Patient Demographics: Requires type 2 SCD to track history of address changes, insurance policies over time.
Product Catalog: Size, color changes need Type 1 overwrite. But price changes need Type 2 history retention.
Employee Skills: Skill set changes require Type 3 approach with previous skills also preserved.
As you can see, used judiciously, slowly changing dimensions enable you to model the complete picture over time rather than just current snapshot.
Now let‘s switch examples and look at a simplified sales dimensional model in action…
Sample Sales Dimensional Model
The example below shows a star schema with:
- A sales fact table containing metrics like quantity and amount sold
- Four dimensions describing sales – store, product, promotion and customer
Sales Star Schema – Image Source: casetern.com
Here, dimensions provide context to sales data with descriptors like customer name, demographics, product categories and types, store regions and tiers. This enables analyzing sales by different dimensions like promotion, sliced by customer age groups across certain region‘s premium stores.
The sales fact table centralizes metrics delivered by upstream ETL process aggregating data from transactions, inventory, budgets and other sources. Granularity could be at day level or summarized to month/quarter level as required.
Let‘s wrap up this section by comparing and contrasting dimensional modeling with the traditional normalized modeling.
Dimensional vs. 3NF Modeling
Newcomers to data warehousing often wonder why a separate dimensional model is needed instead of simply using the 3NF model powering their operational transactions.
Here‘s a quick comparison to illustrate the key differences and use cases:
| 3NF Transactional Model | Dimensional Model | |
|---|---|---|
| Optimized for | Atomic data capture, integrity | Flexible analytics |
| Structure | Deeply normalized,POINT heavy inter-table dependencies | Denormalized into star schema, minimizing table joins |
| Queries | Primary key lookups on individual transactions | Summarized data analysis involving aggregated metrics |
| Flexibility | Rigid schema changes impact multiple dependencies | Isolated impact within star, easy to extend |
| Use Cases | OLTP systems, operational reporting | Data warehousing, BI and analytics |
To summarize, normalized models 3NF aim for correctness by reducing data redundancy. Dimensional models focus on usability by structuring data spread across systems for unified analysis and reporting.
In Closing
Let‘s recap what we learned about dimensional modeling:
- Dimensional modeling structures data into facts containing metrics and dimensions with descriptive attributes to enable insightful analysis and reporting.
- Special dimension types like slowly changing dimensions handle changes over time.
- Dimensional modeling myths debunked include being only suitable for summarized data and inability to handle changes.
- Real-world examples illustrate dimensional modeling concepts like handling history in patient data and sales data analysis.
Dimensional data modeling is integral to any enterprise data warehouse implementation. Mastering concepts like conformed dimensions and slowly changing types takes time and practice. With the right foundation you are well on your way to tackle your next data warehousing project confidently!