1

DW Boot Camp DW Overview

©Copyright 2010, Breckenridge Academy. All rights reserved.

β reckenridgecademyA

©Copyright 2010, Breckenridge Academy. All rights reserved.

Breckenridge Academy

Inmon vs. Kimball: Let’s get to the bottom of this!

TDWI LA Chapter

Los Angeles, CA

March 9, 2010

Speaker: Bob Conway BobConway@BreckAcademy.com 303-885-4811

Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 2

©Copyright 2010, Breckenridge Academy. All rights reserved.

Strategic Planning

Management Monitoring & Control

Business Operations

Decision Support

Analysis & Reporting

Transaction Processing

Anthony’s Pyramid

Business Perspective Systems Perspective

2

DW Boot Camp DW Overview

©Copyright 2010, Breckenridge Academy. All rights reserved.

β reckenridgecademyA Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 3

©Copyright 2010, Breckenridge Academy. All rights reserved.

Why Data Warehousing? Transaction Processing

•Real-time perspective

•Detailed data

•Update intensive

•Online updates

•Batch reporting

•Performance sensitive

•Structured processes

•Stable data structures

•Functional organization

•Clerical community

Analytical/Reporting

•Historic perspective

•Summarized/derived info.

•Read-only

•Batch updates

•Online reporting

•Flexibility priority

•Ad-hoc reporting

•Evolving data structures

•Cross-functional

•Mgmt/analyst community

Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 4

©Copyright 2010, Breckenridge Academy. All rights reserved.

OLTP versus Reporting Design

Transaction Processing

•Highly normalized

•Minimal indexing

•Transaction logging on

•Record locking on

•Individual records

•Calculate derived data

Analytical Processing

•Denormalized

•Liberal indexing

•Transaction logging off

•Record locking off

•Sets of data

•Store derived data

3

DW Boot Camp DW Overview

©Copyright 2010, Breckenridge Academy. All rights reserved.

β reckenridgecademyA Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 5

©Copyright 2010, Breckenridge Academy. All rights reserved.

Traditional System Development

PARTS SUPPLIERS

POs

INVOICES VENDORS PAYMENTS

PARTS ASSYs

REQUISITIONS

Purchasing Application

Accounts Payable

Inventory Management

•Data belongs to an application •Point-to-point interfaces

Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 6

©Copyright 2010, Breckenridge Academy. All rights reserved.

Typical Legacy ‘Architecture’ • Redundant data ($) • Inconsistent, unreliable data ($$$)

4

DW Boot Camp DW Overview

©Copyright 2010, Breckenridge Academy. All rights reserved.

β reckenridgecademyA Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 7

©Copyright 2010, Breckenridge Academy. All rights reserved.

Data Warehouse Concept

Extract, Translate Load Data Transformation Layer

Operational Layer (source of record)

Data Warehouse

Reporting and Analysis Read Only Snapshots Historical, Summarized Validated, Integrated

Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 8

©Copyright 2010, Breckenridge Academy. All rights reserved.

Strategic Planning

Management Monitoring & Control

Business Operations

Decision Support

Analysis & Reporting

Transaction Processing

Anthony’s Pyramid

Business Perspective Systems Perspective

IE only addresses

these

DW addresses

these

•DW complement Tx Proc •DW pathway to integration •DW immediate value

5

DW Boot Camp DW Overview

©Copyright 2010, Breckenridge Academy. All rights reserved.

β reckenridgecademyA Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 9

©Copyright 2010, Breckenridge Academy. All rights reserved.

Inmon Architecture circa 1992

Oper DB •op detail •current •App-oriented •Unintegrated

Atomic DW •op grain •latency •subject oriented •time variant •enterprise integrated

Dept DW •parochial •summary •derived data

Individual •PC-based •temporary •ad-hoc •heuristic

Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 10

©Copyright 2010, Breckenridge Academy. All rights reserved.

Relational (3NF) Design MARKET

market_id

CUSTOMER

customer_id

