Data Warehousing and OLAP

Decision Support Systems • Decision-support systems are used to make business decisions, often based on data collected by online transaction-processing systems • Examples of business decisions: – What items to stock? – What insurance premium to change? – To whom to send advertisements?

• Examples of data used for making decisions – Retail sales transaction details – Customer profiles (income, age, gender, etc.) CMPT 354: Database I -- Data Warehousing and OLAP

2

Data and Statistical Analysis • Data analysis tasks are simplified by specialized tools and SQL extensions – Example tasks • For each product category and each region, what were the total sales in the last quarter and how do they compare with the same quarter last year • As above, for each product category and each customer category

• Statistical analysis packages (e.g., SAS) can be interfaced with databases CMPT 354: Database I -- Data Warehousing and OLAP

3

Data Analysis and OLAP • Online Analytical Processing (OLAP) – Interactive analysis of data, allowing data to be summarized and viewed in different ways in an online fashion (with negligible delay)

• Multidimensional data: data modeled as dimension attributes and measure attributes – Dimension attributes: define the dimensions on which measure attributes (or aggregates thereof) are viewed, e.g. the attributes item_name, color, and size of the sales relation – Measure attributes: can be aggregated upon, e.g., the attribute number of the sales relation CMPT 354: Database I -- Data Warehousing and OLAP

4

Pivot Table • • • •

Values for one of the dimension attributes form the row headers Values for another dimension attribute form the column headers Other dimension attributes are listed on top Values in individual cells are (aggregates of) the values of the dimension attributes that specify the cell

CMPT 354: Database I -- Data Warehousing and OLAP

5

Relational Representation • Cross-tabs can be represented as relations – The value all is used to represent aggregates – All represents a set – The SQL:1999 standard uses null values in place of all despite confusion with regular null values CMPT 354: Database I -- Data Warehousing and OLAP

6

Data Cubes • A data cube is a multidimensional generalization of a cross-tab • Can have n dimensions • Cross-tabs can be used as views on a data cube

CMPT 354: Database I -- Data Warehousing and OLAP

7

Online Analytical Processing • Pivoting: changing the dimensions used in a cross-tab is called • Slicing: creating a cross-tab for fixed values only – Sometimes called dicing, particularly when values for multiple dimensions are fixed

• Rollup: moving from finer-granularity data to a coarser granularity • Drill down: The opposite operation - that of moving from coarser-granularity data to finergranularity data CMPT 354: Database I -- Data Warehousing and OLAP

8

Hierarchies on Dimensions • Enable dimensions be viewed at different levels of detail – Dimension DateTime can be used to aggregate by hour of day, date, day of week, month, quarter or year

CMPT 354: Database I -- Data Warehousing and OLAP

9

Cross Tabulation With Hierarchy

CMPT 354: Database I -- Data Warehousing and OLAP

10

OLAP Implementation • Multidimensional OLAP (MOLAP) systems – Multidimensional arrays in memory to store data cubes

• Relational OLAP (ROLAP) systems – Relational tables to store data cubes

• Hybrid OLAP (HOLAP) systems – Store some summaries in memory and store the base data and other summaries in a relational database CMPT 354: Database I -- Data Warehousing and OLAP

11

Extended Aggregation in SQL:1999 • The cube operation computes union of group by’s on every subset of the specified attributes select item-name, color, size, sum(number) from sales group by cube(item-name, color, size)

• Compute the union of eight different groupings of the sales relation: { (item-name, color, size), (itemname, color), (item-name, size), (color, size), (item-name), (color), (size), ( ) } • For each grouping, the result contains the null value for attributes not present in the grouping CMPT 354: Database I -- Data Warehousing and OLAP

12

OLTP Versus OLAP OLTP

OLAP

users

clerk, IT professional

knowledge worker

function

day to day operations

decision support

DB design

application-oriented

subject-oriented

data

current, up-to-date, detailed, flat relational Isolated

historical, summarized, multidimensional integrated, consolidated

usage

repetitive

ad-hoc

access

read/write, index/hash on prim. key

lots of scans

unit of work

short, simple transaction

complex query

# records accessed

tens

millions

#users

thousands

hundreds

DB size

100MB-GB

100GB-TB

metric

transaction throughput

query throughput, response

CMPT 354: Database I -- Data Warehousing and OLAP

13

What Is a 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

CMPT 354: Database I -- Data Warehousing and OLAP

14

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 • Providing a simple and concise view around particular subject issues by excluding data that are not useful in the decision support process CMPT 354: Database I -- Data Warehousing and OLAP

15

Integrated • Integrating multiple, heterogeneous data sources – Relational databases, flat files, on-line transaction records

• Data cleaning and data integration – 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

CMPT 354: Database I -- Data Warehousing and OLAP

16

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” CMPT 354: Database I -- Data Warehousing and OLAP

17

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 CMPT 354: Database I -- Data Warehousing and OLAP

18

Data Warehousing

CMPT 354: Database I -- Data Warehousing and OLAP

19

Collecting Data • Source driven architecture: data sources transmit new information to a warehouse, either continuously or periodically (e.g. at night) • Destination driven architecture: a warehouse periodically requests new information from data sources • Keeping warehouse exactly synchronized with data sources (e.g. using two-phase commit) is too expensive – Usually OK to have slightly out-of-date data at warehouse – Data/updates are periodically downloaded form online transaction processing (OLTP) systems CMPT 354: Database I -- Data Warehousing and OLAP

20

Design Issues • Data cleansing – Correct mistakes in addresses (misspellings, zip code errors), and merge address lists from different sources and purge duplicates

• Update propagating – Warehouse schema may be a (materialized) view of schema from data sources

• Summarizing data – Raw data may be too large to store on-line – Aggregate values (totals/subtotals) often suffice – Queries on raw data can often be transformed by query optimizer to use aggregate values CMPT 354: Database I -- Data Warehousing and OLAP

21

Warehouse Schemas • Dimension values are usually encoded using small integers and mapped to full values via dimension tables • Resultant schema is called a star schema – More complicated schema structures • Snowflake schema: multiple levels of dimension tables • Constellation: multiple fact tables

CMPT 354: Database I -- Data Warehousing and OLAP

22

Data Warehouse Schema

CMPT 354: Database I -- Data Warehousing and OLAP

23

Picture from publib.boulder.ibm.com

Snowflake Schema A star schema is a snowflake schema where each dimension has only one single dimension table

CMPT 354: Database I -- Data Warehousing and OLAP

24

Why Data Mining? • Evolution of database technology – To collect a large amount of data Æ primitive file processing – To store and query data efficiently Æ DBMS

• New challenges: huge amount of data, how to analyze and understand? – Data mining

CMPT 354: Database I -- Data Warehousing and OLAP

25

What Is Data Mining? • Mining data – mining knowledge • Data mining is the non-trivial process of identifying valid, novel, potentially useful, and ultimately understandable patterns in data

CMPT 354: Database I -- Data Warehousing and OLAP

26

The KDD Process Knowledge

Transformed data Preprocessed data

Interpretation/ Patterns evaluation Data mining

Transformation

Preprocessing

Selection Target data

Data CMPT 354: Database I -- Data Warehousing and OLAP

27

KDD Process Steps • Preprocessing – Data cleaning – Data integration

• • • • •

Data selection Data transformation Data mining Pattern evaluation Knowledge presentation CMPT 354: Database I -- Data Warehousing and OLAP

28

Summary • OLAP • Data warehousing – Star schema – Snowflake schema

• Data mining and KDD process

CMPT 354: Database I -- Data Warehousing and OLAP

29