OLAP (Online Analytical Processing)
UNIT-III Part 2
UNIT-III
Overview of Data warehouse and OLAP technology, Types of OLAP Servers, Multi-dimensional data model, data warehouse architectures, Data Warehouse Implementation, concept of data warehousing to data mining.
OVERVIEW
INTRODUCTION
OLAP CUBE
HISTORY OF OLAP
OLAP OPERATIONS
DATAWAREHOUSE ARCHITECHTURE
DIFFERENCE BETWEEN OLAP &
OLTP
TYPES OF OLAP
APPLICATIONS OF OLAP
INTRODUCTION TO OLAP
OLAP (online analytical processing) is computer processing that enables a user to easily and selectively extract the data from different points of view.
OLAP allows users to analyze database information from multiple database systems at one time.
OLAP data is stored in multidimensional
databases.
AN EXAMPLE…
Some popular OLAP server software programs include:
Oracle Express Server
Hyperion Solutions Essbase
OLAP processing is often used for data mining.
OLAP products are typically designed for multiple-user environments, with the cost of the software based on the number of users.
THE OLAP CUBE
An OLAP cube is a multi-dimensional array of data OR simply a data structure.
Online analytical processing (OLAP) is a
computer-based technique of analyzing data to look for insights.
The term cube here refers to a multi-dimensional dataset, which is also sometimes called a hypercube if the number of dimensions is greater than 3.
For example, a company might wish to summarize financial data by product, by time-period, and by city to compare actual and budget expenses. Product, time, city and scenario (actual and budget) are the data's dimensions
OLAP CUBE
HISTORY OF OLAP
The term OLAP was created as a slight modification of the traditional database term OLTP (Online Transaction Processing).
Databases configured for OLAP employ a multidimensional data model, allowing for complex analytical and ad-hoc queries with a rapid execution time.
Nigel Pendse has suggested that an alternative and perhaps more descriptive term to describe the concept of OLAP is Fast Analysis of Shared Multidimensional Information (FASMI).
OLAP OPERATIONS
There are five basic analytical operations that can be performed on an OLAP cube:
1.Drill down 2.Roll up
3.Dice 4.Slice 5.Pivot
Drill Down operation
In drill-down operation, the less detailed data is converted into highly detailed data. It can be done by:
• Moving down in the concept hierarchy
• Adding a new dimension
• In the cube given in overview section, the drill down operation is performed by moving down in the concept hierarchy of Time dimension (Quarter -> Month).
Roll Up operation
It is just opposite of the drill-down operation. It performs aggregation on the OLAP cube. It can be done by:
• Climbing up in the concept hierarchy
• Reducing the dimensions
• In the cube given in the overview section, the roll-up operation is performed by climbing up in the concept hierarchy of Location dimension (City -> Country).
Dice operation
It selects a sub-cube from the OLAP cube by selecting two or more dimensions. In the cube given in the overview section, a sub-cube is selected by selecting following dimensions with criteria:Location = “Delhi” or “Kolkata”
Time = “Q1” or “Q2”
Item = “Car” or “Bus”
Slice operation
It selects a single dimension from the OLAP cube which results in a new sub-cube creation. In the cube given in the overview section, Slice is performed on the dimension Time
= “Q1”.
Pivot operation
It is also known as rotation operation as it rotates the current view to get a new view of the representation. In the sub-cube obtained after the slice operation, performing pivot operation gives a new view of it.
TYPES OF OLAP
Relational OLAP(ROLAP):
To store and manage warehouse data, ROLAP uses relational or extended-relational DBMS(DBMS).
Multidimensional OLAP(MOLAP): MOLAP uses array-based multidimensional storage engines for multidimensional views of data. With multidimensional data stores, the storage utilization may be low if the data set is sparse.
• Hybrid Online Analytical Processing (HOLAP)
Hybrid OLAP is a combination of both ROLAP and MOLAP. It offers higher scalability of ROLAP and faster computation of MOLAP. HOLAP servers allows to store the large data volumes of detailed information
Relational OLAP
Provides functionality by using relational databases and relational query tools to store and analyze multidimensional data.
Build on existing relational technologies and represent extension to all those companies who already used RDBMS.
Multidimensional data schema support within the RDBMS.
Data access language and query performance are optimized for multidimensional data.
Support for very large databases.
Multidimensional OLAP
MOLAP extends OLAP functionality to MDBMS.
Best suited to manage, store and 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 Databases are known to be much faster than the ROLAP counter parts.
ROLAP v/s MOLAP
Characteristics ROLAP MOLAP
SCHEMA User star Schema
•Additional
dimensions can be added dynamically.
User Data cubes
•Addition dimensions require recreation of data cube.
Database Size Medium to large Small to medium
Architecture Client/Server Client/Server
Access Support ad-hoc
requests
Limited to pre-defined dimensions
Characteristics ROLAP MOLAP
Resources HIGH VERY HIGH
Flexibility HIGH LOW
Scalability HIGH LOW
Speed •Good with small data sets.
•Average for medium to large data set.
•Faster for small to medium data sets.
•Average for large data sets.
Implementation of OLAP servers
ROLAP:
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.
Advantage:
Scalable
Disadvantage:
Direct access to cells.
MOLAP:
Implements the multidimensional view by storing data in special multidimensional data structures.
Advantages:
Fast indexing
Only values are stored.
Disadvantage:
Not very Scalable
APPLICATIONS OF OLAP
OLE DB for OLAP
OLE DB for OLAP (abbreviated ODBO) is
a Microsoft published specification and an industry standard for multi-dimensional data processing.
ODBO was specifically designed for Online Analytical Processing (OLAP) systems by
Microsoft as an extension to Object Linking and Embedding Database (OLE DB).
/Contd…
Marketing and sales analysis
Consumer goods industries
Financial services industry (insurance, banks etc)
Database Marketing
One main benefit of OLAP is consistency of information and calculations.
It allows a manager to pull down data from an OLAP database in broad or specific terms.
OLAP creates a single platform for all the information and business needs, planning,
budgeting, forecasting, reporting and analysis.
