DW Boot Camp DW Overview βAcademy reckenridgedownload. 5 DW Boot Camp DW Overview...

Click here to load reader

  • date post

    14-Jul-2020
  • Category

    Documents

  • view

    6
  • download

    0

Embed Size (px)

Transcript of DW Boot Camp DW Overview βAcademy reckenridgedownload. 5 DW Boot Camp DW Overview...

  • 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/