Introduction

On Monday, we began talking about online analytical processing (OLAP), which is the examination of data in a database for patterns or trends.
We said that OLAP tends to involve more complicated queries, called decision support queries, than what is used for online transaction processing (OLTP), the kind of transaction processing like ATM debits, etc. we have been considering so far.
We gave an example of a data warehouse with three tables Sales, Autos, Dealers and then considered the query to return by state, the average sale prices of cars sold after some date.
We said this was an example of a decision support query that might touch a lot of this database.
The Sales table in our schema might be referred to as a fact table, as it is the central relation of this warehouse, and as we can view the rows in this table as points in a multidimensional space called a raw data cube.
We said if we augmented this cube with aggregates of the data for all subsets of the dimensions, as well as if we did some pre-aggregation, such as aggregating cars by model rather than leave as individual cars, we get a so-called formal data cube.
Finally, we said that the two main approaches to OLAP are: ROLAP (relational OLAP), which tries to do OLAP in a traditional relation DBMS using the raw data cube, and MOLAP (multidimensional OLAP) which uses a non-relational DBMS specialized to spatial queries on a formal data cube.
Today, we begin by looking at a common schema that is used in ROLAP scenario data warehouses known as the star schema.

Star Schemas

A star schema consists of the schema for the fact table, together with several other relation called dimension tables which are linked to via foreign key relationships.
As we can see in the above figure, the fact table is at the center of a star shaped database schema with the points of the star being the schemas of the dimension tables.
In our cars example, the Sales(serialNo, date, dealer, price) table was a fact table with three dimensions: serialNo, date, dealer.
It also had one dependent attribute, price, which is what a typical OLAP query to this database would ask for in aggregation.
The dimension tables in this case would be Autos(serialNo, model, color), Dealers(name, city, state, phone), and a non-materialized dimension table, Days(day, week, month, year).
Attributes of the dimension table are often used for groupings in a SQL GROUP BY query. For example, we might want to group average price per dealer by state and car color.
Days would typically not be an actual table in our schema, but we might use SQL time functions to do various GROUP BY's on dates as if we had such a table.

In-Class Exercise

At a school, a student often takes courses from professors who work in departments which belong to colleges contained in the whole university.
Students want to know their GPAs, policy makers want to know average GPAs or class grades at different organizational levels, for courses within disciplines, etc.
For the above situation, suggest a star schema. I.e., suggest a fact table and associated dimension tables for this situation.
Please post your solution to the May 6 In-Class Exercise Thread.

Slicing and Dicing Data

We can think of the points of the raw-data cube as being partitioned in each dimension at some level of granularity.
For example, we could imagine partitioning time (using a GROUP BY clause) by day, by week, by month, by year, etc. Or we could choose not to partition at all.
For Dealers we might choose to partition by city, by state, or choose not to partition.
A choice of partition for each dimension of the cube is called a dicing of the cube.
The figure on the left above illustrates a possible dicing of the Sales data cube.
Dicing is typically implemented by SQL queries that perform aggregates together with a GROUP BY clause.
If we are using dicing and decide to switch from a coarse partition to a finer one, we are said to be drilling down into the data; going from a finer partition to a coarser one is called rolling up the data.
We can use a WHERE clause to select a particular partition bin, such as in the figure on the right above. This is called slicing.

Example of Slicing and Dicing

Consider the query:

SELECT color, SUM(price)
FROM Sales NATURAL JOIN Autos
WHERE model = 'Gobi'
GROUP BY color

In the above, the dicing makes use of the Autos dimension table and is by color.
The slicing also makes use of the Auto dimension table and selects for model='Gobi'.

The Cube Operator

How a cube operator augments a data cube

We now look at how the raw data cube is augmented to make a formal data cube.
Given a fact table `F`, we define the augmented table CUBE(F) that adds to each dimension an additional value, denoted by `star`.
The intended meaning of `star` is "ANY" and the value for a tuple with `star` in a column is assumed to be aggregated amongst all values that it implies.
For example, any tuple in Sales would also be in CUBE(Sales).
So the tuple:
```
('Gobi', 'red', '2011-05-21', 'Good Deal Joe', 30000, 2)
```
which represents that dealer Good Deal Cars sold two red Gobi's on May 21, 2011 for a total of $30,000, would be in CUBE(Sales).
CUBE(Sales) would also contain tuples like:
```
('Gobi', *, '2011-05-21', 'Good Deal Cars', 150000, 7)
```
Indicating 7 sales totaling $150,000 on May 21, 2011 of Gobi's of any color at Good Deal Cars.
```
('Gobi', *, '2011-05-21', *, 2050000, 30)
```
Indicating 30 sales totaling $2,050,000 on May 21, 2011 of Gobi's of any color at any dealer.
```
(*, *, *, *, 31067005, 210)
```
Indicating 210 sales totaling $31,067,005 of any model in any color at any dealer for all time.

