DIMENSIONAL MODELING

24BSCS- 4th semester

  Introduction to DM
AGENDA  Example
 Key Components of DM:
 Fact table
 Dimension table
 Schema types ( 1 – Star schema,
2 – Snowflake schema )
Fact Table
 Dimension Table
 Difference btw fact & dimension table
 Why Dm is important
 Conclusion
WHAT IS DIMENSIONAL
MODELING?

A technique used in data warehousing to organize data

efficiently

Helps businesses analyze data easily

 Dimensional modeling is a data design
technique used in data warehousing to
organize large amounts of data in a simple
and structured way. It focuses on dividing
data into facts (measurable data like sales,
WHAT IS revenue) and dimensions (descriptive data
DIMENSIONAL like time, product, customer). This structure
MODELING? makes it easier to store, retrieve, and
manage data efficiently, often using models
like star schema or snowflake schema.
WHAT IS DIMENSIONAL MODELING?
It is mainly used to help businesses analyze data quickly and
make better decisions. By simplifying complex data into
understandable formats, dimensional modeling improves query
performance and makes reporting more user-friendly. Analysts
and decision-makers can easily explore trends, patterns, and
insights without dealing with complicated database structures.
Example: Imagine a supermarket database
that stores sales records
The store needs to analyze total
sales per month, top-selling
products, and customer buying
trends
Imagine a supermarket database that stores daily sales
transactions such as product name, quantity, price, date, and
customer details. Using dimensional modeling, this data is
organized into a fact table (sales) that stores measurable
values like total amount and quantity sold, and multiple
dimension tables such as time (day, month, year), product
(name, category), and customer (age, location). This
structured design (often a star schema) makes the data
simple and easy to query.
With this setup, the store can
easily perform analysis like total
sales per month using the time
dimension, identify top-selling
products using the product
dimension, and understand
customer buying trends using
the customer dimension. This
helps the supermarket make
better decisions, such as
managing inventory, planning
promotions, and improving
customer satisfaction.
KEY COMPONENTS OF DIMENSIONAL MODELING
• Fact Table: Stores measurable data(e.g. sales,profit)
• Stores quantitative (measurable) data such as sales
amount, profit, and quantity sold.
It contains foreign keys that link to dimension tables.
Fact tables are used for calculations like totals,
averages, and trends.
Example: total daily sales of products.
KEY COMPONENTS OF DIMENSIONAL MODELING
• Dimension Table: Stores descriptive details (e.g. product
names, customer details )
• Stores descriptive information like product names,
customer details, and dates.
It provides context to the data stored in the fact table.
Helps in filtering, grouping, and labeling data during
analysis.
Example: product category, customer city, or purchase
date.
KEY COMPONENTS OF DIMENSIONAL MODELING
• Schema Types:
• Star schema( simple & fast )
A simple structure with one central fact table connected to
multiple dimension tables.
Easy to design and provides fast query performance.
Widely used in data warehousing for reporting and analysis.
Best for straightforward and quick data access.
KEY COMPONENTS OF DIMENSIONAL MODELING
• Schema Types:
• Snowflake schema(organized but complex)
A more complex structure where dimension tables are further
normalized.
Reduces data redundancy by splitting dimensions into multiple
related tables.
Takes more time to query due to multiple joins.
Best for complex data relationships and detailed organization.
FACT TABLE
Definition
A Fact Table is a primary table in a dimensional
model (like a Star Schema) that stores quantitative
data. It is designed to hold the "facts" or metrics of
a business process—specifically numbers and
figures that can be mathematically manipulated
(summed, averaged, etc.).
FACT TABLE
Real-life Example: Shopping Mall Database
In the context of a shopping mall’s database, the Fact
Table captures the specific details of every transaction.
Key examples of data stored include:
Date of purchase: The specific time the event occurred
(often linked to a Dimension Table).
Amount spent: The monetary value of the transaction (a
quantitative measure).
Number of items bought: The volume of products sold in
a single session.
DIMENSION TABLE
Definition
A Dimension Table is a database table used to store
descriptive data (attributes) that provides context to
numerical facts. Its primary purpose is to help
categorize, filter, and label the quantitative data
found in a Fact Table.
DIMENSION TABLE
Real-life Example: Restaurant Database
In a restaurant's data model, Dimension Tables provide
the "who, what, and where" for every transaction.
Examples of descriptive data include:
Food item names: Specific details about products (e.g.,
"Cheeseburger," "Pasta Carbonara").
Customer preferences: Information regarding dining
habits or dietary requirements.
Locations of different restaurant branches: Descriptive
geographic data such as city, street address, or branch ID.
Feature Fact Table Dimension Table
Usually located at the center of a Usually located at the edges,
Schema Design
Star or Snowflake schema. surrounding the Fact table.
Deep and Narrow: Contains
Wide and Shallow: Contains fewer rows
Table Size millions/billions of rows but fewer
but many descriptive columns.
columns.
Uses a composite primary key (a
Uses a single primary key (often a
Primary Keys combination of multiple foreign
surrogate key).
keys).

