CSE5334 DATA MINING Lecture 3: Data CSE 4334/5334 Data Mining, - PowerPoint PPT Presentation

CSE5334 DATA MINING Lecture 3: Data CSE 4334/5334 Data Mining, Fall 2014 Warehousing, OLAP , Department of Computer Science and Engineering, University of Texas at Arlington Data Cube Chengkai Li (Slides courtesy of Jiawei Han)

Chapter 3: Data Warehousing and OLAP Technology: An Overview  What is a data warehouse?  A multi-dimensional data model  Data warehouse architecture  Data warehouse implementation Lecture 3: Data 2 Warehousing, OLAP

What is Data Warehouse?  “A data warehouse is a subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of management’s decision -making process.”— W. H. Inmon  Data warehousing: The process of constructing and using data warehouses Lecture 3: Data 3 Warehousing, OLAP

Data Warehouse — Subject-Oriented  Organized around major subjects, such as customer, product, sales  Focusing on the modeling and analysis of data for decision makers, not on daily operations or transaction processing  Provide a simple and concise view around particular subject issues by excluding data that are not useful in the decision support process Lecture 3: Data 4 Warehousing, OLAP

Data Warehouse — Integrated  Constructed by integrating multiple, heterogeneous data sources  relational databases, flat files, on-line transaction records  Data cleaning and data integration techniques are applied.  Ensure consistency in naming conventions, encoding structures, attribute measures, etc. among different data sources  E.g., Hotel price: currency, tax, breakfast covered, etc.  When data is moved to the warehouse, it is converted. Lecture 3: Data 5 Warehousing, OLAP

Data Warehouse — Time Variant  The time horizon for the data warehouse is significantly longer than that of operational systems  Operational database: current value data  Data warehouse data: provide information from a historical perspective (e.g., past 5-10 years)  Every key structure in the data warehouse  Contains an element of time, explicitly or implicitly  But the key of operational data may or may not contain “time element” Lecture 3: Data 6 Warehousing, OLAP

Data Warehouse — Nonvolatile  A physically separate store of data transformed from the operational environment  Operational update of data does not occur in the data warehouse environment  Does not require transaction processing, recovery, and concurrency control mechanisms  Requires only two operations in data accessing:  initial loading of data and access of data Lecture 3: Data 7 Warehousing, OLAP

Data Warehouse vs. Operational DBMS OLTP (on-line transaction processing)   Major task of traditional relational DBMS  Day-to-day operations: purchasing, inventory, banking, manufacturing, payroll, registration, accounting, etc. OLAP (on-line analytical processing)   Major task of data warehouse system  Data analysis and decision making Distinct features (OLTP vs. OLAP):   User and system orientation: customer vs. market  Data contents: current, detailed vs. historical, consolidated  Database design: ER + application vs. star + subject  View: current, local vs. evolutionary, integrated  Access patterns: update vs. read-only but complex queries Lecture 3: Data 8 Warehousing, OLAP

OLTP vs. OLAP OLTP OLAP users clerk, IT professional knowledge worker function day to day operations decision support application-oriented subject-oriented DB design data current, up-to-date historical, detailed, flat relational summarized, multidimensional isolated integrated, consolidated usage repetitive ad-hoc access read/write lots of scans index/hash on prim. key unit of work short, simple transaction complex query # records accessed tens millions #users thousands hundreds DB size 100MB-GB 100GB-TB transaction throughput query throughput, response metric Lecture 3: Data 9 Warehousing, OLAP

Why Separate Data Warehouse?  Different functions and different data:  Note: There are more and more systems which perform OLAP analysis directly on relational databases  There is no absolute boundary. Lecture 3: Data 10 Warehousing, OLAP

Chapter 3: Data Warehousing and OLAP Technology: An Overview  What is a data warehouse?  A multi-dimensional data model  Data warehouse architecture  Data warehouse implementation Lecture 3: Data 11 Warehousing, OLAP

