Week 3 DW Design Kimball

Information about Week 3 DW Design Kimball

Published on November 7, 2007

Author: Jolene

Source: authorstream.com

Content

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

Related presentations


Other presentations created by Jolene

Smart Dust and Micro Robots
07. 01. 2008
0 views

Smart Dust and Micro Robots

2509 9h20 kessler usoinseguro
02. 05. 2008
0 views

2509 9h20 kessler usoinseguro

whats driving emerging markets
27. 09. 2007
0 views

whats driving emerging markets

Barrie Peter Pan Text Only
12. 10. 2007
0 views

Barrie Peter Pan Text Only

rutherford winterjohnson
13. 10. 2007
0 views

rutherford winterjohnson

chap3 2
16. 10. 2007
0 views

chap3 2

IntroDNACloningDNARep
16. 10. 2007
0 views

IntroDNACloningDNARep

MOAC204
17. 10. 2007
0 views

MOAC204

nile hydrology
23. 10. 2007
0 views

nile hydrology

12 HACCPJuiceSeafood French
24. 10. 2007
0 views

12 HACCPJuiceSeafood French

politiques Sant Boi Pere Dorca
24. 10. 2007
0 views

politiques Sant Boi Pere Dorca

thomson
15. 10. 2007
0 views

thomson

climate change and poverty
29. 11. 2007
0 views

climate change and poverty

botnet underground economics
04. 12. 2007
0 views

botnet underground economics

Macroevolution
12. 10. 2007
0 views

Macroevolution

Roman Religion powerpoint
29. 10. 2007
0 views

Roman Religion powerpoint

2268Atakan
31. 10. 2007
0 views

2268Atakan

Banned Books
31. 10. 2007
0 views

Banned Books

Bob
01. 11. 2007
0 views

Bob

STILLEHAVSKRIGEN3
13. 11. 2007
0 views

STILLEHAVSKRIGEN3

enhancing osh standards
14. 11. 2007
0 views

enhancing osh standards

ELISA 2004
12. 10. 2007
0 views

ELISA 2004

Climate McBean f
21. 10. 2007
0 views

Climate McBean f

PP 08 Conditioning
16. 11. 2007
0 views

PP 08 Conditioning

Ontologies in Bioinformatics
20. 11. 2007
0 views

Ontologies in Bioinformatics

laminas centroamÃrica
22. 10. 2007
0 views

laminas centroamÃrica

1World War II
23. 12. 2007
0 views

1World War II

NOAA Fisheries NMFS
28. 12. 2007
0 views

NOAA Fisheries NMFS

ClassDay4
31. 12. 2007
0 views

ClassDay4

ENGR310 1 07
03. 01. 2008
0 views

ENGR310 1 07

PAN Villarreal
22. 10. 2007
0 views

PAN Villarreal

gpr
05. 01. 2008
0 views

gpr

hansen
29. 10. 2007
0 views

hansen

3 El Hattab Ahamed
25. 10. 2007
0 views

3 El Hattab Ahamed

marrocos2
24. 10. 2007
0 views

marrocos2

NWS Partnering
05. 10. 2007
0 views

NWS Partnering

Lyster
16. 10. 2007
0 views

Lyster

webcast1
29. 10. 2007
0 views

webcast1

Chicago
15. 10. 2007
0 views

Chicago

Faridah Noor Indian Dance New
23. 11. 2007
0 views

Faridah Noor Indian Dance New

xmm UG 051606
28. 11. 2007
0 views

xmm UG 051606

ISOM7
15. 10. 2007
0 views

ISOM7

Thermocouple
16. 02. 2008
0 views

Thermocouple

Gubler
21. 10. 2007
0 views

Gubler

test5
30. 10. 2007
0 views

test5

pit 2
26. 02. 2008
0 views

pit 2

m6 Eyler
28. 02. 2008
0 views

m6 Eyler

PSCM
07. 03. 2008
0 views

PSCM

pfl powerpoint may06
10. 03. 2008
0 views

pfl powerpoint may06

Prasa2001
13. 03. 2008
0 views

