1 / 12

Data Warehousing

Virtual University of Pakistan. Data Warehousing. Lecture-10 Online Analytical Processing (OLAP). Ahsan Abdullah Assoc. Prof. & Head Center for Agro-Informatics Research www.nu.edu.pk/cairindex.asp National University of Computers & Emerging Sciences, Islamabad Email: ahsan@cluxing.com.

axel-leblanc
Download Presentation

Data Warehousing

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. Virtual University of Pakistan Data Warehousing Lecture-10 Online Analytical Processing (OLAP) Ahsan Abdullah Assoc. Prof. & Head Center for Agro-Informatics Research www.nu.edu.pk/cairindex.asp National University of Computers & Emerging Sciences, Islamabad Email: ahsan@cluxing.com Ahsan Abdullah

  2. DWH & OLAP • Relationship between DWH & OLAP • Data Warehouse & OLAP go together. • Analysis supported by OLAP Ahsan Abdullah

  3. What was themonthly sale for lastquarter group by region Supporting the human thought process THOUGHT PROCESS QUERY SEQUENCE An enterprise wide fall in profit What was thequarterly sales during last year ?? How many such query sequences can be programmed in advance?  ? What was thequarterly sales at regionallevel during lastyear ?? Profit down by a large percentage consistently during last quarter only. Rest is OK What was thequarterly sales at productlevel during lastyear? What is special about last quarter ? What was themonthly sale for lastquarter group by products Products alone doing OK, but North region is most problematic. OK. So the problem is the high cost of products purchased in north. What was themonthly sale of products in north at store level group by products purchased Ahsan Abdullah

  4. More in subsequent slides Analysis of last example • Analysis is Ad-hoc • Analysis is interactive (user driven) • Analysis is iterative • Answer to one question leads to a dozen more • Analysis is directional • Drill Down • Roll Up • Pivot Ahsan Abdullah

  5. Challenges… • Not feasible to write predefined queries. • Fails to remain user_driven (becomes programmer driven). • Fails to remain ad_hoc and hence is not interactive. • Enable ad-hoc query support • Business user can not build his/her own queries (does not know SQL, should not know it). • On_the_go SQL generation and execution too slow. Ahsan Abdullah

  6. Challenges • Contradiction • Want to compute answers in advance, but don't know the questions • Solution • Compute answers to “all” possible “queries”. But how? • NOTE: Queries are multidimensional aggregates at some level Ahsan Abdullah

  7. ALL ALL ALL ALL Province Frontier ... Punjab Division Mardan ... Peshawar Lahore ... Multan District Peshawar Lahore City Lahore ... Gugranwala Zone ... Defense Gulberg “All” possible queries (level aggregates) Ahsan Abdullah

  8. OLAP: Facts & Dimensions • FACTS: Quantitative values (numbers) or “measures.” • e.g., units sold, sales $, Co, Kg etc. • DIMENSIONS: Descriptive categories. • e.g., time, geography, product etc. • DIM often organized in hierarchies representing levels of detail in the data (e.g., week, month, quarter, year, decade etc.). Ahsan Abdullah

  9. Where Does OLAP Fit In? • It is a classification of applications, NOT a database design technique. • Analytical processing uses multi-level aggregates, instead of record level access. • Objective is to support very • fast • iterative and • ad-hoc decision-making. Ahsan Abdullah

  10. Data Loading Reports  Transaction Data OLAP Data Cube (MOLAP) Presentation Tools Where does OLAP fit in?  ? Decision Maker Ahsan Abdullah

  11. OLTP vs. OLAP Ahsan Abdullah

  12. OLAP FASMI Test Fast: Delivers information to the user at a fairly constant rate. Most queries answered in under five seconds. Analysis: Performs basic numerical and statistical analysis of the data, pre-defined by an application developer or defined ad-hocly by the user. Shared: Implements the security requirements necessary for sharing potentially confidential data across a large user population. Multi-dimensional: The essential characteristic of OLAP. Information: Accesses all the data and information necessary and relevant for the application, wherever it may reside and not limited by volume. ...from the OLAP Report by Pendse and Creeth. Ahsan Abdullah

More Related