Data Cube  A data warehouse is based on a multidimensional data model which views data in the form of a data cube  A data cube contains aggregates of measure values, on various combinations of dimensions, and furthermore, with various levels of aggregation on individual dimension.  In data warehousing literature, an n-D base cube is called a base cuboid. The top most 0-D cuboid, which holds the highest-level of summarization, is called the apex cuboid. The lattice of cuboids forms a data cube. Lecture 3: Data 12 Warehousing, OLAP

A 3-D Cuboid  Sales volume as a function of product, month, and region Dimensions: Product, Location, Time Hierarchical summarization paths Industry Region Year Category Country Quarter Product (Item) Item City Month Week Office Day Time (Month) Lecture 3: Data 13 Warehousing, OLAP

An Example of Data Cube Total annual sales Time (Quarter) of TV in U.S.A. 2Qtr 1Qtr 3Qtr 4Qtr sum TV U.S.A PC VCR (country) Location sum Canada Mexico sum August 28, 2014 14

Data Cube: A Lattice of Cuboids all 0-D(apex) cuboid location product time 1-D cuboids product,time product,location time, location 2-D cuboids 3-D(base) cuboid product, time, location August 28, 2014 15

Cuboids all location product time time, location product,location Total annual sales Time (Quarter) of TV in U.S.A. 1Qtr 2Qtr 3Qtr product,time 4Qtr sum TV PC U.S.A VCR (country) Location sum product, time, location Canada Mexico sum Lecture 1: Introduction 16

Another 4-D Data Cube all 0-D(apex) cuboid time product location supplier 1-D cuboids time,location product, location location,supplier 2-D cuboids time,supplier product,supplier time,product time,location,supplier 3-D cuboids time,product,location product,location,supplier time,product,supplier 4-D(base) cuboid time, product, location, supplier Lecture 3: Data 17 Warehousing, OLAP

A Concept Hierarchy on Location Dimension all all Europe ... North_America region Germany ... Spain Canada ... Mexico country Vancouver ... city Frankfurt ... Toronto L. Chan ... M. Wind office Lecture 3: Data 18 Warehousing, OLAP

Concept Hierarchy in Data Cube all Product Time Location (category) (quarter) (country) Product(category), location (country) Location Product(category), Time(quarter), (city) Time(quarter) Location(country) Product(category), Product(category), Time(quarter), location (city) Time(quarter), Location(city) Location(country) Product(category), Time(quarter), Location(city) August 28, 2014 19

Conceptual Schema Design  Dimensions & Measures  Dimension tables, such as product (item_name, brand, type), or time(day, week, month, quarter, year)  Fact table contains measures (such as dollars_sold) and keys to each of the related dimension tables Lecture 3: Data 20 Warehousing, OLAP

Conceptual Modeling of Data Warehouses  Star schema: A fact table in the middle connected to a set of dimension tables Lecture 3: Data 21 Warehousing, OLAP

Example of Star Schema time item time_key day item_key day_of_the_week Sales Fact Table item_name month brand time_key quarter type year supplier_type item_key branch_key location branch location_key location_key branch_key street units_sold branch_name city branch_type dollars_sold state_or_province country avg_sales Measures Lecture 3: Data 22 Warehousing, OLAP

Conceptual Modeling of Data Warehouses  Snowflake schema: A refinement of star schema where some dimensional hierarchy is normalized into a set of smaller dimension tables, forming a shape similar to snowflake It provides explicit support of hierarchy  Easier to manage the dimension  Can be less efficient (due to join) than star schema Lecture 3: Data 23 Warehousing, OLAP

Example of Snowflake Schema time item time_key item_key day supplier Sales Fact Table item_name day_of_the_week supplier_key brand month supplier_type time_key quarter type supplier_key year item_key branch_key location branch location_key location_key branch_key street units_sold branch_name city_key city branch_type dollars_sold city_key avg_sales city state_or_province Measures country Lecture 3: Data 24 Warehousing, OLAP

Conceptual Modeling of Data Warehouses  Fact constellations: Multiple fact tables share dimension tables, viewed as a collection of stars, therefore called galaxy schema or fact constellation Lecture 3: Data 25 Warehousing, OLAP