Week 3 DW Design Kimball

Download
Share  Add to  Flag Embed
Uploaded from authorPOINTLite
Views:
 
Category: Entertainment
     
 
License:   All Rights Reserved

Presentation Description

No description available.

Comments

 

By: mybooma (19 month(s) ago)

could you please share with me your presentation? Many Thanks

 

By: amitsroy (42 month(s) ago)

Kimball Please can you send me the PPT at amit.s.roy@gmail.com

Presentation Transcript

615-644 Data Warehousing: 

615-644 Data Warehousing Week 3: Data Warehouse Design: The Kimball approach

Designing a Dimensional Model: 

Designing a Dimensional Model Choose a Business Process Choose the grain of the fact table Choose the dimensions Choose the measured facts (usually numeric, additive quantities) Complete the dimension tables (Kimball, 1996)

Data Warehouse Design: 

Data Warehouse Design A useful way to learn dimensional modelling is using examples from Kimball (1996) Retail trading Inventory Shipments Financial services Insurance

Retail Trading: 

Retail Trading A large grocery store with approx. 500 stores Each store has approx. 60,000 products on shelves SKU - stock keeping unit UPC - universal product code

Retail Trading: 

Retail Trading need to maximise profit and keep shelves stocked important decisions concern pricing and promotion types of promotion are temporary price reductions newspaper advertisements shelf and end-aisle displays coupons

Retail Trading: 

Retail Trading Choose a business process Daily item movement Choose the grain of the fact table SKU by store by promotion by day

Retail Trading: 

Retail Trading Choose the dimensions Time key Product key Store key Promotion key FACTS TBD Time key TIME ATTRIBUTES TBD Sales Fact Time Promotion key PROMOTION ATTRIBUTES TBD Promotion Product key PRODUCT ATTRIBUTES TBD Product Store key STORE ATTRIBUTES TBD Store from Kimball (1996), p27

Retail Trading: 

Retail Trading Choose the measured facts Time key Product key Store key Promotion key Time key TIME ATTRIBUTES TBD Sales Fact Time Promotion key PROMOTION ATTRIBUTES TBD Promotion Product key PRODUCT ATTRIBUTES TBD Product Store key STORE ATTRIBUTES TBD Store Unit sales Dollar costs Customer count Dollar sales from Kimball (1996), p29

Retail Trading: 

Retail Trading Complete the dimension tables Time key Product key Store key Promotion key Sales Fact Time Unit sales Dollar costs Customer count Dollar sales Time key day of week day no in month day no overall week no overall month month no overall week no in year quarter holiday flag weekday flag last day month flag fiscal period season event Product key Product Store key Store Promotion key Promotion from Kimball (1996), p34

Retail Trading: 

Retail Trading Time key Product key Store key Promotion key Sales Fact Product Unit sales Dollar costs Customer count Dollar sales Product key SKU description SKU number package size subcategory category department brand package type weight weight unit of meas units per retail case diet type units per ship case cases per pallet Time key Time Store key Store Promotion key Promotion from Kimball (1996), p38

Retail Trading: 

Retail Trading Time key Product key Store key Promotion key Sales Fact Store Unit sales Dollar costs Customer count Dollar sales Store key store name store number store street address store district store region store manager store postcode store telephone first opened date last opened date store sqft floor plan type grocery sqft frozen sqft Time key Time Product key Product Promotion key Promotion from Kimball (1996), p40

Retail Trading: 

Retail Trading Time key Product key Store key Promotion key Sales Fact Promotion Unit sales Dollar costs Customer count Dollar sales Promotion key promotion name price reduction type ad type coupon type ad media name display provider display type promo cost promo end date promo begin date Time key Time Product key Product Store key Store from Kimball (1996), p43

Inventory: 

Inventory An inventory system serves as a “middleman” between the manufacturer and the retailer There are 3 types of inventory model inventory snapshot delivery status transaction

Inventory Snapshot Model: 

Inventory Snapshot Model For specific time periods, inventory levels are measured and recorded Time key Product key Warehouse key Quantity on hand Inventory Fact Time key Time Product key Product Warehouse key Warehouse from Kimball (1996), p52

Delivery Status Model: 

