Official Business
Dimensional Modeling
Official Business
Definition
• Dimensional modeling is a data modeling technique used to organize
and structure data in a data warehouse. It focuses on creating easily
understandable and navigable models for reporting and analysis
purposes.
Official Business
Purpose
• To enhance data analysis.
• To enable business intelligence reporting, query, and analysis.
• To support scalability and flexibility requirements of data warehousing
environments.
• To facilitate the integration of diverse data sources within the data
warehouse.
• To ensure data consistency and quality.
Official Business
Key Concepts
• Fact Tables
• Dimension Tables
• Attributes
Official Business
Benefits
• Simplified data analysis
• Improved query performance
• Enhanced business insights
• Flexibility and scalability
• Empowerment of end users
• Consistency and standardization
• Support for business intelligence
Official Business
Examples
• Sales Analysis in Retail
• Healthcare Analytics
• Financial Reporting
• Customer Relationship Management
• Supply Chain Management
Official Business
Star Schema – Sales Example
• Central fact table: Sales
• Connected dimensions: Date, Product, Customer, Store
• Enables slicing sales by time, product category, and customer region
Official Business
Fact Tables
• A key table in data warehousing that maintains quantitative data
(facts) about a business process or event, such as sales transactions,
inventory levels, or customer orders.
Official Business
Fact Tables
Characteristics:
• Store transactional or measurable data, such as sales amount, quantity sold,
or revenue.
• Often have foreign key relationships with dimension tables.
• Typically have a large number of rows compared to dimension tables.
Example:
• A fact table in a retail data warehouse might contain sales data, including
sales amount, date, product ID, and store ID.
Official Business
Dimension Tables
• A supporting table that gives descriptive information about the data
in the fact table. It comprises traits or features that aid in categorizing
or filtering the data in the fact table.
Official Business
Dimension Tables
Characteristics:
• Contain descriptive attributes that provide context to the facts, such as time,
product, customer, or location.
• Usually have a smaller number of rows compared to fact tables.
• Often connected to fact tables via foreign key relationships.
Example:
• In a retail data warehouse, dimension tables could include product
dimensions (e.g., product ID, product name, category) or time dimensions
(e.g., date, day of the week, month).
Official Business
Attributes
• The individual data elements or characteristics that describe
dimensions within a dimensional model.
Official Business
Attributes
Characteristics:
• Provide additional context or detail about dimension members.
• Can be hierarchical, allowing for drill-down analysis.
• Play a crucial role in filtering, grouping, and aggregating data.
Example:
• Attributes of a customer dimension might include customer ID, name,
address, age, gender, and income level.
Official Business
Fact Tables vs Dimension Tables
Basis Fact Table Dimension Table
Contents Numeric values and Categorical data and
transactional data. descriptive attributes.
Purpose Stores quantitative Provides descriptive
measures and metrics. attributes and context.
Size Larger in terms of data Smaller in terms of data
volume. volume.
Aggregation Aggregates data for analysis Provides context for data
and reporting. aggregation.
Querying Provides data for analysis Used for filtering and
and calculations. categorization
Examples Sales transactions and Date, product, store, and
inventory levels. customer dimensions
Rows Many rows. Fewer rows.
Official Business
Types of Fact Tables
Transactional Fact Table
• Stores information about specific events or transactions.
• Utilized to save granular, atomic-level data like sales transactions, and
purchase orders.
Official Business
Official Business
Types of Fact Tables
Periodic Snapshot Fact Table
• Collects aggregated data at regular intervals (e.g., daily, weekly, and monthly).
• Saves summarized information regarding a specific time period, such as total
monthly sales or weekly purchase orders.
• When compared to Transactional Fact Tables, it feature fewer rows, but they
provide a greater degree of aggregation for analysis and reporting.
Official Business
Official Business
Types of Fact Tables
Accumulating Snapshot Fact Table
• Captures the state or progress of a process or workflow.
• Documents significant milestones or events that occur during the lifespan of a
business process, such as order processing or project management.
• Allows the recording and analysis of Key Performance Indicators (KPIs) at
various phases of a process.
Official Business
Official Business
Types of Fact Tables
Factless Fact Table
• Only includes foreign keys that correlate with Dimension Tables with no
measurements or metrics.
• Helpful when it is necessary to track non-occurring events or combinations of
occurrences, such as tracking consumers who did not make a purchase or
tracking product pairings that were not sold together.
• Aids in the analysis of data patterns, trends, and exceptions.
Official Business
Official Business
Types of Dimension Tables
Slowly Changing Dimension Table
• Used to track historical changes in dimensional properties across time.
• Record changes in qualities such as customer information, product features,
or geographic information.
• Three common types:
o Type 1: Replaces the old attribute value with the new value, with no history
preservation. – Overwrite (No history)
o Type 2: Generates a new row for each modification while maintaining past values via
effective date ranges or version numbers. - Add New Row (Full history)
o Type 3: Inserts columns to keep track of restricted historical changes, often storing the
current and past attribute values. - Add New Attribute (Limited history)
Official Business
Official Business
Types of Dimension Tables
Role-Playing Dimension Table
• A hybrid approach, where a single dimension table is used for multiple facts
with different roles or perspectives.
• A “Date” Dimension Table, for example, may be used to analyze both order
dates and shipment dates.
Official Business
Official Business
Types of Dimension Tables
Hierarchy Dimension Table
• Provides the hierarchical connection between dimension characteristics.
• A “Product” dimension, for example, might include a hierarchy with levels
such as “Category,” “Sub-category,” and “Product”.
• Allows for data drill down or roll up at various degrees of granularity
Official Business
Official Business
Types of Dimension Tables
Junk Dimension Table
• Used to combine low-cardinality flags or indications into a single Dimension
Table.
• It aids in lowering the number of columns in the Fact Table, simplifying the
schema design, and boosting query efficiency.
Official Business
Official Business
Types of Dimension Tables
Conformed Dimension Table
• Shared across numerous Fact Tables in a data warehouse.
• Maintains uniformity and facilitate the integration of data from many sources
or topic areas.
• For example, a “Customer” dimension may be utilized in both sales and
marketing data tables.
Official Business
Official Business
Normalization &
Denormalization
Official Business
Normalization
• A method used in a database to reduce the data redundancy and data
inconsistency from the table.
Official Business
Normal Forms
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF)
Official Business
First Normal Form (1NF)
• A fundamental concept in database normalization, a process used to
organize and structure data in relational databases efficiently.
• Establishes the basic rules for a well-formed relational database
schema, ensuring that each table contains atomic values and avoids
repeating groups.
Official Business
Student ID Student Name Courses
Math, Physics,
101 John
Chemistry
English, History,
102 Alice
Geography, Math
Student ID Student Name Course
101 John Math
101 John Physics
101 John Chemistry
102 Alice English
102 Alice History
102 Alice Geography
102 Alice Math
Official Business
Second Normal Form (2NF)
• Further step in the process of database normalization, building upon
the principles of First Normal Form (1NF).
• While 1NF ensures that each column contains atomic values and
eliminates repeating groups, 2NF addresses the issue of partial
dependencies within a table.
Official Business
Product
OrderID ProductID Product Name Category
1001 1 Laptop Electronics
1001 2 Mouse Accessories
1002 1 Laptop Electronics
1002 3 Keyboard Accessories
Sales Table Products Table
OrderID ProductID ProductID Product Name Product Category
1001 1 1 Laptop Electronics
2 Mouse Accessories
1001 2
3 Keyboard Accessories
1002 1
1002 3
Official Business
Third Normal Form (3NF)
• A further step in the process of database normalization, building upon
the principles of First Normal Form (1NF) and Second Normal Form
(2NF).
• While 1NF ensures that each column contains atomic values and
eliminates repeating groups, and 2NF addresses partial dependencies,
3NF focuses on eliminating transitive dependencies within a table.
Official Business
Student ID Student Name Course Instructor
101 John Math Mr. Smith
102 Alice English Ms. Johnson
103 Bob Math Mr. Smith
Students Table Courses Table
Student ID Student Name Course Instructor
101 John Math Mr. Smith
102 Alice English Ms. Johnson
103 Bob
Enrollment Table
Student ID Course
101 Math
102 English
103 Math
Official Business
Boyce-Codd Normal Form (BCNF)
• A stricter form of normalization compared to Third Normal Form
(3NF).
• It addresses certain anomalies that may still exist in tables even after
they have been normalized to 3NF.
• Named after Ronald F. Boyce and Raymond F. Boyce, who first
proposed the concept, and Edgar F. Codd, who introduced the
concept of normalization.
Official Business
Employee ID Employee Name Department Manager ID Manager Name
101 John Engineering 201 Alice
102 Alice Marketing 202 Bob
103 Bob Engineering 201 Alice
Employees Table Managers Table
Employee ID Employee Name Department Manager ID Manager Name
101 John Engineering 201 Alice
102 Alice Marketing 202 Bob
103 Bob Engineering
Official Business
Denormalization
• The process of intentionally adding redundancy to a database design
to improve performance or simplify queries.
• It involves reintroducing redundancy into a normalized database
schema by adding redundant data or by grouping data together in a
way that departs from the principles of normalization.
Official Business
Types of Denormalization
• Horizontal Denormalization
• Vertical Denormalization
Official Business
Horizontal Denormalization
• A database optimization technique where a single table is split into
multiple tables based on certain criteria, such as time periods,
geographical regions, or other relevant factors.
• Aims to improve query performance and manageability by
distributing data across multiple smaller tables rather than storing all
data in a single large table.
Official Business
Store ID Region Date Product ID Quantity Amount
1001 North 2024-01-01 101 5 250.00
1002 South 2024-01-01 102 3 150.00
1003 East 2024-01-02 103 2 100.00
1004 West 2024-01-02 101 4 200.00
Store ID Date Product ID Quantity Amount
SalesData_North
1001 2024-01-01 101 5 250.00
Store ID Date Product ID Quantity Amount
SalesData_South
1002 2024-01-01 102 3 150.00
Store ID Date Product ID Quantity Amount
SalesData_East
1003 2024-01-02 103 2 100.00
Store ID Date Product ID Quantity Amount
SalesData_West
1004 2024-01-02 101 4 200.00
Official Business
Vertical Denormalization
• A database optimization technique where related columns from
multiple tables are combined into a single table.
• Unlike horizontal denormalization, which involves splitting a single
table into multiple tables, vertical denormalization consolidates data
from multiple tables into a single table.
Official Business
Employee ID Name Age Department ID Name
1001 John 30 2001 Engineering
1002 Alice 25 2002 Marketing
Employee ID Name Age Department ID Name
1001 John 30 2001 Engineering
1002 Alice 25 2002 Marketing
Official Business
Normalization vs Denormalization
Aspect Normalization Denormalization
Improve query performance, sacrificing some
Objective Minimize redundancy and ensure data integrity.
data integrity for efficiency.
Combine tables, introduce redundancy, and
Decompose tables into multiple related tables
Process store data redundantly to optimize
based on normalization rules.
performance.
Enhances data integrity by minimizing May compromise data integrity due to
Data Integrity
redundancy and preventing anomalies. redundancy, requiring careful management.
More joins, impacting query performance, Faster query performance by reducing the need
Query Performance
especially for complex queries. for joins.
May use more storage due to redundancy but
Storage Efficiency Uses storage efficiently by avoiding redundancy.
can improve query performance.
Reduces the risk of update anomalies by May lead to update anomalies if redundancy is
Update Anomalies
maintaining data in a normalized form. not carefully managed.
Provides greater flexibility in data Offers less flexibility in data representation and
Flexibility
representation and modification. modification.
Introduces complexity in database design due Reduces complexity in database design but may
Complexity
to multiple tables and normalization rules. introduce complexity in managing redundancy.
Requires ongoing maintenance to ensure Requires maintenance to manage redundant
Maintenance
normalization and schema changes. data and ensure consistency.
Well-suited for transactional systems and OLTP Commonly used in reporting and analytics
Usage
environments. systems and OLAP environments.
Official Business
Key Takeaways
• Dimensional modeling simplifies data for business analysis
• Fact tables = numeric, measurable events
• Dimension tables = context (who, what, when, where)
• Slowly Changing Dimension handles changes in dimension attributes
• Normalization = data integrity, Denormalization = performance
Official Business