Basic Model: Retail Grocery Store

1
(No Transcript)
2
Basic Model Retail Grocery Store

• Process Retail Sales
• Grain POS line item
• Dimensions Date, Store, Product, Promotion
• Facts Sales Quantity, Sales Dollar Amount, Cost
  Dollar Amount, Gross Profit Dollar Amount.

3
Model
4
Possible Date Attributes

• SQL date
• Full date description
• Day of week
• Day of month
• Day of calendar year
• Day of fiscal year
• Month of calendar year
• Month of fiscal year
• Calendar Quarter
• Fiscal Quarter

• Fiscal week
• Year
• Month
• Fiscal year
• Holiday ?
• Holiday name
• Day of holiday
• Weekday ?
• Selling season
• Major event
• etc.

5
Possible Product Attributes

• Description
• SKU number
• Brand description
• Department
• Package type
• Package size
• Fat content
• Diet type
• Weight

• Weight units of measure
• Storage type
• Shelf unit type
• Shelf width
• Shelf height
• Shelf depth
• etc.

6
Possible Store Attributes

• Store Name
• Store Number
• Street address
• City
• County
• State
• Zip
• Manager
• District

• Region
• Floor plan type
• Photo processing type
• Financial service type
• Square footage
• Selling square footage
• First open date
• Last remodel date
• etc.

7
Possible Promotion Attributes

• Promotion name
• Price reduction type
• Promotion media
• Ad type
• Ad agency
• Display type
• Coupon type
• Promotion cost

• Begin date
• End date
• etc.

8
Factless Fact Tables

• In order to evaluate promotions that might have
  generated no sales we need another approach.
• Promotion could generate another fact table (or
  could be considered a fact table in itself).
  That new fact table would have no additive
  attributes.

9
Conformed Dimensions Inventory Snapshot Model

• Process Store inventory
• Grain Daily inventory by product and store
• Dimensions Date, product, store
• Fact quantity-on-hand

10
Dimensional Model
Note QuantityOnHand is semi-additive. It is
additive across product and store, but not
across date. The other attributes are additive.
11
Conformed Dimensions

• Common dimensions for different processes should
  be the same.
• Note Dimensions for roll-up or aggregated fact
  tables my add or eliminate attributes based on
  the aggregation Where attributes apply, they
  should mean the same thing.

12
The Bus Matrix
Process Date Product Store Promotion Warehouse Vendor Contract Shipper
Retail Sales X X X X
Retail Inventory X X X
Retail Deliveries X X X
Warehouse Inventory X X X X
Warehouse Deliveries X X X X
Purchase Orders X X X X X X
13
Slowly Changing Dimensions

• Attributes in a dimensional table that change
  over time. Three approaches
• Type 1 overwrite the old value
• Type 2 create a new dimensional record
• Type 3 create a previous value attribute

14
Examples
Original
ProductKey Description Category SKU
21553 LeapPad Education LP2105
Type 1
ProductKey Description Category SKU
21553 LeapPad Toy LP2105
Type 2
ProductKey Description Category SKU
21553 LeapPad Education LP2105
44631 LeapPad Toy LP2105
Type 3
ProductKey Description Category OldCat SKU
21553 LeapPad Toy Education LP2105
Hybrid
ProductKey Description Category OldCat SKU
21335 LeapPad Electronics Education LP2105
44631 LeapPad Electronics Toy LP2105
68122 LeapPad Education Electronics LP2105
15
More about dimensions

• Views for dimensions used for different purposes
• e.g. StartDate and EndDate
• Junk dimensions for flags and miscellaneous
  categories removed from the fact table
• Degenerate dimensions have no attributes
• Usually reserved for order number or something
  similar

16
Accumulation Snapshot

• State change summary that has one row per item.
• Access rows on each update.

17
Multiple Granularities

• Product sales vs. Annual quota
• Line item cost vs. Order shipping cost