Delivery Status Model Create one record for each complete shipment of a product to a warehouse Original order date Product key Warehouse key Vendor key Inventory Fact Time key Time Warehouse key Warehouse PO number PO line number First received date Product key Product Vendor key Vendor Last received date Qty received Qty inspected Qty picked Qty boxed from Kimball (1996), p56

Inventory Transaction Model: 

Inventory Transaction Model Record every transaction that affects the inventory Original order date Product key Warehouse key Transaction key Inventory Fact Time key Time Warehouse key Warehouse PO number Amount Product key Product Transaction key Transaction from Kimball (1996), p60

Shipments: 

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 Ship date Product key Cust ship to key Deal key Shipments Fact Time key Time Cust ship to key Cust ship to Ship from key Ship mode key Invoice number Product key Product Deal key Deal Invoice line Order data Expected ship date Arrival date Actual arrival date Quantity shipped Ext list price Ext allowances Ext discounts Ship from key Ship from Ship mode key Ship mode Deal cost from Kimball (1996), p67

Shipments: 

Shipments Ship date Product key Cust ship to key Deal key Shipments Fact Time key Time Cust ship to Ship from key Ship mode key Invoice number Product key Product Deal key Deal Invoice line Order data Expected ship date Arrival date Actual arrival date Quantity shipped Ext list price Ext allowances Ext discounts Ship from key Ship from Ship mode key Ship mode Deal cost Cust ship to key cust ship to name cust ship to address cust ship to city cust bill to name cust region name cust corp name cust ship to postcode sales team sales region sales district from Kimball (1996), p68

Shipments: 

Shipments Ship date Product key Cust ship to key Deal key Shipments Fact Time key Time Cust ship to Ship from key Ship mode key Invoice number Product key Product Deal Invoice line Order data Expected ship date Arrival date Actual arrival date Quantity shipped Ext list price Ext allowances Ext discounts Ship from key Ship from Ship mode key Ship mode Deal cost Cust ship to key Deal key deal description terms type terms description allowance desc spec incentive type spec incentive desc allowance type

Financial Services: 

Financial Services Large bank - services include cheque accounts, savings 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 groupings

Financial Services: 

Financial Services Requirements five years of monthly data for each account for current month must be snapshot as of previous day group and compare primary balances across accounts each account type has different attributes and numeric facts each account belongs to a household records of account holders names and addresses may differ from account to account interested in demographics and activity in each of the accounts

Financial Services: 

Financial Services Choose a business process Monthly account balances Choose the grain of the fact table Balance of each account by month

Financial Services: 

Financial Services Choose the dimensions Time key Branch key Account key Product key FACTS TBD Time key TIME ATTRIBUTES TBD Household Facts Time Account key ACCOUNT ATTRIBUTES TBD Account Household key HOUSEHOLD ATTRIBUTES TBD Household Branch key BRANCH ATTRIBUTES TBD Branch Product key PRODUCT ATTRIBUTES TBD Product Household key STATUS ATTRIBUTES TBD Status Household key Status key from Kimball (1996), p110

Financial Services: 

Financial Services Choose the measured facts Time key Branch key Account key Product key Primary balance Time key TIME ATTRIBUTES TBD Time Account key ACCOUNT ATTRIBUTES TBD Account Household key HOUSEHOLD ATTRIBUTES TBD Household Branch key BRANCH ATTRIBUTES TBD Branch Product key PRODUCT ATTRIBUTES TBD Product Household key STATUS ATTRIBUTES TBD Status Household key Status key Transaction count Household Facts from Kimball (1996), p110

Financial Services: 

Financial Services Complete the dimension tables Time key Branch key Account key Product key Primary balance Time key Time Account key Account Household key Household Branch key Branch Product key Product Status key Status Household key Status key Transaction count month year fiscal quarter name address date opened Household Facts branch name branch address branch type product desc type category status desc new acc flag closed acc flag head name address income from Kimball (1996), p110

Financial Services: 

Financial Services Heterogeneous Products Time key Branch key Account key Product key Primary balance Product Household key Status key Transaction count Household Facts Product key key product desc type category CD attributes cheque attributes ... savings attributes ... Credit card attribs ... CD facts ... Cheque facts ... Savings facts ... Credit card facts ... Safe deposit facts .. Safe deposit atts ... from Kimball (1996), p112

