Review of Major Points

1
Review of Major Points

• Star schema
• Keys
• Fact Factless
• Additive, semi-additive, non-additive
• Degenerate dimensions
• Snowflaking
• Outriggers
• Extensibility
• Design process

• Slowly changing dimensions
• Type 1, 2, 3, hybrids
• Data warehouse architecture
• Bus
• Matrix
• Conformed dimensions
• Conformed facts
• Fact table types
• periodic snapshot
• accumulating snapshot
• transaction

2
Ch 5 Ideas
Fact table normalization Role playing Junk
dimension Principles for handling Units of
measure, Currency Header fact allocations to line
items Fact table types
3
Fact table normalization
Consider
(FKs) Order qty Gross dollar amount Order deal
discount dollar amount Net order dollar amount
See figure 5.2
These facts will be densely populated in the fact
table (if not 100)
4
Fact table normalization
Consider
(FKs) Fact type (FK) measure

• The concept of normalizing the fact table means
  that we
• reduce the fact table to one measure and
• add a new dimension for fact type (qty, gross,
  discount, net)
• No motivation for normalizing this schema
  (because the facts are densely populated

5
Role playing

• A schema exhibits role-playing if a single
  dimension appears more than once in the schema
• There is one underlying or base table
• Each role is seen as a view

Date dimension found in every schema Consider a
Northwind Dimensional Model with 3 date
dimensions
6
Role playing
Shipper
Customer
Order facts
Quantity Discount amount Gross amount Net amount
Employee
Product
Supplier
Ordered Date
Requested Date
Shipped Date
7
Junk dimension
If, after assigning attributes to fact and
dimension tables, there are a number of
miscellaneous attributes left over, then if these
attributes are lumped into one dimension the
dimension is called a junk dimension e.g.
(similar to figure 5.5
Key
Payment type
Payment type group
Commission indicator

1
cash
cash
commissionable

2
cash
cash
Non-commissionable

3
Discover card
credit
commissionable

4
Discover card
credit
Non-commissionable

5
Mastercard
credit
commissionable






8
Junk dimension
A fact references the appropriate combination
Key
Payment type
Payment type group
Commission indicator

1
cash
cash
commissionable

2
cash
cash
Non-commissionable

3
Discover card
credit
commissionable

4
Discover card
credit
Non-commissionable

5
Mastercard
credit
commissionable






9
Currencies
A global company may handle several
currencies Recommendation Store all facts in a
local and standardized currency this simplifies
any analysis that end-users perform
Order facts
Quantity Discount amount local Discount amount
Cdn Gross amount local Gross amount Cdn Net
amount local Net amount Cdn Local currency
Order facts
Quantity Discount amount Gross amount Net amount
10
Currencies
To allow conversions (easily) to any currency,
utilize a currency conversion fact table
Order facts
Quantity Discount amount local Discount amount
Cdn Gross amount local Gross amount Cdn Net
amount local Net amount Cdn Local currency key
Currency conversion facts
Date key Source currency key Destination currency
key Source/Destination exch rate Destination/Sourc
e exch rate
11
Currencies
What SQL would report the total gross amount in
US dollars?
Order facts
Quantity Discount amount local Discount amount
Cdn Gross amount local Gross amount Cdn Net
amount local Net amount Cdn Local currency key
Currency conversion facts
Date key Source currency key Destination currency
key Source/Destination exch rate Destination/Sourc
e exch rate
12
Allocating facts to a lower granularity fact table
e.g. suppose the Order has a shipping charge.
Where should that be stored? If we are creating
an order line fact table, we could try to
allocate the shipping charge to each line item.
The allocation formula may not be easily
determined. Without an allocation formula,
analysts cannot explore the relationship between
products and shipping charges
Order facts
Quantity Discount amount Gross amount Net
amount Shipping amount
Order shipping charges captured/known at the
level of the order
formula
13
Assignment 2 Due Friday Feb 13, 2004

• Use DTS to perform an initial load of a Star
  Schema for a Northwind Dimensional database
• Some details
• Create your own copy of Northwind to use as
  source data
• Use surrogate keys for each dimension
• PK of fact table is catenation of all FKs and DDs
• Sample stored procedure for populating the Date
  dimension
• Initial load of fact table may assume only one
  record in a dimension per natural key
• Use a view to facilitate loading the fact table
  with its FKs and facts

14
Assignment 2
Order (DD)
Shipper
Customer
Order facts
Quantity Discount amount Gross amount Net amount
Employee
Product
Supplier
Ordered Date
Requested Date
Shipped Date
15
Assignment 3
Design a data mart for the U of W Reference
DATA MODELS FOR A REGISTRARS DATA MART
Georgetown University More details to follow