March 29, 2006

Coverage: [DBCB] Chapter 20, pp. 1070-1081

OLAP (`oh-lap')

OLAP queries (also called decision support queries) examine large amounts of data
- contrast with OLTP (`oh-el-tee-pee') queries which only touch a small portion of the database
where do warehouses fit it?
- data sources are typically distributed
- conducting OLAP queries in situ is not practical for systems which must provide fast response time to users

OLAP Applications

just about anything that involves business analysis
predicting whether new credit card applicants will pay their bill on time based on attributes (age, sex, zip code, or salary) of existing customers
applications in the medical field

consider the following relations:

Sales(isbn, date, dealer, price)
Books(isbn, author, title)
Dealers(name, city, state, phone)

the following is an example of an OLAP query (courtesy [DBCB] Example 20.12, p. 1072):
```
SELECT state, AVG(price)
FROM Sales, Dealers
WHERE Sales.dealer = Dealers.name AND date >= '2005-12-16'
GROUP BY state;
```
- average sales by state after December 16, 2005

Overview of the OLAP Approach

we call Sales the fact table
- the most central relation or collection of data
helpful to think of OLAP data in a multidimensional space

(courtesy [DBCB] Fig. 20.12, p. 1073)
- called a data cube
raw data cube vs. formal data cube
- raw data cube stores raw data, but no aggregates
- formal data cube contains
  - the raw data and aggregations of the data in all subsets of the dimensions
  - its points may represent an initial aggregation of points in the raw-data cube
two broad directions which systems supporting OLAP queries over cube-structured data have gone:
- ROLAP (Relational OLAP):
  - works from raw-data cube
  - relies on the data being stored using a star schema
- MOLAP (Multidimensional OLAP):
  - works from formal-data cube
  - involves non-relational operators over the cube-structured data

Star Schemas

attributes of fact table can be partitioned into dimension attributes and dependent attributes
- dependent attributes (e.g., price) are those over which we aggregate
- dimension attributes (e.g., isbn, date, dealer) are usually foreign keys into dimension tables (e.g., Books, Dealers, and Days)
  - attributes of the dimension tables are those which we typically group by
dimensions represent the axes of the data cube
graphical depiction of the warehouse structures
a star schema refers to the setup of the fact table (the center of the star) with its links to dimension tables (the points of the star)

Slicing and Dicing the Cube

a slice freezes part of the cube
- created by the WHERE clause
a dice partitions a slice
- created by the GROUP BY clause

a `slice-n-dice' query has the following form:

SELECT grouping attributes and aggregations
FROM fact table joined with zero or more dimension tables
WHERE certain attributes are constant
GROUP BY grouping attributes;

(courtesy [DBCB] Fig. 20.15, p. 1077)

simple slicing and dicing example:
```
SELECT used, state, SUM(price)
FROM Sales NATURAL JOIN Books
WHERE author = 'Orwell'
GROUP BY used, state;
```
- results in a table with the total sales in 100 (=50*2) categories for one author

Extended Example: Drilling Down and Rolling Up

adapted from [DBCB] Example 20.15, pp. 1076-1078

Orwell is not selling well. Let's try to see which books are not doing well:

SELECT title, SUM(price)
FROM Sales NATURAL JOIN Books
WHERE author = 'Orwell'
GROUP BY title;

Hmmm. All titles by Orwell are doing about the same. Is this just a recent trend? Let's drill-down:

SELECT title, month, SUM(price)
FROM (Sales NATURAL JOIN Books) JOIN Days ON date = day
WHERE author = 'Orwell'
GROUP BY title, month;

Wow. Animal Farm has not sold well recently. Are any particular dealers not pulling their weight? Let's drill-down further:

SELECT dealer, month, SUM(price)
FROM (Sales NATURAL JOIN Books) JOIN Days ON date = day
WHERE author = 'Orwell' AND title = 'Animal Farm'
GROUP BY month, dealer;

Sales of Animal Farm by month are so small that no real trends are observable. Let's partition only by years (roll-up) and look at only the previous two:

SELECT dealer, year, SUM(price)
FROM (Sales NATURAL JOIN Books) JOIN Days ON date = day
WHERE author = 'Orwell' AND title = 'Animal Farm' AND
               (year = 2004 OR year = 2005)
GROUP BY year, dealer;

The Cube Operator

precomputes all possible subsets of aggregates in a systematic way
- resemble a technique we learnt? dynamic programming?
the cube operator takes the fact table from the raw data cube and returns an augmented fact table
- CUBE(F)
- adds the * value to each dimension
  - * is a wildcard for any
  - * represents an aggregation along the dimension in which it appears
  - new tuples have the form: . . . * . * | <dependent attributes>
graphical depiction of formal data cube

References

Database Systems: The Complete Book