Introduction

Last week, 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.

Quiz

Which of the following statements is true?

It is possible for different distributed transactions in the same distributed database to use a different machine as the lock coordinator.
A federated database is a software component that supports a virtual database, which the user may query. It stores no data itself, but instead, queries its sources and synthesizes from them a response.
A raw data cube would contain aggregates of the data in all subsets of dimensions, as well as the data itself.

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 Autos 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 there 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}, {BI, BS}, {BI, BU}, {BS, HP1}, {HP1, HP2} are the two item frequent sets.
{BI, BS, HP1} is the only three item frequent set.

Association Rules

A natural query about market-basket data is to ask for implications among purchases that people make.
This is the idea that association rules try to formalize.
An association rule is a statement of the form `\{i_1, i_2, ..., i_n\} => j` where the `i_k`'s and `j` are items.
To be useful, an association rule should satisfy:
1. High Support: The support of an association rule `\{i_1, i_2, ..., i_n\} => j` is the number of occurrences of the set `{i_1, i_2, ..., i_n, j}` in the database. We want this to be high so the rule has a good chance to applied.
2. High Confidence: The probability of finding item `j` in a basket that has all of `\{i_1, i_2, ..., i_n\}` is above some threshold. For example, "50% of the p eople who buy diapers buy beer."
3. Interest: The probability of finding item `j` in a basket that has all of `\{i_1, i_2, ..., i_n\}` is significantly higher or lower than the probability of `j` in a random basket. I.e., `j` correlates with `\{i_1, i_2, ..., i_n\}` either positively or negatively. A rule like `{diapers} => beer` is a claim that the association rule has high interest in the positive direction.
Even if an association rule has high confidence or interest, it will tend not to b e useful unless it also has high support.
This is because if the support is low the number of instances of the rule is not large, which limits the benefit of a strategy that exploits the rule.
So a crucial step in determining association rules is to find frequent item sets. I.e., those with high support.

The Computation Model for Frequent Itemsets

In a perfect world, the market-basket data might be stored in a relation:
Baskets(basket, item)
consisting of pairs (some_basket_id, some_item_id).

We could then compute frequent itemsets using a query like:

SELECT I.item, J.item, COUNT(I.basket) 
FROM Baskets I, Baskets J
WHERE I.basket = J.basket AND
I.item < J.item 
GROUP BY I.item, J.item
HAVING COUNT(I.basket) >= s;

Typically, though, the Baskets data set is huge and so the join above will be too large and too time consuming to compute.
For example, if there are a 1,000,000 baskets each with around 20 items, then the join would have around 190,000,000 tuples as `((20),(2)) = 190`.
Market-basket data is not usually stored as a relation. Instead, it is more common to put the data in a file or files consisting of the baskets in some order. A basket is represented by a list of its items and there is punctuation between backets.
For example, {BI,BS,BU}{HP1,HP2,HP3}{BI,HP1}{BI,BS,HP1,HP2}{... might be used to represent the data of a couple slides ago.
Usually, the cost of a frequent item set algorithm is then measured as the number of passes or through such a file.

Naive Algorithms for Frequent Item Sets

To use a marketing technique based on "People who buy X,..., also buy Y", we need to look at itemsets of size at least 2.
To ensure we have enough customers buying a given set, we need to use a support threshold that is reasonbly high, say at least 1% of the baskets.
If larger than size 2 itemsets are desired, the computing resources used to find the frequent pairs are usually sufficient to find the small number of frequent triples, quadruples, and so on.
Suppose we have `M` bytes of memory, and items `{0, ..., k-1}` which can appear in baskets.
As we scan the file of baskets, we want to keep counts of the different pairs we see. I.e., scan a basket from file, for each pair in basket, and 1 to its count.
We can either store pairs using an upper triangular matrix where the (i,j) th entry says the count of the pair {i, j} or using a hash table with key (i,j) and value its count.
Let `p` be the number of pairs. If int's are 4 bytes, then a hash table entry is 12 bytes.
So for this kind of naive algorithm to work, we need `M > 4 \times k^2/2 = 2k^2` if an upper triangular matrix is used, and `M > 12p` if a hash table is used.
If these conditions are met, one pass over the data set is needed.

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].

A Priori Example

Suppose `s = 4`.
If we run the A Priori algorithm on the data set a couple slides back then by line (1) and (3): F[1] := `{\{BI}, {BS}, {HP1}, {HP2}}` as those are columns with at least 4 items.
In the second pass through the FOR loop, we want to augment each set in F[1] by each possible additional element (we could restrict to element which we know have `>=4` occurrences) and see if the result still has more than 4 occurrences.
This gives F[2]:
`{\{BI, BS}, \{HP1, HP2}, \{BI, HP1}, \{BS, HP1}}`
Finally, C[3] only contains `{BI, BS, HP1}` and this does not have 4 elements. So only F[1] and F[2] will be returned as F[n], `n > 2` are empty.
On Wednesday, we will look at how efficient this algorithm is, and optimizations for it.

Finish OLAP - Data Mining

Outline