Notes On OLAP

What is OLAP?
Online Analytical Processing, a category of software tools which provide analysis of

data for business decisions. OLAP systems allow users to analyze database
information from multiple database systems at one time.
Online Analytical Processing Server (OLAP) is based on the multidimensional data

model. It allows managers, and analysts to get an insight of the information through
fast, consistent, and interactive access to information.
Example of OLAP
Any Data warehouse system is an OLAP system. Uses of OLAP are as follows
• A company might compare their mobile phone sales in September with sales
in October, then compare those results with another location which may be
stored in a separate database.
• Amazon analyzes purchases by its customers to come up with a personalized
homepage with products which likely interest to their customer.
Benefits of using OLAP services
• OLAP creates a single platform for all type of business analytical needs which
includes planning, budgeting, forecasting, and analysis.
• The main benefit of OLAP is the consistency of information and calculations.
• Easily apply security restrictions on users and objects to comply with
regulations and protect sensitive data.
Drawbacks of OLAP service
• Implementation and maintenance are dependent on IT professional because

the traditional OLAP tools require a complicated modeling procedure.
• OLAP tools need cooperation between people of various departments to be
effective which might always be not possible.
What is MOLAP?
Multidimensional OLAP (MOLAP) is a classical OLAP that facilitates data analysis

by using a multidimensional data cube. Data is pre-computed, re-summarized, and
stored in a MOLAP (a major difference from ROLAP). Using a MOLAP, a user can
use multidimensional view data with different facets.
Multidimensional data analysis is also possible if a relational database is used. By
that would require querying data from multiple tables. On the contrary, MOLAP has
all possible combinations of data already stored in a multidimensional array. MOLAP
can access this data directly. Hence, MOLAP is faster compared to Relational Online
Analytical Processing (ROLAP).
MOLAP Architecture
MOLAP Architecture includes the following components −
• Database server.
• MOLAP server.
• Front-end tool.
MOLAP Architecture
Consider the above MOLAP Architectures:
1. The user request reports through the interface

2. The application logic layer of the MDDB retrieves the stored data from
Database
3. The application logic layer forwards the result to the client/user.
MOLAP architecture mainly reads the precompiled data. MOLAP architecture has
limited capabilities to dynamically create aggregations or to calculate results that
have not been pre-calculated and stored.
For example, an accounting head can run a report showing the corporate P/L
account or P/L account for a specific subsidiary. The MDDB would retrieve
precompiled Profit & Loss figures and display that result to the user.
Implementation considerations in MOLAP
• In MOLAP it's essential to consider both maintenance and storage

implications to creating strategy for building cubes.
• Proprietary languages used to query MOLAP. However, it involves extensive
click and drag support for example MDX by Microsoft.
• Difficult to scale because the number and size of cubes required when
dimensions increase.
• API's should provide for probing the cubes.
• Data structure to support multiple subject areas of data analyses which data
can be navigated and analyzed. When the navigation changes, the data
structure needs to be physically reorganized.
• Need different skill set and tools for Database administrator to build, maintain
the database.
MOLAP Advantages
• MOLAP can manage, analyze and store considerable amounts of

multidimensional data.
• Fast Query Performance due to optimized storage, indexing, and caching.
• Smaller sizes of data as compared to the relational database.
• Automated computation of higher level of aggregates data.
• Help users to analyze larger, less-defined data.
• MOLAP is easier to the user that's why It is a suitable model for inexperienced
users.
• MOLAP cubes are built for fast data retrieval and are optimal for slicing and
dicing operations.
• All calculations are pre-generated when the cube is created.
MOLAP Disadvantages
• One major weakness of MOLAP is that it is less scalable than ROLAP as it