SQL CUBE and ROLLUP operations

Since SQL 1999, SQL supports two operations, CUBE and ROLLUP, that facilitates OLAP queries.
Most commercial DBs have support for these operations: DB2, Oracle, MS SQL Server.
MySql supports the ROLLUP operation.
If you have support for the CUBE operation in group by clauses and have support for materialized views, you could imagine creating a formal cube from a raw data cube with a command like:
```
CREATE MATERIALIZED VIEW SalesCube AS
    SELECT model, color, date, dealer, SUM(val), SUM(cnt)
    FROM Sales
    GROUP BY model, color, date, dealer WITH CUBE;
```
The WITH CUBE operator adds additional rows to the result of the select above for each different set of columns we could aggregate by.
In SQL, rather than use `star`, the aggregated columns have NULL for their values. For example,
```
('Gobi', NULL, '2011-05-21', 'Good Deal Cars', 150000, 7)
```
If your DBMS doesn't support materialized views, but does support WITH CUBE., you can fake the above by doing a CREATE TABLE SalesCube, then doing an INSERT ... SELECT statement.

Mysql ROLLUP operations

Mysql supports a special sub-case of CUBE called ROLLUP.

The two queries below illustrate the kinds of aggregation that happen when ROLLUP is used in Mysql:
First without ROLLUP

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year ASC, country ASC, product ASC;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
+------+---------+------------+--------+

Next with ROLLUP

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year ASC, country ASC, product ASC WITH ROLLUP;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | India   | NULL       |   1350 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2000 | USA     | NULL       |   1575 |
| 2000 | NULL    | NULL       |   4525 |
| 2001 | Finland | Phone      |     10 |
| 2001 | Finland | NULL       |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
| 2001 | USA     | NULL       |   3000 |
| 2001 | NULL    | NULL       |   3010 |
| NULL | NULL    | NULL       |   7535 |
+------+---------+------------+--------+

Data Mining

Data Mining is the process of examining data and finding simple rules or models that summarize the data.
An example rule we might discover could be "50% of the people who buy hot dogs also buy mustard".
In order to discover such a rule we might look at customer purchases.
In the Market Basket model we assume their is a set of items people might purchase together in a basket at a store.
Market Basket data might appear from data that is collected at a supermarket checkout.
Each sale at the checkout itemizes the goods bought by one purchaser at one time. Further, some purchasers belong to loyalty programs, and so we might also be able to identify additional information about that purchaser in addition to the sale items.
Market Basket data can also appear from data in online purchases as often one has an online cart that is first filled with items that are then later purchased.
A store can make use of such rules by doing things like putting frequently co-purchased items closer together in the store.

Frequent Sets of Items

Let `I` be a set of items, a basket `b subset I` is any subset of `I`.
Let `B` be a set of baskets from `I`.
A positive `s gt 0` is called a support threshold.
We say a set of items `J subset I` is a frequent with respect to a support threshold s, if there are at least `s` baskets containing `J`.
Above we have a table of eight viewers and six movies (Bourne Movies + Some Harry Potter), showing which movies those viewers saw.
Suppose s=3. Then all single movies except HP3 are frequent.
{BI,HP1}, {BS, HP1}, {HP1, HP2} are the two item frequent sets.
{BI, BS, HP1} is the only three item frequent set.

A Priori Algorithm

The A Priori Algorithm (Rakesh Agrawal and Ramakrishnan Srikant 1994) is a famous algorithm for computing frequent item sets.

It operates as follows:

INPUT: A file D consisting of baskets of items, a support threshold s, 
    and a size limit q for the size of frequent itemsets.
OUTPUT: The sets of itemsets F[1],F[2],..., F[q], where F[i] is the 
    set of all itemsets of size i that appear in at least s baskets of D.
METHOD:
(1) LET C[1] = all items that appear in file D;
(2) For n = 1 to q:
(3)    F[n] = those sets in C[n] that occur at least s times in D
(4)    IF n==q BREAK;
(5)    LET C[n+1] = all itemsets of S of size n+1 such that every
           subset S of size n is in F[n].

Finish OLAP - Data Mining

Outline

Introduction

Star Schemas

In-Class Exercise

Slicing and Dicing Data

Example of Slicing and Dicing

The Cube Operator

SQL CUBE and ROLLUP operations

Mysql ROLLUP operations

Data Mining

Frequent Sets of Items

A Priori Algorithm