1 / 248

AM33 Data Warehousing A to Z

AM33 Data Warehousing A to Z. Harnam Thandi Senior Consultant Business Intelligence Division harnam@sybase.com. Part 1 - Business Drivers Part 2 - Data Warehouse Architecture Part 3 - Project Management Part 3 - Data Quality Part 5 - Data Exploitation

burke
Download Presentation

AM33 Data Warehousing A to Z

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. AM33Data Warehousing A to Z • Harnam Thandi • Senior Consultant • Business Intelligence Division • harnam@sybase.com

  2. Part 1 - Business Drivers Part 2 - Data Warehouse Architecture Part 3 - Project Management Part 3 - Data Quality Part 5 - Data Exploitation Part 6 - Database Performance & Design Part 7 – Copy Management Architecture About this Session

  3. Part 1 • The Data Warehouse Business Drivers

  4. “If only we knew (the facts) with greater certainty...” Hadrian 220 A.D. “The main objective in war, as in life, is to deduce what we do not know from what we do” Duke of Wellington, 1812 “The key to being successful in business is knowing something that nobody else knows” Aristotle Onassis “Whereas hydrocarbons were the major fuel for the industrial economy, information is the major fuel that provides energy for the new economy” Stanley Davis (from ‘Future Perfect’ 1984) Exploiting Data is not a Fashion. We have been doing it for thousands of years!

  5. MARKET LEADERSHIP Customer-facing Systems REVENUE GROWTH DSS Systems ADDED BUSINESS VALUE COST REDUCTION Operations Systems Automation Innovation Adaptation Information Management Maturity

  6. #1 Value Exchange WIDEN CUSTOMER ACQUISITION - Acquire new customers CUSTOMER LATENCY - Sell more to existing customers CUSTOMER LOYALTY - Maintain the customer relationship longer LENGTHEN DEEPEN Define your target customer base, quantify the current and full- potential value of these relationships, and commit the entire company to closing the gap between the two. HBR 12/95

  7. #2 Disintermediation Cutting out the Middleman .........

  8. #3 Disaggregation Customer Segmentation by: - Product - Value - Demographics - Psychographics - Behaviour - Geography

  9. #4 Mass Customisation Share of Market Share of Customer change Transparent Collaborative Product Adaptive Cosmetic no change no change change Representation

  10. An Information Systems Model for CRM “Just as language shapes thought and communication, measures shape the attitude and behaviour of a business organisation” System Customer Relationship Management Loyalty Analysis Value Analysis Behaviour Analysis Measurement Applications Prospecting Discounting Mediation Customer Applications Product Design Supply-chain Integration Process Applications

  11. Market Segmentation • Demographic • Psychographic • Geographic • Behavioural • Temporal • Commercial • Industrial • Credit-based Market Segmentation leads to Mass Customisation

  12. A Corporate Memory forMarketing Bad Debt Promotional History Demographics List Sources Model Scores Competitive Data Customers Prospects Product Information Regulations Payment History Product Utilization 3

  13. Micro-segmentation of Markets Level of detailed analysis (of markets) Data volumes Market Niche Segment Individual Marketing Focus

  14. Affinity Analysis - the obvious and the not so obvious .... Affinity: Razor Blades Affinity with: Promotions Customer Segments Time Locations Other products High-value products ‘Some of the things you can count, don’t count - Some of the things you can’t count, really count’ -Albert Einstein

  15. The key to unlocking the business potential of CRM 1. Vertical Industry Subject-oriented Data Models 2. Vertical Industry application templates 3. Generic implementation methodology TIME PROMOTION CUSTOMER SALES PRODUCT CHANNEL GEOGRAPHY

  16. Vertical Industry Applications

  17. Spread of Applications • Overwhelmingly, the data warehouse is used as an aggressive weapon for sales, marketing, pricing and strategic planning in competitive markets.

  18. And what does the future hold? Data Warehouse Maturity Model 1. Reporting 2. Ad hoc 3. Analysis 4. Applications 5. Hybrid Value 1 2 3 4 5 Administration Time

  19. Part 2 • Data Warehouse Architecture

  20. Transaction Processing & Query Processing - 2 Different Worlds Lack of History System Fragmentation Performance Why operational systems will never support business queries Resource Contention

  21. THE CONCEPTUAL ARCHITECTURE Data Acquisition Data Management Data Exploitation THE LOGICAL ARCHITECTURE Data Model Extraction/ Mapping Aggregate/ Summarise Middle- ware Query Handling Integrate/ Transform Applications Metadata Management Operation & Administration THE PHYSICAL ARCHITECTURE Modelling Tool Extraction Tool Metadata Tool Middle- ware s/w Data mart s/w Query tool suite DBMS Application development environment Cleansing Tool Hardware Platform Data comms network Data mart h/w Transformation Tool Loading Utility

  22. Architectural Options Tactical/Limited Value Proven & Preferred Analysis Paralysis/High Cost 3NF Schema (3 tier) 3NF DATABASE MANY {p} DATA MARTS REPORT- ORIENTED DATABASE 3NF DATABASE SOME {p}DATA MARTS BASIC STAR SINGLE FACT CONFORMED DIMENSIONS MANY FACTS ENTERPRISE WAREHOUSE n FACTS Dimensional Schema (2 tier) QUERY- ORIENTED DATA MART APPLICATION- ORIENTED DATA MART TRENDING ENTERPRISE WAREHOUSE Aggregate Data Single Multiple All Subjects Subject Subjects

  23. Subject: Sales Application/Property Matrix

  24. Un-architected proliferation of decision-support systems LEGACY DSS

  25. The Logical Data Warehouse Architecture Legacy Extraction Database Middleware Application

  26. An Enterprise DW Architecture Legacy Extraction Database Middleware Application ENTERPRISE WAREHOUSE SINGLE INFRASTRUCTURE FOR DSS APPLICATIONS SINGLE GATEWAY TO LEGACY DATA

  27. An Enterprise/Mart DW Architecture Legacy Extraction Database Middleware Application MART ENTERPRISE WAREHOUSE MART MART NORMALISED/ SEMI NORMALISED DATA LEGACY DATA DENORMALISED DATA

  28. An Application-specific DW Architecture Legacy Extraction Database Application SINGLE APPLICATION OBJECTIVE MULTIPLE DATA SOURCES

  29. A Subject-area DW Architecture Legacy Extraction Database Middleware Application SUBJECT AREA DATA WAREHOUSES SUBJECT AREA DATA WAREHOUSE APPLICATION DEVELOPMENT INFRASTRUCTURE DEVELOPMENT

  30. A Virtual DW Architecture Legacy Transparency/ Applications Extraction OPERATIONAL DATA QUERY RESULT DATA

  31. Extraction Tool Metadata OLAP Tool Metadata DW Model Metadata SQL Tool Metadata External Data Metadata Data Mining Metadata DATA WAREHOUSE METADATA Cleansing Conversions Metadata Applications Metadata Source Systems Metadata Data Marts Metadata Data Marts Metadata

  32. Administrative Data Environmental Data Managing the Warehouse Environment Metadata Categories Physical (where the data is stored), Logical (what the data is named), Contextual (how the data is administered) Physical Layer Semantic Layer Physical Metadata Physical Data Logical Metadata Contextual Metadata

  33. Usability : High Flexibility :Low Refined information Upper Strata Dimensions, summaries and aggregations appropriate to OLAP & apps Usability : Moderate Flexibility : Moderate Middle Strata FLEXIBILITY USABILITY Usability: Low Flexibility : High Atomic data that is not easily accessible and used for ad hoc SQL queries and DM Basement Strata Data Granularity in the Data Warehouse

  34. The New Hybrid DW Architecture Legacy Extraction Database Middleware Application DSS MART OPMART APPLICATION DEVELOPMENT INFRASTRUCTURE DEVELOPMENT

  35. Occupying a New Space High complexity on-line transactions Number of transactions OLTP DSS Complexity of Transaction

  36. Part 3 • Managing the Data Warehouse Project

  37. Data Warehouse Drivers High Organizational Readiness High HIGH PRIORITY High Internal (Product) Complexity Extent of Data Fragmentation LOW PRIORITY Low Market Turbulence High

  38. Taking the Right Course ..... Think Locally Think Globally Think Globally Act Locally Act Globally Act Locally Independent Data Marts lead to information fragmentation Enterprise deployment leads to analysis- paralysis Evolutionary deployment maximises benefits and minimises risk

  39. #1 The Waterfall Methodology Commonly agreed to be inappropriate CONSTRUCTION DESIGN Plan Analyse Design Build

  40. #2 The Iterative Methodology Commonly used and promoted as appropriate Not successful in practice CONSTRUCTION DESIGN

  41. #3 The Evolutionary Methodology Proven in practice CONSTRUCTION DESIGN High Level

  42. Manages the scalability risk Manages the technology risk Manages the design risk Ensures enterprise data standards Ensures application compatibility Enables concurrent application development Enables powerful pan-corporate ad hoc queries Advantages of Evolutionary Approach

  43. Only Scope can be Varied ........ COST SPEED Select any 2 out of 3 The IWS Packaged Solution resolves this dilemma QUALITY

  44. Evolutionary Principles • Kelly’s Rule of Evolutionary Design: ‘There must be a logical framework representing the whole before we can physically proceed to assemble the parts’

  45. Organisational Culture High INNOVATIVE Enterprise Warehouse ADAPTABLE Enterprise Architecture Extent of Change CONSERVATIVE Departmental Data marts DELEGATED Distributed Data marts Low Low High Extent of Empowerment

  46. The Design Choices Middle Out Design Top Down Design Bottom Up Design BUSINESS DRIVERS TECHNOLOGY DRIVERS

  47. Iterative Enhancement Integrate Applications Development Build Architect The infrastructure issues Plan The business issues DW Methodology

  48. Business strategy model Data warehouse logical model Legacy system data models Data warehouse physical model

  49. Data Warehouse Project Obstacles Lack of strategic focus/cost justification Business Issues Technical Issues Lack of sponsorship/user awareness Short-term demands of business Wrong business process alignment Lack of required data Difficulty in scoping the project Failure to contain modelling effort System integration/metadata mgt. Knowledge of legacy systems Inability to anticipate query behaviour

  50. Roles & Responsibilities

More Related