handles only a limited amount of data.
• The MOLAP also introduces data redundancy as it is resource intensive
• MOLAP Solutions may be lengthy, particularly on large data volumes.
• MOLAP products may face issues while updating and querying models when
dimensions are more than ten.
• MOLAP is not capable of containing detailed data.
• The storage utilization can be low if the data set is highly scattered.
• It can handle the only limited amount of data therefore, it's impossible to
include a large amount of data in the cube itself.
MOLAP Tools
• Essbase - Tools from Oracle that has a multidimensional database.

• Express Server - Web-based environment that runs on Oracle database.
• Yellowfin - Business analytics tools for creating reports and dashboards.
• Clear Analytics - Clear analytics is an Excel-based business solution.
• SAP Business Intelligence - Business analytics solutions from SAP
OLAP Operations
Since OLAP servers are based on multidimensional view of data, we will discuss
OLAP operations in multidimensional data.
Here is the list of OLAP operations −
• Roll-up (Consolidation)
• Drill-down
• Slice and dice
• Pivot (Rotation)
Roll-up
Roll-up operation performs aggregation on a data cube in any of the following ways −
• By climbing up a concept hierarchy for a dimension

• By dimension reduction
The following diagram illustrates how roll-up works.

Roll-up is performed by climbing up a concept hierarchy for the dimension location.
Initially the concept hierarchy was "street < city < province < country".
On rolling up, the data is aggregated by ascending the location hierarchy from the
level of city to the level of country.
The data is grouped into cities rather than countries.
When roll-up is performed, one or more dimensions from the data cube are removed.
Drill-down
Drill-down is the reverse operation of roll-up. It is performed by either of the following
ways −
• By stepping down a concept hierarchy for a dimension
• By introducing a new dimension.
The following diagram illustrates how drill-down works –
Drill-down is performed by stepping down a concept hierarchy for the dimension

time. (Quarter→Month)
Initially the concept hierarchy was "day < month < quarter < year."
On drilling down, the time dimension is descended from the level of quarter to the
level of month.
When drill-down is performed, one or more dimensions from the data cube are
added?
It navigates the data from less detailed data to highly detailed data.
Slice
The slice operation selects one particular dimension from a given OLAP cube and
provides a new sub-cube. Consider the following diagram that shows how slice
works.
Here Slice is performed for the dimension "time" using the criterion time = "Q1".
It will form a new sub-cube by selecting one or more dimensions.
Dice
Dice selects two or more dimensions from a given cube and provides a new sub-
cube. Consider the following diagram that shows the dice operation.
The dice operation on the cube based on the following selection criteria involves
three dimensions.
• (location = "Toronto" or "Vancouver")
• (time = "Q1" or "Q2")
• (item =" Mobile" or "Modem")
Pivot (Rotation)
The pivot operation is also known as rotation. It rotates the data axes to view in order
to provide an alternative presentation of data. Consider the following diagram that
shows the pivot operation, giving a new view of it.

Notes On OLAP

Uploaded by

Copyright:

Available Formats

Notes On OLAP

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Notes On OLAP

Uploaded by

Copyright:

Available Formats

What is OLAP?

Online Analytical Processing, a category of software tools which provide analysis of

Online Analytical Processing Server (OLAP) is based on the multidimensional data

Benefits of using OLAP services

Drawbacks of OLAP service

• Implementation and maintenance are dependent on IT professional because

Multidimensional OLAP (MOLAP) is a classical OLAP that facilitates data analysis

MOLAP Architecture includes the following components −

Consider the above MOLAP Architectures:

1. The user request reports through the interface

• In MOLAP it's essential to consider both maintenance and storage

• MOLAP can manage, analyze and store considerable amounts of

• One major weakness of MOLAP is that it is less scalable than ROLAP as it

• Essbase - Tools from Oracle that has a multidimensional database.

Here is the list of OLAP operations −

• By climbing up a concept hierarchy for a dimension

The following diagram illustrates how roll-up works.

The following diagram illustrates how drill-down works –

Drill-down is performed by stepping down a concept hierarchy for the dimension

It will form a new sub-cube by selecting one or more dimensions.

You might also like