emirareach.com

Get Started

 

Table of Contents

  1. What is a Logical Data Model in Data Warehousing?
  2. Core Components of a Logical Data Model
  3. Importance of Logical Data Models in Data Warehousing
  4. Example: Northwind Data Warehouse Logical Model
  5. Logical Model vs Physical Model
  6. Dimensional Modeling and Star Schema
  7. Conclusion

What is a Logical Data Model in Data Warehousing?

A Logical Data Model in Data Warehousing is a blueprint that defines how business data will be organized, related, and analyzed. Unlike the physical model, which deals with actual storage, tables, and indexes, the logical model focuses on business meaning, entities, and relationships.

It bridges the gap between business users who want insights and IT teams who design the system.

At its core, it defines:

  • Facts (measures) → What events we measure (e.g., Sales, Profit, Quantity).
  • Dimensions (context) → Who, what, when, where the events occur (e.g., Customer, Product, Time, Geography).
Logical Data Model in Data Warehousing Example

Core Components of a Logical Data Model

1. Facts

Facts represent measurable business activities.
Examples include:

  • Sales Amount
  • Quantity Sold
  • Discount Applied
  • Net Revenue

2. Dimensions

Dimensions describe the facts by adding context.
Examples include:

  • Time Dimension (Year, Quarter, Month, Day)
  • Product Dimension (Category, Brand, Price)
  • Customer Dimension (Name, Age, Location)
  • Employee Dimension (Role, Supervisor, Region)

3. Hierarchies

Dimensions often contain hierarchies that allow drill-down analysis.
For example:

  • Time → Year → Quarter → Month → Day
  • Geography → Country → State → City

4. Relationships

The logical model shows how facts are related to dimensions. Typically, fact tables connect to multiple dimension tables using primary and foreign keys.

This forms well-known structures such as Star Schema or Snowflake Schema.

 

Importance of Logical Data Models in Data Warehousing

Designing a proper Logical Data Model in Data Warehousing ensures:

  • Business–IT alignment → Translates business requirements into structured design.
  • Consistency → Defines a single version of truth for analytics.
  • Scalability → Allows easy addition of new dimensions or facts.
  • Efficient analysis → Supports OLAP, dashboards, and KPIs.

Without a solid logical model, the warehouse risks duplication, inefficiency, and incorrect reporting.

Example: Northwind Data Warehouse Logical Model

A famous case study is the Northwind DW Logical Model.

  • Fact Table: Sales
    • Measures: Quantity, SalesAmount, Discount, NetAmount.
  • Dimensions:
    • Time (Year, Quarter, Month, Day).
    • Customer (CompanyName, Address, Region).
    • Product (ProductName, Category, UnitPrice).
    • Employee (Supervisor, Subordinate).
    • Supplier and Shipper.
    • Geography (City, State, Country, Region).
  • For More details

This structure answers questions like:

  • “What is total sales by product category and region?”
  • “Which employee generated the highest revenue last quarter?”
  • “How do discounts impact sales across countries?”

Logical Model vs Physical Model

Feature Logical Model Physical Model
Focus Business meaning Database storage
Elements Facts, Dimensions, Hierarchies Tables, Indexes, Partitions
Audience Business Analysts, Data Architects Database Admins
Flexibility High Low
Example “Sales linked to Customers and Products” “Sales table with CustomerID and ProductID keys”

Dimensional Modeling and Star Schema

The Logical Data Model in Data Warehousing is often implemented using Dimensional Modeling.

Star Schema

  • A central fact table linked to multiple dimension tables.
  • Easy for analysts and BI tools.

Snowflake Schema

  • Dimensions are normalized into multiple related tables.
  • Saves space but adds complexity.

Star-Flake Schema

  • A hybrid of the two.

For further reading, check out Kimball Group’s resources on dimensional modeling.

Conclusion

A Logical Data Model in Data Warehousing is the foundation of effective analytics. By defining facts, dimensions, hierarchies, and relationships, it ensures that business questions can be answered quickly and accurately.

Whether you are analyzing customer behavior, sales performance, or product profitability, the logical model guarantees that your warehouse delivers reliable, scalable, and actionable insights.

Next, explore our guide on Star Schema vs Snowflake Schema for a deeper dive.

For More details