Lecture 5 Database Management Objectives of Lecture 5 Systems Data Warehousing and OLAP Data Warehousing and OLAP • Realize the purpose of data warehousing. Winter 2004 • Comprehend the data structures behind data CMPUT 391: Data Warehousing warehouses and understand the OLAP technology. Dr. Osmar R. Zaïane • Get an overview of the schemas used for multi- dimensional data. • See some implementations of OLAP operators with University of Alberta Chapter 19 of SQL Textbook Database Management Systems University of Alberta 1 Database Management Systems University of Alberta 2 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 Data Warehouse and OLAP Incentive for a Data Warehouse • Businesses have a lot of data, operational data and facts. • This data is usually in different databases and in different • What is a data warehouse and what is it for? physical places. • What is the multi-dimensional data model? • Data is available (or archived), but in different formats and locations. (heterogeneous and distributed). • What is the difference between OLAP and OLTP? • What is the general architecture of a data warehouse? • How can we implement a data warehouse? • Decision makers need to access information (data that has been • Are there issues related to data cube technology? summarized) virtually on one single site. • This access needs to be fast regardless of the size of the data, and how old the data is. 3 4 Database Management Systems University of Alberta Database Management Systems University of Alberta Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004
What Is Data Warehouse? Evolution of Decision Support Systems • A data warehouse consolidates different data sources. 1970s 1990s 1960s 1980s • A data warehouse is a database that is different and maintained Data Warehousing and Terminal-based Batch and Manual D On-Line Analytical Processing Decision Support Systems e Reporting s separately from an operational database. k t o p • A data warehouse combines and merges information in a consistent D a database (not necessarily up-to-date) to help decision support. t a A n a l y s i s T • Statistician o o • Computer scientist l s Difficult and limited • Data Analyst • Data Analyst Decision support systems access data warehouse and queries highly Inflexible and Flexible integrated do not need to access operational databases � do • Executive specific to some non-integrated spreadsheets. Integrated tools distinctive needs not unnecessarily over-load operational databases. tools Slow access to Data Mining operational data Database Management Systems University of Alberta 5 Database Management Systems University of Alberta 6 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 Definitions Definitions (con’t) Data Warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s Data Warehousing is the process of constructing and using decision making process. (W.H. Inmon) data warehouses. Subject oriented: oriented to the major subject areas of the corporation that have been defined in the data model. A corporate data warehouse collects data about subjects Integrated: data collected in a data warehouse originates from different heterogeneous data sources. spanning the whole organization. Data Marts are specialized, single-line of business warehouses. They collect data for a Time-variant: The dimension “time” is all-pervading in a data warehouse. department or a specific group of people. The data stored is not the current value, but an evolution of the value in time. Non-volatile: update of data does not occur frequently in the data warehouse. The data is loaded and accessed. 7 8 Database Management Systems University of Alberta Database Management Systems University of Alberta Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004
Data Warehouse and OLAP Building a Data Warehouse • What is a data warehouse and what is it for? Option 1 : Corporate • What is the multi-dimensional data model? Consolidate Data Marts Data Warehouse • What is the difference between OLAP and OLTP? Option 2 : • What is the general architecture of a data warehouse? Build from scratch • How can we implement a data warehouse? Data Mart Data Mart Data Mart Data Mart • Are there issues related to data cube technology? Corporate data Database Management Systems University of Alberta 9 Database Management Systems University of Alberta 10 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 Construction of Data Warehouse Describing the Organization Based on Multi-dimensional Model We sell products in various • Think of it as a cube with labels markets, and we measure our on each edge of the cube. performance over time • The cube doesn’t just have 3 dimensions, but may have many Business Manager dimensions (N). • Any point inside the cube is at the intersection of the coordinates We sell Products in various Time defined by the edge of the cube. Markets Markets , and we measure our • A point in the cube may store performance over Time values (measurements) relative to the combination of the labeled Products Data Warehouse Designer dimensions. 11 12 Database Management Systems University of Alberta Database Management Systems University of Alberta Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004
Concept-Hierarchies Data Warehouse and OLAP • What is a data warehouse and what is it for? Most Dimensions are hierarchical by nature: total orders or partial orders Example: Location(continent � country � province � city) • What is the multi-dimensional data model? Time(year � quarter � (month,week) � day) • What is the difference between OLAP and OLTP? Industry Country Year • What is the general architecture of a data warehouse? Dimensions: Product, Region, Time Category Region Quarter • How can we implement a data warehouse? Hierarchical summarization paths Product City Month Week • Are there issues related to data cube technology? Office Day Database Management Systems University of Alberta 13 Database Management Systems University of Alberta 14 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 On-Line Transaction Processing On-Line Analytical Processing • On-line analytical processing (OLAP) is essential for • Database management systems are typically used for on-line decision support. transaction processing (OLTP) • OLAP is supported by data warehouses. • Data warehouse consolidation of operational databases. • OLTP applications normally automate clerical data • The key structure of the data warehouse always contains processing tasks of an organization, like data entry and some element of time. enquiry, transaction handling, etc. (access, read, update) •Owing to the hierarchical nature of the dimensions, OLAP • Database is current, and consistency and recoverability are operations view the data flexibly from different perspectives critical. Records are accessed one at a time. (different levels of abstractions). � OLTP operations are structured and repetitive • roll-up (increase the level of abstraction) � OLTP operations require detailed and up-to-date data •OLAP operations: • drill-down (decrease the level of abstraction) � OLTP operations are short, atomic and isolated transactions • slice and dice (selection and projection) • pivot (re-orient the multi-dimensional view) DW tend to be in the order of Tb • drill-through (links to the raw data) Databases tend to be hundreds of Mb to Gb. 15 16 Database Management Systems University of Alberta Database Management Systems University of Alberta Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004
Recommend
More recommend
