Two broad types of database processing
OLTP (Online Transaction Processing)
– Short transactions
– simple queries
– touch small portions of data
– frequent updates
OLAP (Online Analytical Processing)
– Long transactions
– complex queries
– touch large portions of data
– infrequent updates

More terminology
Data warehousing
– bring data from operational (OLTP) sources into a single warehouse for analysis
Decision support system (DDS)
– infrastructure for data analysis

“Star” Schema
Fact table
– updated frequently, often append-only, very large
Dimension tables
– updated infrequently, not as large
Fact table references dimension tables.

Example:
Sales(storeID, itemID, custID, qty, price) – Fact table
Store(storeID, city, state) – Dimension table
Item(itemID, category, brand, color, size) – Dimension table
Customer(custID, name, address) – Dimension table

Foreign keys into dimension tables are called dimension attributes. Other attributes are dependent attributes.

OLAP queries
Join -> Filter -> Group -> Aggregate

Performance
– inherently very slow: special indexes, query processing techniques
– extensive use of materialized views

Data Cube (a.k.a. multidimensional OLAP)
– dimension data forms axes of “cube”
– fact (dependent) data in cells
– aggregated data on sides, edges, corner

Fact table uniqueness for data cube
Sales(storeID, itemID, custID, qty, price)
If dimension attributes not a key, must aggregate
Date can be used to create key
– Dimension or dependent? dimension, but no table

Drill down
Examining summary data, break out by dimension attribute

Example:
Select state, brand, Sum(qty*price)
From Sales F, Store S, Item If
Where F.storeID = S.storeID and F.itemID = I.itemID
Group By state, category, brand

Roll-up
Examining data, summarize by dimension attribute

Example:
Select brand, Sum(qty*price)
From Sales F, Store S, Item If
Where F.storeID = S.storeID and F.itemID = I.itemID
Group By brand

SQL Constructs

With Cube
Select dimension-attrs, aggregates
From tables
Where conditions
Group By dimension-attrs With Cube
Add to result: faces, edges and corner of cube using NULL values

With Rollup
Select dimension-attrs, aggregates
From tables
Where conditions
Group By dimension-attrs With Rollup
For hierarchical dimensions, portion of With Cube

DEMO

Star Join

select *
from Sales F, Store S, Item I, Customer C
where F.storeID = S.storeID and F.itemID = I.itemID and F.custID = C.custID;

select S.city, I.color, C.cName, F.price
from Sales F, Store S, Item I, Customer C
where F.storeID = S.storeID and F.itemID = I.ItemID and F.custID = C.custID and S.state = ‘CA’ and I.category = ‘Tshirt’ and C.age < 22 and F.price < 25; Drilling Down select storeID, custID, sum(price) from Sales group by storeID, custID; select storeID, itemID, custID, sum(price) from Sales group by storeID, itemID, custID; select state, county, category, sum(price) from Sales F, Store S, Item I where F.storeID = S.storeID and F.itemID = I.itemID group by state, county, category; select state, county, category, gender, sum(price) from Sales F, Store S, Item I, Customer C where F.storeID = S.storeID and F.itemID = I.itemID group by state, county, category, gender; Slicing A query that analyzes a slice of the cube by contraining one of the dimensions. select F.storeID, itemID, custID, sum(price) from Sales F, Store S wher F.storeID = S.storeID and state = 'WA' group by F.storeID, itemID, custID; Dicing Slice in two dimensions to get a chunk of the Cube select F.storeID, F.itemID, custID, sum(price) from Sales F, Store S, Item I wher F.storeID = S.storeID and state = 'WA' and color = 'red' group by F.storeID, F.itemID, custID; Rolling Up select itemID, sum(price) from Sales F group by itemID; select state, category, sum(price) from Sales F, Store S, Item I where F.storeID = S.storeID and F.itemID = I.itemID group by state, category; select state, gender, sum(price) from Sales F, Store S, Customer C where F.storeID = S.storeID group by state, gender; With Cube select storeID, itemID, custID, sum(price) from Sales group by storeID, itemID, custID with cube; select storeID, itemID, custID, sum(price) from Sales group by storeID, itemID, custID with cube(storeID, custID); shows NULLs (ex: sales for store1, item1, any customer) Can create Cube table to query Cube directly gives pre-aggregated data select C.* from Cube C, Store S, Item I where c.storeID = S.storeID and C.itemID = I.itemID and state = 'CA' and color = 'blue' and custID is null; select C.* from Cube C, Store S, Item I where c.storeID = S.storeID and C.itemID = I.itemID and state = 'CA' and color = 'blue' and custID is not null; no summarized data With Rollup select storeID, itemID, custID, sum(price) from Sales F group by storeID, itemID, custID with rollup subsection of cube based on group by attributes best for hierarchical analysis select state, county, city, sum(price) from Sales F, Store S where F.storeID = S.storeID group by state, county, city with rollup