On-Line Analytic Processing

1. On-Line Analytic Processing Chetan Meshram Class Id:221

2. Agenda • Introduction • Multidimensional View of OLAP Data • Star Schemas • Examples • Slicing and Dicing • References

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

4. 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)

5. 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 :Online Transaction Processing • Bank Deposits, 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.

6. Cars Dealers Date Multidimensional OLAP Fact Table: Multidimensional Space Data Cube • 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.

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

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

9. Star Schemas Example: Star Schema: • 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

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

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

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

13. References • http://en.wikipedia.org/wiki/Online_analytical_processing • http://en.wikipedia.org/wiki/OLAP_cube • http://www.akadia.com/services/ora_olap_dimensions.html

14. Questions?