Prasa2001

PresentationANALYSTE SSNI2006
24. 10. 2007
0 views

PresentationANALYSTE SSNI2006

Perine I2 22april04
16. 03. 2008
0 views

Perine I2 22april04

Panel B Igor Hansen
20. 03. 2008
0 views

Panel B Igor Hansen

kapil kaul
25. 03. 2008
0 views

kapil kaul

part1 intro
07. 10. 2007
0 views

part1 intro

ans1 ffa program
03. 04. 2008
0 views

ans1 ffa program

Jonah
07. 04. 2008
0 views

Jonah

051208 Osawa
09. 10. 2007
0 views

051208 Osawa

3arq
18. 10. 2007
0 views

3arq

Bourgeat Siam
06. 12. 2007
0 views

Bourgeat Siam

allen
08. 04. 2008
0 views

allen

Fry 2005
10. 04. 2008
0 views

Fry 2005

ValueofNonpartisan
14. 04. 2008
0 views

ValueofNonpartisan

lovelock12
16. 04. 2008
0 views

lovelock12

Pumpkin SmallSat 2006
17. 04. 2008
0 views

Pumpkin SmallSat 2006

CSAM DOJ Briefing Day2
22. 04. 2008
0 views

CSAM DOJ Briefing Day2

SOUTHEAST pp
03. 01. 2008
0 views

SOUTHEAST pp

Arbetslagsplan 04 05
02. 11. 2007
0 views

Arbetslagsplan 04 05

dentist
06. 05. 2008
0 views

dentist

critical care slides
07. 05. 2008
0 views

critical care slides

origins
08. 05. 2008
0 views

origins

mas info
08. 05. 2008
0 views

mas info

18 4
22. 10. 2007
0 views

18 4

Vulvodynia
01. 05. 2008
0 views

Vulvodynia

014 Robot Control Architectures
02. 05. 2008
0 views

014 Robot Control Architectures

3678s1 03 schechter
02. 05. 2008
0 views

3678s1 03 schechter

Dana Guerrieri
02. 05. 2008
0 views

Dana Guerrieri

Anestezja
02. 05. 2008
0 views

Anestezja

lezersp1
15. 10. 2007
0 views

lezersp1

20050928 Yasuyuki Fuchita
09. 10. 2007
0 views

20050928 Yasuyuki Fuchita

S Pathi
16. 10. 2007
0 views

S Pathi

Thompson Adjusted
30. 04. 2008
0 views

Thompson Adjusted

Poretti dscut Gdor CW8
15. 11. 2007
0 views

Poretti dscut Gdor CW8

2002 04 maintenance
19. 11. 2007
0 views

2002 04 maintenance

N1 Richard News from ELAN Paris
29. 09. 2007
0 views

N1 Richard News from ELAN Paris

cal20050303
09. 10. 2007
0 views

cal20050303

2007 FL GHC ICC Tezak Kilcollins
05. 10. 2007
0 views

2007 FL GHC ICC Tezak Kilcollins

TUD About the institution
18. 03. 2008
0 views

TUD About the institution

iPOP2007 OIF Berthold
09. 10. 2007
0 views

iPOP2007 OIF Berthold

patricia arsene
15. 10. 2007
0 views

patricia arsene

IHS 2003
27. 03. 2008
0 views

IHS 2003

MOTIVATE 1 slides
29. 10. 2007
0 views

MOTIVATE 1 slides

Alia2003
05. 10. 2007
0 views

Alia2003

RE SUNUM ppt2
23. 11. 2007
0 views

RE SUNUM ppt2

grid2000 welcome
17. 10. 2007
0 views

grid2000 welcome

Larry
07. 01. 2008
0 views

Larry

Abel
25. 10. 2007
0 views

Abel

Weitz Tracy 1 24 07
03. 01. 2008
0 views

Weitz Tracy 1 24 07

del rel
07. 11. 2007
0 views

del rel

ilc analyse
23. 10. 2007
0 views

ilc analyse

CAS 07 MDHS
02. 05. 2008
0 views

CAS 07 MDHS