OLAP SYSTEM
OLAP (online analytical processing) is computer processing that enables a userto easily and selectively extract and view data from different points of view.For example, a user can request that data be analyzed to display a spreadsheet showing all of a company's products sold in Chennai in the month of July, compare revenue with those
for the same products in September, and then see a comparison of other product sales in Chennai in thesame time period. (On Line Analytical Processing) Decision support software that allows the user to quickly analyze information that has been summarized into multidimensional views and hierarchies.
Online: Although most OLAP tools and applications enable development of reports that can be savedand printed when not connected to live data, OLAP emphasizes live access to data rather than static reporting. Analytic queries are submitted against the database in real time, and the results are returned in real time.
Analytical processing: This is the key concept with OLAP. End users can:
• Easily navigate multidimensional data to perform unpredictable ad hoc queries and to display
the results
• Manage business rules across dimensions and cubes
• Drill through levels of detail to uncover significant aspects of data
• Rapidly and efficiently obtain the results of sophisticated data calculation and selection across
multiple dimensions of data
A standard transactional report or query might ask, “When did order 84305 ship?” This query reflects
the basic mechanics of doing business. It involves simple data selection and little or no calculation
processing. It can be answered directly from the transactional system, probably without impacting other operations. Every organisation needs this basic level of information.
In contrast, OLAP systems are typically deployed to extend and enhance an organization’s ability to answer a much broader range of business questions about the data they are collecting in their transactional systems:
• How do sales for our top 10 most profitable products across Europe for this quarter compare
with sales a year ago?
• What are the differences in the product-sales mix between the regions, relative to the global
sales mix?
• What are our forecast units, unit price per service, unit cost per product, sales, cost trends, and
profit for the next 12 months?
• In what ways does the mix vary by salesperson, and what is the relative performance of our
salespeople?
• What are the products making up 40% of our profit for each region over time?
These questions are more analytical and complex, and the answer to one question often leads
immediately to another question as the user follows a train of thought in researching a business
problem or opportunity.
Although OLAP applications are found in widely divergent functional areas, they all require the
following key features:
· Multidimensional views of data
· Calculation-intensive capabilities
· Time intelligence
OLAP System can handle:
· Reports over time
· Summarized data
· Aggregated data
· Data used for trend analysis
· Non time sensitive information
OLAP systems can’t handle
· Transactional forms and reports used in day-to-day business
· Detailed transactions, unless there is a specific drill down reporting need.
OLAP Advantages:
• On-Line Analytical Processing (OLAP) provides customers with tools that can be used to perform
"multidimensional analysis" on data to discover hidden information within the database.
• BI Technology: A special technologies are far better tuned to dealing with summarization, slice
‘n’ dicing and decision support solutions than a stock standard relational database.
• Demoralized data:Data in the OLAP database can be demoralized to include submersed data.
This should drastically cut down computational time for many reports. The number of joins is
reduced, thus reducing look ups, hashes and merging.
• Index optimization:Many indexes can be placed on the data which are geared for report processing
performance.As data is not being added all the time, there will be no conflict with OLTP
systems.
• Lock reduction:the databaseis brought to an absolute minimum on the database as it is SELECT
only store, the tables gladly run with multiple “read locks”.
Types of OLAP Systems
• MOLAP - Multidimensional Online Analytical Processing or MOLAP is considered to be main
form of OLAP and thus sometimes just referred as OLAP. It is known for its fast query performance
which is because of optimized storage, multidimensional indexing . The main disadvantage
is that it introduces data redundancy.
Advantages of MOLAP
• Fast query performance due to optimized storage, multidimensional indexing .
• Automated computation of higher level aggregates of the data.
• It is very compact for low dimension data sets.
Disadvantages of MOLAP
• Within some MOLAP Solutions the processing step (data load) can be quite lengthy, especially
on large data volumes.
• MOLAP tools traditionally have difficulty querying models with dimensions with very high
cardinality(i.e., millions of members).
• Some MOLAP products have difficulty updating and querying models.
• Some MOLAP methodologies introduce data redundancy.
• ROLAP - Relational Online Analytical Processing is an alternative to the MOLAP and works
directly with relational databases. The main difference between MOLAP and ROLAP is that it
doesn't require storage of information. One advantage of ROLAP is that data stored in database
can be accessed through SQL tool. But it is considered to be slower than MOLAP tools by
industry people.
Advantages of ROLAP
• ROLAP is considered to be more scalable in handling large data volumes, especially models
with dimensions with very high cardinality (i.e. millions of members).
• The data is stored in a standard relational database and can be accessed by any sql tool. .
Disadvantages of ROLAP
• There is a consensus in the industry that ROLAP tools have slower performance than MOLAP
tools.
• When the step of creating aggregate tables is skipped, the query performance then suffers
because the larger detailed tables must be queried. This can be partially remedied by adding
additional aggregate tables, however it is still not practical to create aggregate tables for all
combinations of dimensions/attributes.
• HOLAP -This is a combination of the above two. This architecture has the MOLAP storage of
summary data and this summary data has links to the detailed transaction level data in Data
Warehouse RDBMS to enable user to drill down to the lowest level of detail. Apart from that it
also allows direct access to the data in Data Warehouse, which is not considered worthwhile to
be translated into OLAP cubes.
OLAP (online analytical processing) is computer processing that enables a userto easily and selectively extract and view data from different points of view.For example, a user can request that data be analyzed to display a spreadsheet showing all of a company's products sold in Chennai in the month of July, compare revenue with those
for the same products in September, and then see a comparison of other product sales in Chennai in thesame time period. (On Line Analytical Processing) Decision support software that allows the user to quickly analyze information that has been summarized into multidimensional views and hierarchies.
Online: Although most OLAP tools and applications enable development of reports that can be savedand printed when not connected to live data, OLAP emphasizes live access to data rather than static reporting. Analytic queries are submitted against the database in real time, and the results are returned in real time.
Analytical processing: This is the key concept with OLAP. End users can:
• Easily navigate multidimensional data to perform unpredictable ad hoc queries and to display
the results
• Manage business rules across dimensions and cubes
• Drill through levels of detail to uncover significant aspects of data
• Rapidly and efficiently obtain the results of sophisticated data calculation and selection across
multiple dimensions of data
A standard transactional report or query might ask, “When did order 84305 ship?” This query reflects
the basic mechanics of doing business. It involves simple data selection and little or no calculation
processing. It can be answered directly from the transactional system, probably without impacting other operations. Every organisation needs this basic level of information.
In contrast, OLAP systems are typically deployed to extend and enhance an organization’s ability to answer a much broader range of business questions about the data they are collecting in their transactional systems:
• How do sales for our top 10 most profitable products across Europe for this quarter compare
with sales a year ago?
• What are the differences in the product-sales mix between the regions, relative to the global
sales mix?
• What are our forecast units, unit price per service, unit cost per product, sales, cost trends, and
profit for the next 12 months?
• In what ways does the mix vary by salesperson, and what is the relative performance of our
salespeople?
• What are the products making up 40% of our profit for each region over time?
These questions are more analytical and complex, and the answer to one question often leads
immediately to another question as the user follows a train of thought in researching a business
problem or opportunity.
Although OLAP applications are found in widely divergent functional areas, they all require the
following key features:
· Multidimensional views of data
· Calculation-intensive capabilities
· Time intelligence
OLAP System can handle:
· Reports over time
· Summarized data
· Aggregated data
· Data used for trend analysis
· Non time sensitive information
OLAP systems can’t handle
· Transactional forms and reports used in day-to-day business
· Detailed transactions, unless there is a specific drill down reporting need.
OLAP Advantages:
• On-Line Analytical Processing (OLAP) provides customers with tools that can be used to perform
"multidimensional analysis" on data to discover hidden information within the database.
• BI Technology: A special technologies are far better tuned to dealing with summarization, slice
‘n’ dicing and decision support solutions than a stock standard relational database.
• Demoralized data:Data in the OLAP database can be demoralized to include submersed data.
This should drastically cut down computational time for many reports. The number of joins is
reduced, thus reducing look ups, hashes and merging.
• Index optimization:Many indexes can be placed on the data which are geared for report processing
performance.As data is not being added all the time, there will be no conflict with OLTP
systems.
• Lock reduction:the databaseis brought to an absolute minimum on the database as it is SELECT
only store, the tables gladly run with multiple “read locks”.
Types of OLAP Systems
• MOLAP - Multidimensional Online Analytical Processing or MOLAP is considered to be main
form of OLAP and thus sometimes just referred as OLAP. It is known for its fast query performance
which is because of optimized storage, multidimensional indexing . The main disadvantage
is that it introduces data redundancy.
Advantages of MOLAP
• Fast query performance due to optimized storage, multidimensional indexing .
• Automated computation of higher level aggregates of the data.
• It is very compact for low dimension data sets.
Disadvantages of MOLAP
• Within some MOLAP Solutions the processing step (data load) can be quite lengthy, especially
on large data volumes.
• MOLAP tools traditionally have difficulty querying models with dimensions with very high
cardinality(i.e., millions of members).
• Some MOLAP products have difficulty updating and querying models.
• Some MOLAP methodologies introduce data redundancy.
• ROLAP - Relational Online Analytical Processing is an alternative to the MOLAP and works
directly with relational databases. The main difference between MOLAP and ROLAP is that it
doesn't require storage of information. One advantage of ROLAP is that data stored in database
can be accessed through SQL tool. But it is considered to be slower than MOLAP tools by
industry people.
Advantages of ROLAP
• ROLAP is considered to be more scalable in handling large data volumes, especially models
with dimensions with very high cardinality (i.e. millions of members).
• The data is stored in a standard relational database and can be accessed by any sql tool. .
Disadvantages of ROLAP
• There is a consensus in the industry that ROLAP tools have slower performance than MOLAP
tools.
• When the step of creating aggregate tables is skipped, the query performance then suffers
because the larger detailed tables must be queried. This can be partially remedied by adding
additional aggregate tables, however it is still not practical to create aggregate tables for all
combinations of dimensions/attributes.
• HOLAP -This is a combination of the above two. This architecture has the MOLAP storage of
summary data and this summary data has links to the detailed transaction level data in Data
Warehouse RDBMS to enable user to drill down to the lowest level of detail. Apart from that it
also allows direct access to the data in Data Warehouse, which is not considered worthwhile to
be translated into OLAP cubes.
