Chapter 9 : Data Warehousing - PowerPoint PPT Presentation

chapter 9 data warehousing n.
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 9 : Data Warehousing PowerPoint Presentation
Download Presentation
Chapter 9 : Data Warehousing

play fullscreen
1 / 86
Chapter 9 : Data Warehousing
Download Presentation
Download Presentation

Chapter 9 : Data Warehousing

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Chapter 9: Data Warehousing Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258

  2. Objectives Definition of terms Reasons for information gap between information needs and availability Reasons for need of data warehousing Describe three levels of data warehouse architectures (ETL) Describe two components of star schema Estimate fact table size Design a data mart Develop requirements for a data mart OLAP, data mining and its applications

  3. A Solution to the Information Gap • A solution to bridging the information gap is the ______ _________ which consolidate and integrate information from many different sources and arrange it in a meaningful format for making accurate business decisions. data warehouses

  4. Two issues need to know about D.W. • 1. A major factor drives the need for data warehousing • Businesses need an integrated view of company information. • 2. Which of the following organizational trends does not encourage the need for data warehousing? • Multiple, nonsynchronized systems • Focus on customer relationship management • Downsizing • Focus on supplier relationship management • Answer: _____ 2

  5. Need for Data Warehousing Integrated, company-wide view of high-quality information (from disparate databases) Separation of operational and informational systems and data (for improved performance) Table 9-1 – Comparison of Operational and Informational Systems

  6. DATA WAREHOUSE FUNDAMENTALS • Data warehouse – a logical collection of information – gathered from many different operational databases – that supports business analysis activities and decision-making tasks • The primary purpose of a data warehouse is to aggregate information throughout an organization into a single repository for decision-making purposes

  7. Definition Data Warehouse: A subject-oriented, integrated, time-variant, non-updatable collection of data used in support of management decision-making processes Subject-oriented: e.g. customers, patients, students, products DW is organized around key high-level entities of the enterprise Integrated: Consistent naming conventions, formats, encoding structures; from multiple data sources Time-variant: Can study trends and changes data in the warehouse contain a time dimension so that they may be used to study trends and changes. Non-updatable: Read-only, periodically refreshed Data Mart: A data warehouse that is limited in scope contains a subset of data warehouse information

  8. History Leading to Data Warehousing • Improvement in database technologies, especially relational DBMSs • Advances in computer hardware, including mass storage and parallel architectures • Emergence of end-user computing with powerful interfaces and tools • Advances in middleware, enabling heterogeneous database connectivity • Recognition of difference between operational and informational systems

  9. Need for Data Warehousing • Integrated, company-wide view of high-quality information (from disparate databases) • Separation of operational and informationalsystems and data (for improved performance)

  10. Issues with Company-Wide View • Inconsistent key structures • Synonyms • Free-form vs. structured fields • Inconsistent data values • Missing data See figure 9-1 for example

  11. Figure 9-1 Examples of heterogeneous data

  12. Database vs. Datawarehouse Database DBMS Data Warehouse ???

  13. Database vs. Datawarehouse Database DBMS Data Warehouse Data Mining

  14. Database vs. Datawarehouse Database DBMS Datawarehouse ???

  15. Legacy Systems Sales Data Mart Finance Data Mart Operational Data Store Marketing Data Mart Accounting Data Mart Operational Data Store Operational Data Store Operational Data Store Data Marts and the Data Warehouse Legacy systems feed data to the warehouse. The warehouse feeds specialized information to departments (data marts). ETL Organizational Data Warehouse ETL

  16. The Data Mart is More Specialized • Organizational Data Warehouse • Corporate • Highly granular data • Normalized design • Robust historical data • Large data volume • Data Model driven data • Versatile • General purpose DBMS technologies The data mart serves the needs of one business unit, not the organization. Sales Data Mart Finance Data Mart Marketing Data Mart ETL Accting Data Mart Organizational Data Warehouse • Data Marts • Departmentalized • Summarized, aggregated data • Star join design • Limited historical data • Limited data volume • Requirements driven data • Focused on departmental needs • Multi-dimensional DBMS technologies

  17. Organizational Trends Motivating Data Warehouses • No single system of records • Multiple systems not synchronized • Organizational need to analyze activities in a balanced way • Customer relationship management • Supplier relationship management

  18. Separating Operational and Informational Systems • Operational system – a system that is used to run a business in real time, based on current data; also called a system of record • Informational system – a system designed to support decision making based on historical point-in-time and prediction data for complex queries or data-mining applications

  19. Position of the Data Warehouse Within the Organization

  20. DATA WAREHOUSE FUNDAMENTALS (cont.) • Extraction, transformation, and loading (ETL) – a process that extracts information from internal and external databases, transforms the information using a common set of enterprise definitions, andloads the information into a data warehouse

  21. Data Warehouse Architectures Independent Data Mart Dependent Data Mart and Operational Data Store Logical Data Mart and Real-Time Data Warehouse Three-Layer architecture All involve some form of extraction, transformation and loading (ETL)

  22. Data marts: Mini-warehouses, limited in scope L T E Separate ETL for each independent data mart Data access complexity due to multiple data marts Figure 9-2 Independent data mart data warehousing architecture 23

  23. ODS provides option for obtaining current data L T E Simpler data access Dependent data marts loaded from EDW Single ETL for enterprise data warehouse (EDW) Figure 9-3 Dependent data mart with operational data store:a three-level architecture 24

  24. Figure 9-4 Logical data mart and real time warehouse architecture ODS and data warehouse are one and the same L T E Data marts are NOT separate databases,but logical views of the data warehouse  Easier to create new data marts Near real-time ETL for Data Warehouse 25

  25. The ETL Process –another perspective and example • Capture/Extract - E • Scrub or data cleansing • Transform - T • Load and Index - L ETL = Extract, transform, and load

  26. Capture/Extract…obtaining a snapshot of a chosen subset of the source data for loading into the data warehouse Incremental extract = capturing changes that have occurred since the last static extract Static extract = capturing a snapshot of the source data at a point in time

  27. Scrub/Cleanse…uses pattern recognition and AI techniques to upgrade data quality Fixing errors: misspellings, erroneous dates, incorrect field usage, mismatched addresses, missing data, duplicate data, inconsistencies Also: decoding, reformatting, time stamping, conversion, key generation, merging, error detection/logging, locating missing data

  28. Transform = convert data from format of operational system to format of data warehouse Record-level: Selection – data partitioning Joining – data combining Aggregation – data summarization Field-level: single-field – from one field to one field multi-field – from many fields to one, or one field to many

  29. Load/Index= place transformed data into the warehouse and create indexes Refresh mode: bulk rewriting of target data at periodic intervals Update mode: only changes in source data are written to data warehouse

  30. Information Cleansing or Scrubbing • An organization must maintain high-quality data in the data warehouse • Information cleansing or scrubbing – a process that weeds out and fixes or discards inconsistent, incorrect, or incomplete information

  31. Information Cleansing or Scrubbing • Standardizing Customer name from Operational Systems

  32. Information Cleansing or Scrubbing

  33. Information Cleansing or Scrubbing • Accurate and complete information

  34. Figure 9-5 Three-layer data architecture for a data warehouse

  35. Status Status Figure 9-6 Example of DBMS log entry Data CharacteristicsStatus vs. Event Data Event = a database action (create/ update/ delete) that results from a transaction

  36. Data CharacteristicsTransient vs. Periodic Data Figure 9-7 Transient operational data With transient data, changes to existing records are written over previous records, thus destroying the previous data content

  37. Data CharacteristicsTransient vs. Periodic Data Figure 9-8 Periodic warehouse data Periodic data are never physically altered or deleted once they have been added to the store

  38. Other Data Warehouse Changes • New descriptive attributes • New business activity attributes • New classes of descriptive attributes • Descriptive attributes become more refined • Descriptive data are related to one another • New source of data

  39. Data Reconciliation • Typical operational data is: • Transient – not historical • Not normalized (perhaps due to denormalization for performance) • Restricted in scope – not comprehensive • Sometimes poor quality – inconsistencies and errors • After ETL, data should be: • Detailed – not summarized yet • Historical – periodic • Normalized – 3rd normal form or higher • Comprehensive – enterprise-wide perspective • Timely – data should be current enough to assist decision-making • Quality controlled – accurate with full integrity

  40. Derived Data Objectives Ease of use for decision support applications Fast response to predefined user queries Customized data for particular target audiences Ad-hoc query support Data mining capabilities Characteristics Detailed (mostly periodic) data Aggregate (for summary) Distributed (to departmental servers) Most common data model = star schema (also called “dimensional model”)

  41. Figure 9-9 Components of a star schema Fact tables contain factual (descriptive) or quantitative data (numerical values) 1:N relationship between dimension tables and fact tables Dimension tables are denormalized to maximize performance Dimension tables contain descriptions about the subjects of the business (values in the fact table) Excellent for ad-hoc queries, but bad for online transaction processing

  42. Figure 9-10 Star schema example Fact table provides statistics for sales broken down by product, period and store dimensions

  43. Figure 9-11 Star schema with sample data

  44. Surrogate Dimension Keys • Dimension table keys should be surrogate (non-intelligent and non-business related), because: • Business keys may change over time • Helps keep track of nonkey attribute values for a given production key • Surrogate keys are simpler and shorter • Surrogate keys can be same length and format for all keys

  45. Grain of the Fact Table • Granularity of Fact Table–what level of detail do you want? • Transactional grain–finest level • Aggregated grain–more summarized • Finer grains  better market basket analysis capability • Finer grain  more dimension tables, more rows in fact table • In Web-based commerce, finest granularity is a click

  46. Duration of the Database • Natural duration–13 months or 5 quarters • Financial institutions may need longer duration • Older data is more difficult to source and cleanse

  47. Size of Fact Table Depends on the number of dimensions and the grain of the fact table Number of rows = product of number of possible values for each dimension associated with the fact table Example: assume the following for Figure 9-11: Total rows calculated as follows (assuming only half the products record sales for a given month):

  48. Break ! (Ch. 9) • Exercise • # 5 – a, b, c (p. 422) • With the following assumptions: • The length of a fiscal period is one month • The data mart will contain five years of historical data • 3. Approximately 5 percent of the policies experience some type of change each month • 4. There are 8 fields in each record (row) HW #3 (p.422) – a, b, c Assume one professor per course section ALL computations for b & c should be shown to get credits .