advertisement

Week 3 DW Design Kimball

50 %
50 %
advertisement
Information about Week 3 DW Design Kimball
Entertainment

Published on November 7, 2007

Author: Jolene

Source: authorstream.com

advertisement

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

Add a comment

Related presentations

Related pages

Kimball GroupKimball Design Tips Archives - Kimball Group

Kimball Design Tips. Home / ... we’ve worked with countless exemplary DW/BI project team members: smart, ... 3; 5; Next; Categories.
Read more

Kimball Group

The Kimball Group is the source for ... Articles & Design Tips; Kimball Forum; ... but we’re maintaining a streamlined website for the DW/BI ...
Read more

Data warehouse - Wikipedia, the free encyclopedia

... departments are created from the data warehouse. [19] Hybrid design ... DW provides a single source ... 7 ISBN 978-3-639-18589-8; Kimball ...
Read more

Dimensional modeling - Wikipedia, the free encyclopedia

Dimensional modeling ... [3] Single data (fact) table surrounded by multiple descriptive ... Kimball Group, Design Tips (69). ...
Read more

Data Warehouse - Basic Concepts - Universidade Nova de Lisboa

Data Warehouse - Basic Concepts 02 DW Basic Concepts - ... from Ralph Kimball, Margy Ross, Willey 3 DW Basic Concepts - ... Design Pattern for the DW
Read more

Kimball University: Maintaining Dimension Hierarchies ...

Here's how to design, ... Kimball University: Maintaining Dimension Hierarchies. ... try to create a single hierarchy that goes from day to week ...
Read more

Comparing Data Warehouse Design Methodologies for ...

Comparing Data Warehouse Design Methodologies for Microsoft SQL Server. By: ... 2013 - 3:52:05 PM - Jim Frayer: Back To ... Ralph Kimball - bottom-up ...
Read more

DW Boot Camp DW Overview βAcademy reckenridge

Breckenridge Academy Inmon vs. Kimball: ... 3 DW Boot Camp DW Overview ... Design MARKET market_id CUSTOMER customer_id market_id
Read more

Shrunken dimension - Microsoft SQL Server BI Online ...

Shrunken dimension table is a specific type of a dimension in Kimball DW design. ... Day of Week; Day of Week Name;
Read more