OLAP (Online Analytical Processing) : 10/24/2008 1 OLAP (Online Analytical Processing) Architecture
ROLAP VS. MOLAP What Is Data Warehouse? : 10/24/2008 2 What Is Data Warehouse? consolidates the information from different data sources, enabling OLAP (online analytical processing), to help decision support.
is maintained separately from an operational database (which is used for OLTP – online transaction processing). OLAP(Online Analytical Processing) : 10/24/2008 Sudarshan 3 OLAP(Online Analytical Processing) Slide 4: 10/24/2008 4 Multi-Tiered Architecture Data
Warehouse OLAP Engine Analysis
Data mining Monitor
Integrator Metadata Data Sources Front-End Tools Serve Data Marts Data Storage OLAP Server What is OLAP? : 10/24/2008 5 What is OLAP? On-Line Analytical Processing
Information technology to help the knowledge worker (executive, manager, analyst) make faster and better decisions.
OLAP is an element of decision support systems OLAP : 10/24/2008 6 OLAP Create an advanced data analysis environment that supports decision making, business modeling and operation research activities.
Characteristics of OLAP
Use multidimensional data analysis technique
Provide advance database support
Provide easy-to-use end user interfaces.
Support client/server architecture. Two types of database activity : 10/24/2008 7 Two types of database activity OLTP and OLAP
OLTP: On-Line Transaction Processing
Short transactions, both queries and updates
(e.g., update account balance, enroll in course)
Queries are simple
(e.g., find account balance, find grade in course)
Updates are frequent
(e.g., concert tickets, seat reservations, shopping carts) OLAP: On-Line Analytical Processing : 10/24/2008 8 OLAP: On-Line Analytical Processing Long transactions, usually complex queries
(e.g., all statistics about all sales, grouped by dept and month)
“Data mining” operations
Infrequent updates OLTP Compared With OLAP : 10/24/2008 9 OLTP Compared With OLAP On Line Transaction Processing – OLTP
– Maintain a database that is an accurate model of some real-world enterprise
• Short simple transactions
• Relatively frequent updates
• Transactions access only a small fraction of the database On Line Analytical Processing - OLAP
– Use information in database to guide strategic decisions
• Complex aggregation queries
• Infrequent updates
• Transactions access a large fraction of the database Slide 10: 10/24/2008 10 RELATIONAL OLAP : 10/24/2008 Sudarshan 11 RELATIONAL OLAP Provides functionality by using relational databases and relational query tools to store and analyze multidimensional data.
Build on existing relational technologies and represents extension to all those companies that already used RDBMS
ROLAP adds the following extensions to traditional RDBMS
Multidimensional data schema support within the RDBMS
Data access language and query performance are optimized for multidimensional data.
Support for very large data bases Multidimensional OLAP : 10/24/2008 12 Multidimensional OLAP MOLAP extends OLAP functionality to MDBMS
Best suited to manage, store or analyze multidimensional data.
Proprietary techniques used in MDBMS.
MDBMS and users visualize the stored data as a 3-dimensional cube i.e data cube.
MOLAP data bases are known to be much faster than their ROLAP counter parts.
Data cubes are held in memory called “cube cache”. ROLAP vs MOLAP : 10/24/2008 13 ROLAP vs MOLAP ROLAP vs MOLAP : 10/24/2008 14 ROLAP vs MOLAP Implementation of the OLAP Server : 10/24/2008 15 Implementation of the OLAP Server ROLAP: Relational OLAP – data is stored in
tables in relational database or extended relational databases. They use an RDBMS to manage the warehouse data and aggregations using often a star schema.
• They support extensions to SQL.
Disadvantage: No direct access to cells. Implementation of the OLAP Server : 10/24/2008 16 Implementation of the OLAP Server MOLAP:Multidimensional OLAP - implements the
multidimensional view by storing data in special multidimensional data structures.
Advantage:Fast indexing to pre-computed aggregations.
Only values are stored.
Disadvantage: Not very scalable.
• Characteristics of OLAP : 10/24/2008 17 Characteristics of OLAP Fast - means that the system targeted to deliver most responses
to user within about five second, with the simplest analysis taking no more than one second and very few taking more than 20 sec.
Share - means that the system implements all the security requirements for confidentiality and, if multiple write access is needed, concurrent update location at an appropriated level not all applications need users to write data back, but for the growing number that do, the system should be able to handle multiple updates in a timely, secure manner. Slide 18: 10/24/2008 18 Analysis - means that the system can cope with any business logic and statistical analysis that it relevant for the application and the user, keep it easy enough for the target user. Although some pre programming may be needed we do not think it acceptable if all application definitions have to be allow the user to define new adhoc calculations as part of the analysis and to report on the data in any desired way, without having to program so we exclude products (like Oracle Discoverer) that do not allow the user to define new adhoc calculation as part of the analysis and to report on the data in any desired product that do not allow adequate end user oriented calculation flexibility. Slide 19: 10/24/2008 19 Multidimensional - is the key requirement. OLAP system must provide a multidimensional conceptual view of the data, including full support for hierarchies, as this is certainly the most logical way to analyze business and organizations.
Information - are all of the data and derived information needed? Wherever it is and however much is relevant for the application. We are measuring the capacity of various products in terms of how much input data they can handle, not how many gigabytes they take to store it. Slide 20: 10/24/2008 20 What appears to be the end may really be a new beginning.