Financial Services: 

Financial Services Core and Custom Fact Tables Time key Branch key Account key Product key Primary balance Product Household key Status key Transaction count Core Facts Product key key product desc type category Time key Branch key Account key Product key Primary balance Product Household key Status key Transaction count Custom Savings Facts Product key key product desc type category savings attributes ... savings facts ... from Kimball (1996), p114

Insurance: 

Insurance A large property and casualty insurer for cars, home fire protection and personal liability Two main data sources policy formulation transactions claim processing transactions Goal - to analyse profitability of policies Build a household data warehouse to track the “lifetime” of policies

Insurance: 

Insurance Policy Creation There are several types of transaction in policy creation create/alter/cancel policy create/alter/cancel coverage on item rate/decline to rate coverage underwrite/ decline to underwrite policy The data warehouse fact table will track these transactions

Insurance - Policy Transaction: 

Insurance - Policy Transaction Transaction date Effective date Insured party key Employee key Transaction key Date key Time Employee key Employee Covd item key Covered Item Insured party key Insured Party Coverage key Coverage Policy key Policy Covered item key Policy key Amount day of week fiscal period name employee type department Policy Facts name address type description market segment line of business risk grade description type Transaction key description reason Transaction demographics ... Coverage key annual stmt line from Kimball (1996), p129

Insurance - Claims Transaction: 

Insurance - Claims Transaction Transaction date Effective date Insured party key Employee key Claimant key Date key Time Employee key Employee Covd item key Covered Item Insured party key Insured Party Coverage key Coverage Policy key Policy Covered item key Policy key Claim key day of week fiscal period name employee type department Claims Facts name address type description market segment line of business risk grade description type Transaction key description reason Transaction demographics ... Coverage key annual stmt line Transaction key Third party key Amount Claim key description type Claim Claimant key Claimant name address type Third party key Third Party name address type from Kimball (1996), p132

Insurance - Policy Monthly Snapshot: 

Insurance - Policy Monthly Snapshot Snapshot date Effective date Insured party key Agent key Status key Date key Agent key Covd item key Insured party key Coverage key Policy key Covered item key Policy key Written premium fiscal period agent name agent type Policy Snapshot name address type description market segment line of business risk grade description type Status key description demographics ... Coverage key annual stmt line agent location Primary deductible Earned premium Primary limit No transactions from Kimball (1996), p134

Insurance - Claims Monthly Snapshot: 

Insurance - Claims Monthly Snapshot Snapshot date Effective date Insured party key Agent key Claim key Date key Agent key Covd item key Insured party key Coverage key Policy key Covered item key Policy key Status key fiscal period agent name agent location agent type Claims Snapshot name address type description market segment line of business risk grade description type demographics ... Coverage key annual stmt line Paid this month Reserve amount Received this mth Status key description Claim key Claim description claim type No transactions from Kimball (1996), p135

Policy Transaction - Heterogeneous Products: 

Policy Transaction - Heterogeneous Products Transaction date Effective date Insured party key Employee key Transaction key Covered item key Policy key Amount Policy Transaction Coverage key Covd item key description type homeowner atts automobile atts pers article atts Gen liability atts Coverage key description market segment homeowner atts automobile atts pers article atts Gen liability atts line of business ann statement line from Kimball (1996), p136

Policy Transaction - Custom Dimension Tables: 

Policy Transaction - Custom Dimension Tables Transaction date Effective date Insured party key Employee key Transaction key Covered item key Policy key Amount Policy Transaction Coverage key Covd item key description type automobile atts Coverage key description market segment automobile atts line of business ann statement line from Kimball (1996), p136

Policy Snapshot - Custom Dimension Tables: 

Policy Snapshot - Custom Dimension Tables Transaction date Effective date Insured party key Employee key Claimant key Covered item key Policy key Claim key Claims Transaction Coverage key Transaction key Third party key Amount Claim key description type Coverage key description market segment automobile atts line of business ann statement line Covd item key description type automobile atts automobile atts from Kimball (1996), p137