Dimensional modeling is a data warehouse design technique that organizes data into fact tables and dimension tables to optimize analytical queries and reporting. It is commonly used in OLAP systems because it simplifies querying and provides better performance for business intelligence workloads.
Fact Table
- A fact table stores measurable business events or metrics.
- It typically contains numeric measures and foreign keys to dimension tables.
- The grain of the fact table defines what a single row represents.
Example:
In an e-commerce system, an Order Fact table could store:
order_id
customer_key
product_key
date_key
quantity
sales_amount
discount_amount
Each row may represent one product within an order.
Dimension Table
A dimension table stores descriptive attributes that provide context to facts.
Dimensions answer the business questions: Who, What, When, Where and How
Examples:
Customer Dimension
customer_key
customer_name
city
state
country
Product Dimension
product_key
product_name
category
brand
Conformed Dimensions
A conformed dimension is a dimension shared across multiple fact tables with the same meaning and structure.
Example:
Customer Dimension used by: Sales Fact, Returns Fact, Shipment Fact
This ensures consistent reporting across business processes.
Grain
The grain defines exactly what a single row in the fact table represents. It is the most important step in dimensional modeling and should be defined before identifying facts and dimensions. For example: One row per order, One row per product in an order.
Once the grain is defined, every fact and dimension must be consistent with that grain.
Interview Summary for Dimensional Modelling (30-Second Answer)
Dimensional modeling is a data warehouse design approach that organizes data into fact and dimension tables for analytical reporting. Fact tables store business measures, while dimension tables store descriptive attributes that provide context. Before designing a fact table, we define its grain, which specifies exactly what a row represents. We can also have conformed dimensions, which are shared dimensions used across multiple fact tables to ensure consistent reporting.
Star Schema - Data Model
- It is a data model, it is used to deformalize business data into dimensions (like time and product) and facts (like transactions in amounts and quantities)
- A star schema has a single fact table in the center, containing business facts. The fact table connects multiple other dimension table.
- Star Schema deformalize the data which means adding redundant columns to some dimension table.
- In this model, the fact tables is normalized but the dimensions tables are not.
Snowflake Schema - Data Model
- A snowflake schema is a multi-dimensional data model that is an extension of a star schema, where dimension table are broken into sub-dimensions.
- In this data model, the individual dimension tables are broken down into logical sub-dimensions. This make data model more complex but it can be easier for analysts to work with.
Snowflake vs Star Schema
Snowflake schemas offer more storage efficiency, due to their tighter adherence to high normalization standards, but query performance is not as good as with more denormalized data models. Denormalized data models like star schemas have more data redundancy (duplication of data), which makes query performance faster at the cost of duplicated data.