market_id postal_cd cntry_id

ORDER

order_id

customer_id employee_id day_id

ORDER_ITEM

order_id order_line_id

product_id

PRODUCT_CLASS

prodcut_class_id

PRODUCT_CATEGORY

product_category_id

prodcut_class_id

PRODUCT

product_id

product_category_id

EMPLOYEE

employee_id

territory_id department_id

DEPARTMENT

department_id

division_id

TERRITORY

territory_id

region_id

REGION

region_id

DIVISION

division_id

ACCOUNT

account_id

INVOICE

invoice_id

account_id

INVOICE_ITEM

invoice_line_id invoice_id

order_id order_line_id day_id

YEAR

year_id

QUARTER

quarter_id

year_id

MONTH

month_id

quarter_id

WEEK

week_id

month_id

DAY

day_id

month_id week_id pay_period_id

PAY_PERIOD

pay_period_id

month_id

COUNTRY

cntry_id

STATE

cntry_id state_id

COUNTY

county_id

state_id cntry_id

CITY

city_id

county_id

POSTAL_ZONE

postal_cd cntry_id

city_id

6

DW Boot Camp DW Overview

©Copyright 2010, Breckenridge Academy. All rights reserved.

β reckenridgecademyA Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 11

©Copyright 2010, Breckenridge Academy. All rights reserved.

Atomic History Table Design

CSTMR_HST cstmr_dwidhst_sqnc_id strt_ext_ts end_ext_ts** hst_crf cstmr_nm

101 1 Mon night Wed night N Bob 102 1 Mon night 12/31/2999 Y Joe 103 1 Tues night 12/31/2999 Y Mary 101 2 Wed night 12/31/2999 Y Robert

No gaps or overlaps in time spans for a given DWID value ** Use high date (12/31/2999) instead of NULL for current row

101 - Bob

101-Robert

Mon Wed 29991231

Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 12

©Copyright 2010, Breckenridge Academy. All rights reserved.

Inmon Architecture circa 1996

ODS EDW Data

Marts

Oper DB •op detail •current

•App-oriented •Unintegrated

Oper Data Store •op grain •latency

•subject oriented •volatile •enterprise integrated

Enterprise DW •op grain •latency

•subject oriented •time variant •enterprise integrated

Dept DW •parochial •summary

•derived data

7

DW Boot Camp DW Overview

©Copyright 2010, Breckenridge Academy. All rights reserved.

β reckenridgecademyA Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 13

©Copyright 2010, Breckenridge Academy. All rights reserved.

Kimball Architecture circa 1996

� 3NF is great for tx processing but is inappropriate for DW

� Dimensional model (star schemas)

� Central fact table –aggregate measures

� Grouped by dimensions - denormalized

� EDW- collection of star schemas (by subject area) with shared (conforming) dimensions.

Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 14

©Copyright 2010, Breckenridge Academy. All rights reserved.

Dimensional (Star Schema) Design

D_PRODUCT

product_id

prodcut_class_id product_category_id

D_EMPLOYEE

employee_id

territory_id region_id department_id division_id

D_CUSTOMER

customer_id

market_id postal_cd city_id county_id state_id cntry_id

D_DAY

day_id

week_id pay_period_id month_id quarter_id year_id

F_SALES

day_id employee_id product_id customer_id

sales_units sales_amount ytd_sales

8

DW Boot Camp DW Overview

©Copyright 2010, Breckenridge Academy. All rights reserved.

β reckenridgecademyA Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 15

©Copyright 2010, Breckenridge Academy. All rights reserved.

Proliferation of Data Marts

Sales

MRP

Acctg

Sales DM

Mfg DM

Finance DM

HR

ETL tools

BI Tools

BI Tools

BI Tools

Breckenridge Academy Data Warehouse Boot Camp DW Overview, Page 16

©Copyright 2010, Breckenridge Academy. All rights reserved.

DW Challenges in 2010

� Multiple DWs-Redunant/