More Data Warehousing

CS257

Chris Pollett

Nov 9, 2020

Outline

Star Schema
Slicing and Dicing
Quiz
Cube and Rollup Operators
Snowflake Schema and Fact Constellation

div class="slide">

Introduction

On Wednesday, we began talking about online analytical processing (OLAP), which is the examination of data in a database for patterns or trends.
This is one of the principle uses of a data warehouse.
This examination of trends is used to understand past and future performance of a business, it goes under the moniker business intelligence.
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 data warehouse schema that is used in ROLAP scenario data warehouses known as the star schema.
We will discuss this schema in detail before looking at some other warehousing schemas.

Star Schemas

A star schema consists of the schema for the fact table, together with several other relations 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.

div class="slide">

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.
If the raw data cube is augmented with aggregations to become the formal data cube, but only involves two dimensions, and so can be drawn in a spreadsheet like format, it is sometimes called a pivot table or cross-table. This term can also be used to a particular 2D-slice of the formal data cube.

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

Quiz

Which of the following is true?

Log Structured Merge Trees are usually used as an immutable storage structure as they don't easily support deletes.
Raid 5 uses only one disk for all parity blocks.
Data warehouses are designed for high throughput OLTP situations.

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 CUBE (model, color, date, dealer);
//could also write GROUP BY model, color, date, dealer WITH CUBE

The 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;
// could also write GROUP BY ROLLUP (year ASC, country ASC, product ASC)
+------+---------+------------+--------+
| 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 |
+------+---------+------------+--------+

GROUPING SETS

The GROUPING SETS operator (supported by Postgres, Oracle, DB2) generates a result set equivalent to that generated by a UNION ALL of multiple simple GROUP BY clauses.

For example, the query:

SELECT QUARTER, REGION, SUM(SALES)
FROM SALESTABLE
GROUP BY GROUPING SETS ((QUARTER), (REGION))

is equivalent to:

SELECT QUARTER, NULL, SUM(SALES) FROM SALESTABLE
GROUP BY QUARTER
UNION ALL
SELECT NULL, REGION, SUM(SALES) FROM SALESTABLE
GROUP BY REGION

As another example, the CUBE operation:

SELECT QUARTER, REGION, SUM(SALES) 
FROM SALESTABLE
GROUP BY CUBE (QUARTER, REGION)

is equivalent to:

SELECT QUARTER, REGION, SUM(SALES)
FROM SALESTABLE
GROUP BY GROUPING SETS 
((QUARTER, REGION), (QUARTER), (REGION), ())

Snowflake Schema

Some times it useful to decompose the dimensions in our star schema if the dimension tables grow too large.
This results in a snowflake schema data warehouse such as illustrated above.
The snowflake schema can also be faster for queries on the warehouse than a star schema if the queries tend to only involve the portion of the original dimension which ends up being directly connected to the Fact Table after making a snowflake schema.
On the other hand, snowflake schemas tend to increase the number of joins needed to get data out of the warehouse.

Fact Constellation Schema

A fact constellation is a data warehouse where we have more than one fact table.
Often these fact tables may share dimensions.
For example, in the above figure we have two fact tables: Sales Fact Table and Shipping and they share the Time, Product and Store Dimensions

Specific Schema Issues

Often when building a data warehouse, we don't use the same keys for the tables as might have been used in the OLTP setting. We instead use surrogate keys which we add to the OLTP data. This allows for flexibility if the underlying OLTP key format has to change. For example, a company might originally use SSNs for employee ID and then switch to something else. If we use a surrogate keys. we don't need to worry about operational changes such as this.
A fact table that only has foreign keys is called a factless fact table. Such a table can be used to answer queries about events. For example, in the above schema, we can answer questions like:
- Which professor teaches the highest/fewest number of courses?
- What is the average number of students that attend a course?
- Which course has the maximum number of students?
To optimize the efficiency of a warehouse, the dimension tables should be indexed because they are frequently used for selection. Some dimensions with a fixed small number of tuples such as time (for a fixed window like ten years) can actually be pre-built.
Dimensions with a small number of values like booleans can be combined together into a single dimension (a so-called junk dimension) that enumerates all their possibilities.

More Schema Specific Issues

There are a variety of situations where we may add an auxiliary table off of a dimension table in a somewhat snowflaking kind of operation:
- When we have highly correlated data of low cardinality, we might do this and call it and outrigger table. This might be done to reduce duplication.
- If we have a rapidly changing dimension, we migt split it into mini dimension tables.