Design a Data Model Example - Data Warehousing

Estimated Reading Time: < 5 minutes

Problem Statement

Fictitious company A, a car rental business, wants to create a dimensional data warehouse to analyze branch performance. The business processes to be covered include signed contracts, returned cars, and picked up cars per branch, car, customer, and region.

Solution

1. Dimensions:
• Branch: BranchID (Primary Key), Name, Location, Region
• Car: CarID (Primary Key), Make, Model, Year
• Customer: CustomerID (Primary Key), FirstName, LastName, Email, Phone, Region
• Region: RegionID (Primary Key), Name
2. Facts:
• Signed Contracts Fact: FactID, BranchID (Foreign Key), CarID (Foreign Key), CustomerID (Foreign Key), SignDate, TotalCost
• Returned Cars Fact: ReturnID, BranchID (Foreign Key), CarID (Foreign Key), CustomerID (Foreign Key), ReturnDate
• Picked Up Cars Fact: PickupID, BranchID (Foreign Key), CarID (Foreign Key), CustomerID (Foreign Key), PickupDate

Logical Data Model of the Resulting Data Marts

Technical Design Document

Full and Incremental Load Process

The Full Load process involves populating the data warehouse with initial data from OLTP systems. This is done once at the start of the data warehouse's lifecycle or after a significant data model change.

The Incremental Load process updates the data warehouse daily, only adding new records since the last load. This ensures that the data warehouse remains current and relevant to the business needs.

Design a Data Model Example for Data Warehousing

Introduction

In this article, we will walk through an example of designing a data model for a data warehouse. The example is based on a hypothetical e-commerce company named "ShopMart." This data model will focus on the Sales and Inventory domains.

Entities

Sales Entity

• SaleID: A unique identifier for each sale transaction.
• CustomerID: The customer who made the purchase, referencing the Customer table.
• OrderDate: The date and time the order was placed.
• TotalAmount: The total amount of the order (including taxes, shipping costs, etc.).

Inventory Entity

• ProductID: A unique identifier for each product.
• SupplierID: The supplier who provides the product, referencing the Supplier table.
• ProductName: The name of the product.
• Quantity: The current quantity available in stock.

Relationships

One-to-Many Relationships

• One: Sales Entity (SaleID)
• Many: Customer Entity

Each sale transaction is associated with one customer.

Many-to-One Relationships

• Many: Sales Entity (ProductID)
• One: Inventory Entity (ProductID)

Each sale transaction can involve multiple inventory items of the same product, and there is only one inventory item per product.

Normalization

Applying normalization principles to this data model ensures that each table only stores atomic values, minimizing redundancy and improving data consistency. For example, instead of storing the customer's name in the Sales table, we store the CustomerID and reference the corresponding customer details in a separate table.

Conclusion

This article provided an example data model for a data warehouse focusing on the Sales and Inventory domains of ShopMart. Designing a well-structured data model is essential for efficient query performance, data consistency, and ease of analysis. Adapting this example to your specific use case can help you create effective data warehousing solutions.