1 / 14

On-Line Analytic Processing

On-Line Analytic Processing. Chetan Meshram Class Id:221. Agenda. Introduction Multidimensional View of OLAP Data Star Schemas Examples Slicing and Dicing References. Introduction - OLAP. Provides quick answers to analytical queries that are multi-dimensional in nature.

Télécharger la présentation

On-Line Analytic Processing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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?

More Related