Module 2
Designing and Implementing a
Data Warehouse
Module Overview
• Data Warehouse Design Overview
• Designing Dimension Tables
• Designing Fact Tables
Lesson 1: Data Warehouse Design Overview
• The Dimensional Model
• The Data Warehouse Design Process
• Dimensional Modeling
• Documenting Dimensional Models
The Dimensional Model
Dimension
Attributes
Dimension
Attributes
Dimension
Attributes
Fact
Measures
Star schema
Dimension
Attributes
Snowflake schema
Dimension
Attributes
Dimension
Attributes
The Data Warehouse Design Process
1. Determine analytical and reporting requirements
Identify the business processes that generate the
2.
required data
3. Examine the source data for those business
processes
4. Conform dimensions across business processes
5. Prioritize processes and create a dimensional
model for each
6. Document and refine the models to determine
the database logical schema
7. Design the physical data structures for the
database
Dimensional Modeling
Conformed Dimensions
e
n
i
L
y
r
o
t
c
a
F
x
n
o
s
r
e
p
s
e
l
a
S
x
r
e
m
o
t
s
u
C
x
x
r
e
p
p
h
S
i
x
t
n
e
m
t
r
a
p
e
D
t
n
u
o
c
c
A
x
x
e
s
u
o
h
e
r
a
W
x
t
c
u
d
o
r
P
x
x
x
e
m
T
i
x
x
x
x
x
Business Processes
Manufacturing
Order Processing
Publicité
Order Fulfilment
Financial Accounting
Inventory Management
• Grain: 1 row per order item
• Dimensions: Time (order date and ship date), Product, Customer, Salesperson
•
Facts: Item Quantity, Unit Cost, Total Cost, Unit Price, Sales Amount, Shipping Cost
Documenting Dimensional Models
Time
(Order Date and
Ship Date)
Calendar Year
Month
Date
Fiscal Year
Fiscal Quarter
Month
Date
Category
Subcategory
Product Name
Color
Size
Product
Sales Order
Item Quantity
Unit Cost
Total Cost
Unit Price
Sales Amount
Shipping Cost
Salesperson
Region
Country
Territory
Manager
Name
Name
Country
State or Province
City
Age
Marital Status
Gender
Customer
Lesson 2: Designing Dimension Tables
• Considerations for Dimension Keys
• Dimension Attributes and Hierarchies
• Unknown and None
• Designing Slowly Changing Dimensions
• Time Dimension Tables
• Self-Referencing Dimension Tables
• Junk Dimensions
Considerations for Dimension Keys
CustomerKey
CustomerAltKey
Name
1
2
1002
1005
Amy Alberts
Neil Black
Surrogate Key
Business (Alternate) Key
ProductKey
ProductAltKey
ProductName
1
2
MB1-B-32
MB1-R-32
MB1 Mountain Bike
MB1 Mountain Bike
Color
Size
Blue
Red
32
32
Dimension Attributes and Hierarchies
CustKey
CustAltKey
Name
Country
State
City
Phone
Gender
1
2
3
1002
1005
1006
Amy Alberts
Canada
Neil Black
Ye Xu
USA
USA
BC
CA
NY
Vancouver
555 123
Irvine
555 321
New York
555 222
F
M
M
Hierarchy
Drill-through detail
Slicer
Unknown and None
• Identify the semantic meaning of NULL
• Unknown or None?
• Do not assume NULL equality
• Use ISNULL( )
OrderNo
Discount
DiscountType
1000
1001
1002
1003
1004
1005
1006
Bulk Discount
N/A
Promotion
Other
N/A
1.20
0.00
Publicité
2.00
0.50
2.50
0.00
1.50
Source
Dimension Table
DiscKey
DiscAltKey
DiscountType
-1
0
1
2
3
Unknown
Unknown
N/A
None
Bulk Discount
Bulk Discount
Promotion
Promotion
Other
Other
Designing Slowly Changing Dimensions
CustKey
CustAltKey
Name
Phone
1
1002
Amy Alberts
555 123
Type 1
CustKey
CustAltKey
Name
Phone
1
1002
Amy Alberts
555 222
CustKey
CustAltKey
Name
City
Current
Start
End
1
1002
Amy Alberts
Vancouver
Yes
1/1/2000
Type 2
CustKey
CustAltKey
Name
City
Current
Start
End
1
4
1002
1002
Amy Alberts
Vancouver
Amy Alberts
Toronto
No
Yes
1/1/2000
1/1/2012
1/1/2012
CustKey
CustAltKey
Name
Cars
1
1002
Amy Alberts
0
Type 3
CustKey
CustAltKey
Name
Prior Cars
Current Cars
1
1002
Amy Alberts
0
1
Time Dimension Tables
DateKey
DateAltKey
MonthDay
WeekDay
Day MonthNo
Month
Year
00000000
01-01-1753
NULL
NULL
NULL NULL
NULL
NULL
20130101
01-01-2013
20130102
01-02-2013
20130103
01-03-2013
20130104
01-04-2013
1
2
3
4
3
4
5
6
Tue
Wed
Thu
Fri
01
01
01
01
Jan
Jan
Jan
Publicité
Jan
2013
2013
2013
2013
• Surrogate key
• Granularity
• Range
• Attributes and hierarchies
• Multiple calendars
• Unknown values
Self-Referencing Dimension Tables
EmployeeKey
EmployeeAltKey
EmployeeName
ManagerKey
1
2
3
4
1000
1001
1002
1003
Kim Abercrombie
NULL
Kamil Amireh
Cesar Garcia
Jeff Hay
1
1
2
• Kim Abercrombie
• Kamil Amireh
•
Jeff Hay
• Cesar Garcia
Junk Dimensions
JunkKey
OutOfStockFlag
FreeShippingFlag
CreditOrDebit
1
2
3
4
5
6
7
8
1
1
1
1
0
0
0
0
1
1
0
0
1
1
0
0
Credit
Debit
Credit
Debit
Credit
Debit
Credit
Debit
• Combine low-cardinality attributes that don’t
belong in existing dimensions into a junk
dimension
• Avoids creating many small dimension tables
Lesson 3: Designing Fact Tables
• Fact Table Columns
• Types of Measure
• Types of Fact Table
Fact Table Columns
• Dimension Keys
OrderDateKey
ProductKey
CustomerKey
OrderNo
Qty
SalesAmount
20120101
20120101
20120101
25
99
25
• Measures
120
120
178
1000
1000
1001
1
2
2
350.99
6.98
701.98
OrderDateKey
ProductKey
CustomerKey
OrderNo
Qty
SalesAmount
20120101
20120101
20120101
25
99
25
120
120
178
1000
1000
1001
1
2
2
350.99
6.98
701.98
• Degenerate Dimensions
OrderDateKey
ProductKey
CustomerKey
OrderNo
Publicité
Qty
SalesAmount
20120101
20120101
20120101
25
99
25
120
120
178
1000
1000
1001
1
2
2
350.99
6.98
701.98
Types of Measure
• Additive
OrderDateKey
ProductKey
CustomerKey
SalesAmount
20120101
20120101
20120102
25
99
25
• Semi-Additive
120
120
178
350.99
6.98
701.98
DateKey
20120101
20120101
20120102
ProductKey
StockCount
25
99
25
23
118
22
• Non-Additive
OrderDateKey
ProductKey
CustomerKey
ProfitMargin
20120101
20120101
20120102
25
99
25
120
120
178
25
22
27
Types of Fact Table
• Transaction Fact Tables
OrderDateKey
ProductKey
CustomerKey
OrderNo
Qty
Cost
SalesAmount
20120101
20120101
20120101
25
99
25
120
120
178
1000
1000
1001
1
2
2
125.00
350.99
2.50
6.98
250.00
701.98
• Periodic Snapshot Fact Tables
DateKey
ProductKey
OpeningStock
UnitsIn
UnitsOut
ClosingStock
20120101
20120101
25
99
25
120
1
0
3
2
23
118
• Accumulating Snapshot Fact Tables
OrderNo
OrderDateKey
ShipDateKey
DeliveryDateKey
1000
1001
1002
20120101
20120101
20120102
20120102
20120105
20120102
00000000
00000000
00000000