Transcript 221_OLAPNew_presentaton

On-Line Analytic
Processing
Chetan Meshram
Class Id:221
Agenda
Introduction
 Multidimensional View of OLAP Data
 Star Schemas


Examples
Slicing and Dicing
 Conclusion
 References

Introduction - OLAP
Provides quick answers to analytical
queries that are multi-dimensional in
nature.
 Generally involves highly complex queries
that use aggregations.
 OLAP or Decision-support Queries examine
large data.
 Applications: business reporting for sales,
marketing, budgeting and forecasting,
financial reporting etc.

OLAP Applications



Common OLAP application uses Warehouse of
sales data
Queries that aggregates sales into groups and
identify significant groups
Example:
Schema for Warehouse:
Sales(serialNo, date, dealer, price )
Autos(serialNo, model, color)
Dealers(name, city, state, phone)

OLAP Applications

Query:
SELECT state, AVG(price)
FROM Sales, Dealers
Where Sales.dealer = Dealers.name AND
date>= ‘2001-01-04’
Group BY state;


Query classifies recent Sales by state of the dealer and touches large
amount of data
OLTP Query:



Bank Deposists, Air Line Reservations
Touches only tiny portion of the database
Ex: Find price at which auto with serial number 123 was
sold, touches only a single tuple of data.
Multidimensional OLAP
Fact Table:
 Central relation or
collection of data
arranged in a
multidimensional
space or cube
 Dimensions: car,
dealer and date
 Point represents sale
of automobile
 Dimensions represent
properties of sale.
Multidimensional Space

Data Cube
Cars
Dealers
Date
Multidimensional OLAP

Types:

ROLAP: Relational OLAP




Data is stored in relations with a specialized structure
called ‘Star Schema’.
Fact Table contains raw or unaggregated data
Other relations contains values along each dimension
MOLAP: Multidimensional OLAP


A specialized structure called “Data Cube” is used to
hold data and its aggregates.
Nonrelational operators implemented by system.
Star Schemas
Schema for the fact table which links to
other relations called “dimension tables”.
 Fact table is at the centre of the “star”
whose points are the dimension tables.
 Fact table consists of dimensions and
dependent attributes


Ex: Sales(serialNo, date, dealer, price)


serialNo, date and dealer are dimensions
Price is dependent attribute
Star Schemas
Example:
 Dimension tables
describe values along
each dimension
 Dimension attribute of
fact table is a foreign
key of corresponding
dimension table
 Suggest possible
groupings in an SQL
GROUP BY query
Star Schema:
Star Schemas

Example:

Dimension Table:

Autos(serialNo, model, color)
Dealers(name, city, state, phone)

Fact Table:




Sales(serialNo, date, dealer, price)
serialNo is a foreign key referencing serialNo of
Autos
Autos.model and Autos.color can be used to
group sales in interesting ways.
Breakdown of sales by color, or by dealer.
Slicing and Dicing
Refers to ability to look at the database
from different viewpoints
 Performed along time axis to analyze
trends and find patterns.
 Choice of partition for each dimension
“dices” the data cube into smaller cubes
 GROUP BY and WHERE clause , a query
focuses on particular partitions.

Slicing and Dicing

Example



SELECT color, SUM(price)
FROM Sales NATURAL JOIN Autos
WHERE model = ‘Sedan’
GROUP BY color;
Query dices by color and slices by model
User Patterns and trends can be understood.
References
http://en.wikipedia.org/wiki/Online_analyt
ical_processing
 http://en.wikipedia.org/wiki/OLAP_cube
 http://www.akadia.com/services/ora_olap
_dimensions.html

Questions?