Data Warehouse Design:
Dimensional Modeling II
Data Technology
Chularat Tanprasert, Ph.D.

Recap


Dimensional modeling







Popular, useful, and pragmatic approach
Based on Kimball

Fact table
Dimension tables
Design process in steps

Database Schema Design

Star Schema (With Attributes)

Example Designs






A useful way to learn about data warehouse design principles is by using examples – reuse.
Kimball – Data warehouse lifecycle toolkit
Adamson & Venerable – Data warehouse design solutions
Let’s take a look at inventory, shipments, and financial services.

Inventory




An inventory system serves as a
“middleman” between the manufacturer and the retailer – value adding process.
There are threee types of inventory model




Inventory snapshot
Delivery status
Transaction

Inventory Snapshot Model
For specific time periods, inventory levels are measured and recorded.

Delivery Status Model
Create one record for each complete shipment of a product to a warehouse.

Transaction Model
Record every transaction that affects the inventory.

Shipments





The shipments process is where the product leaves a company and is delivered to a customer.
Typically, accompanying each shipment is a shipment invoice.
Each line item on the shipment invoice corresponds to an SKU.

Shipments

Shipments

Shipments

Financial Services



Typically a large bank.
Services include





Cheque accounts, saving accounts, mortgage loans, investment loans, personal loans, credit cards, etc.

Goal – to market more effectively to households.
Build a household data warehouse to track accounts, account holders, and their household grouping. Financial Services








Five years of monthly data for each account.
For current month must