1 / 14

On-Line Analytical Processing ( OLAP )

Learn the basics of OLAP, differentiating it from OLTP, exploring terminology like star schema and data cube, and understanding OLAP queries and SQL constructs. Dive into drill-down, roll-up, and more.

hobgood
Download Presentation

On-Line Analytical Processing ( OLAP )

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 Analytical Processing (OLAP) Introduction

  2. OLAP: Intro Two broad types of database activity • 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 the data • Infrequent updates

  3. OLAP: Intro More terminology • Data warehousing Bring data from operational (OLTP) sources into a single “warehouse” for (OLAP) analysis • Decision support system (DSS) Infrastructure for data analysis E.g., data warehouse tuned for OLAP

  4. OLAP: Intro “Star Schema” • Fact table Updated frequently, often append-only, very large • Dimension tables Updated infrequently, not as large

  5. OLAP: Intro Star Schema – fact table references dimension tables Sales(storeID,itemID,custID,qty,price) Store(storeID,city,state) Item(itemID,category,brand,color,size) Customer(custID,name,address)

  6. OLAP: Intro OLAP queries Join  Filter  Group  Aggregate Performance • Inherently very slow: specialindexes, query processing techniques • Extensive use of materialized views Sales(storeID,itemID,custID,qty,price) Store(storeID,city,state) Item(itemID,category,brand,color,size) Customer(custID,name,address)

  7. OLAP: Intro Data Cube (a.k.a. multidimensional OLAP) • Dimension data forms axes of “cube” • Fact (dependent) data in cells • Aggregated data on sides, edges, corner

  8. OLAP: Intro Fact table uniqueness for data cube • If dimension attributes not key, must aggregate • Date can be used to create key Dimension or dependent? Sales(storeID,itemID,custID,qty,price)

  9. OLAP: Intro Drill-down and Roll-up

  10. OLAP: Intro Drill-down and Roll-up Examining summary data, break out by dimension attribute Select state,brand, Sum(qty*price) From Sales F,Store S, Item I Where F.storeID=S.storeIDAnd F.itemID=I.itemID Group By state, brand

  11. OLAP: Intro Drill-down and Roll-up Examining data, summarize by dimension attribute Select state,brand, Sum(qty*price) From Sales F,Store S, Item I Where F.storeID=S.storeIDAnd F.itemID=I.itemID Group By state, brand

  12. OLAP: Intro SQL Constructs With Cube and With Rollup Add to result: faces, edges, and corner of cube using NULL values Select dimension-attrs, aggregates From tables Where conditions GroupBy dimension-attrs WithCube

  13. OLAP: Intro SQL Constructs With Cube and With Rollup For hierarchical dimensions, portion of WithCube Select dimension-attrs, aggregates From tables Where conditions GroupBy dimension-attrs WithRollup

  14. OLAP: Intro Two broad types of database activity • 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 the data • Infrequent updates • Star schemas • Data cubes • With Cube and With Rollup • Special indexes and query processing techniques

More Related