Data is added constantly as new Data changes infrequently (e.g., a

Change Frequency
transactions occur. customer changing their address).

Contains quantitative measures Contains qualitative attributes (e.g.,

Data Nature
(e.g., price, quantity, temperature). color, brand, city name).

Usually completely denormalized to Can be normalized (in a Snowflake

Normalization
improve query speed. schema) or denormalized.
WHY DIMENSIONAL MODELING IS IMPORTANT?

• Dimensional Modeling organizes data efficiently.

• Fact Tables store numbers (sales, revenue).
• Dimension Tables store descriptions (product, customer
info).
• Used in businesses, e-commerce, and analytics.
WHY DIMENSIONAL MODELING IS IMPORTANT?

1. High Query Performance

Dimensional models are designed to
minimize the number of "joins" a database
has to perform. By using a Star Schema—
where a central Fact Table is surrounded by
Dimension Tables—the database can retrieve
complex reports much faster than in a
traditional, highly normalized database.
WHY DIMENSIONAL MODELING IS IMPORTANT?
2. Business User Intuition
In a standard relational database, data is often spread across dozens of
tables, making it hard for non-technical users to find what they need.
Dimensional modeling organizes data into recognizable business
entities:
Dimensions: The "Who, What, Where, When, and Why" (e.g., Date,
Product, Store).
Facts: The "How Much" (e.g., Sales Amount, Quantity Sold). This
structure mimics how business people naturally think about their
performance.
WHY 3. Flexibility and Scalability

DIMENSIONAL
MODELING IS
IMPORTANT? Dimensional models are incredibly resilient to
change. You can easily:

Add new facts: Introduce a new metric (like "Tax

Amount") to the Fact Table without rebuilding the
entire system.

Add new dimensions: Add a new category (like

"Season") to a Dimension Table without affecting
existing reports.
WHY DIMENSIONAL MODELING IS IMPORTANT?

4. Consistent Data Reporting (Conformed Dimensions)

By using "Conformed Dimensions" (shared dimensions across
different Fact Tables), a company ensures that "Revenue" or
"Product Name" means the same thing across the Marketing,
Finance, and Sales departments. This creates a "single version of
the truth."
WHY DIMENSIONAL MODELING IS IMPORTANT?

5. Efficient Data Aggregation

Since Fact Tables store quantitative data, it is computationally
simple for BI tools (like Power BI or Tableau) to perform
aggregations. Whether you want to see sales by Day, Month, or
Year, the dimensional structure allows for "drilling down" or
"rolling up" data instantly.
CONCLUSION:
Dimensional Modeling organizes data
efficiently.
Fact Tables store numbers (sales,
revenue).
Dimension Tables store descriptions
(product, customer info).
Used in businesses, e-commerce, and
analytics.
THE END