Types of Dimensions in Data Warehouse: Alright, let’s start with the basics: What are dimensions in data warehouse systems? Think of dimensions like the “who, what, where, when, and how” of your data. They provide context to numbers. On their own, figures mean very little—but add a label, a time, a location, and a product type, and suddenly that number tells a story.
Data is just a bunch of numbers until you give it dimensions.
In technical terms, a dimension is a structure that categorizes facts and measures to enable users to answer business questions.
For example, if your sales report says ₹50,000, you’d ask:
- ₹50,000 of what?
- Sold where?
- Sold when?
- Sold to whom?
All of those answers come from dimensions.
Fact Table and Dimension Table in Data Warehouse: How Do They Work?
Now before we explore the types of dimensions in data warehouse, we need to get this straight: there are two main types of tables in a data warehouse.
1. Fact Table
This is where the action happens! A fact table stores measurable, quantitative data like sales amount, profit, quantity sold, etc.
2. Dimension Table
This is the context provider. A dimension table stores attributes related to the fact data. It tells you the “who,” “what,” and “where.” These include things like customer name, product category, time, and geography.
For example:
In a sales data warehouse:
- Fact Table = Sales transactions
- Dimension Tables = Time, Customer, Product, Region
To explore this deeper, check our Ze Learning Labb course on Data Analytics – it explains how to create and manage these tables hands-on. And if you want to learn a bit more about Data Warehouse right now, check out ZELL’s blog on the following:
- Data Warehouse in Data Mining: Role, Types, Benefits, Functions, And More
- Key Functions Of A Data Warehouse
- What Is The Difference Between Data Warehouse and Data Mining? Advantages, Disadvantages N’ More
What is Dimension in Data Warehouse with Example?
Let’s make it real. Imagine a healthcare setup.
Fact Table: Hospital visits (includes count of visits, treatment cost, duration)
Dimension Tables:
- Patient Dimension: Patient ID, Name, Age, Gender
- Time Dimension: Date, Month, Year, Quarter
- Doctor Dimension: Doctor ID, Name, Specialty
So, if we ask: “How many visits did male patients aged 60+ make to cardiologists in Q1 2023?” we’re filtering facts using multiple dimensions.
- This is the power of dimensions. And it’s exactly how health data analytics platforms, like those taught in our courses! Check them out here: https://learninglabb.com/
Types of Dimensions in Data Warehouse
Time to explore the meat of the matter. Here are the types of dimensions in data warehouse systems, explained in everyday language.
1. Conformed Dimension
- Meaning: A dimension that’s shared across multiple fact tables or data marts.
- Use Case: Helps in consistency of data analysis.
- Example: A “Date” dimension that’s used in both sales and finance reports.
2. Junk Dimension
- Meaning: A combination of low-cardinality flags or indicators grouped together in one dimension.
- Use Case: Cleans up clutter in fact tables.
- Example: Status flags like “Online/Offline”, “Returned/Not Returned”, “Payment Status”.
3. Degenerate Dimension
- Meaning: A dimension stored in the fact table but without its own table.
- Use Case: Represents transactional data like invoice numbers.
- Example: “Transaction ID” that doesn’t have any descriptive info.
4. Role-Playing Dimension
- Meaning: A single dimension that can be used for multiple roles in the same fact table.
- Use Case: Helps in date tracking.
- Example: A “Date” dimension used as “Order Date”, “Ship Date”, and “Return Date”.
5. Slowly Changing Dimension (SCD)
- Meaning: A dimension that changes over time.
- Use Case: Tracks history of changes.
- Types:
- Type 1 – Overwrite old data
- Type 2 – Keep history by adding new rows
- Type 3 – Add new columns for changes
Curious about how these types are handled in ETL? Our Data Science course covers this using real-time datasets.
6. Snowflake Dimension
- Meaning: A normalized form of a dimension.
- Use Case: Saves storage but adds complexity.
- Example: Product dimension split into Product, Category, and Sub-category tables.
7. Outrigger Dimension
- Meaning: A dimension that references another dimension.
- Use Case: Adds hierarchical structure.
- Example: A store dimension referencing a region dimension.
8. Inferred Dimension
- Meaning: A placeholder dimension row created when actual dimension data is delayed.
- Use Case: Prevents data rejection during ETL loads.
- Example: A blank customer row created when transaction data arrives before customer info.
A Quick Recap Table For You: Types of Dimensions in Data Warehouse
| Dimension Type | Purpose | Example |
| Conformed Dimension | Used across facts | Time, Customer |
| Junk Dimension | Groups flags | Payment Status, Return Flag |
| Degenerate Dimension | In fact, no separate table | Invoice Number |
| Role-Playing Dimension | Same table, different roles | Date as Order/Ship Date |
| SCD (Slowly Changing) | Handles changes over time | Customer Address history |
| Snowflake Dimension | Normalized structure | Product → Category → Department |
| Outrigger Dimension | References another dimension | Store → Region |
| Inferred Dimension | Placeholder during ETL | Unknown Customer ID |
Why Should You Care About Dimensions in Data Warehouse?
Whether you’re building a reporting system, a customer dashboard, or a healthcare analytics platform, understanding these types is a must.
For instance:
- In retail, conformed dimensions ensure your sales reports match your inventory dashboards.
- In healthcare, slowly changing dimensions track patient demographics or insurance plans over time.
Try Answering This!
You’re designing a dashboard for hospital visits. Which types of dimensions would you use to track:
- The doctor who saw the patient?
- Whether the patient was admitted or sent home?
- The treatment plan that changed over months?
Think it through – or better yet, practice it hands-on in our healthcare-oriented institution – LLRI, the Best Institute for PG Diploma in Clinical Research.
How Ze Learning Labb Can Help You Master This
Ready to get your hands dirty and not just read about it? Ze Learning Labb has you covered.
Learn It Practically:
- Data Science Course: Covers modeling, dimensions, and handling large datasets.
- Data Analytics Course: Focuses on BI tools like Power BI and Tableau using dimensions and facts.
On A Final Note…
By now, you should have a good grip on:
- What are dimensions in data warehouse
- The role of fact table and dimension table in data warehouse
- And of course, the various types of dimensions in data warehouse
These concepts aren’t just theory, they’re the foundation of real-world data systems. From e-commerce to banking to patient tracking systems, understanding these dimensions can transform how we make data-driven decisions.