OLAP (online analytical processing) is computer processing that enables a user to 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 beach ball products sold in Florida in the month of July, compare revenue figures with those for the same products in September, and then see a comparison of other product sales in Florida in the same time period. To facilitate this kind of analysis, OLAP data is stored in a multidimensional database. Whereas a relational database can be thought of as two-dimensional, a multidimensional database considers each data attribute (such as product, geographic sales region, and time period) as a separate "dimension." OLAP software can locate the intersection of dimensions (all products sold in the Eastern region above a certain price during a certain time period) and display them. Attributes such as time periods can be broken down into subattributes.
Why OLAP?
Knowledge is the foundation of all successful decisions.
Successful businesses continuously plan, analyze and report on sales and operational activities in order to maximize efficiency, reduce expenditures and gain greater market share.
All businesses collect data using many different systems, and the challenge remains: how to get all the data together to create accurate, reliable, fast information about the business. A company that can take advantage of reliable information and turn it into shared knowledge, accurately and quickly, will surely be better positioned to make successful business decisions and rise above the competition.
OLAP applications are developped to provide fast and interactive ad-hoc exploration, comparison and analysis of data, regardless of database size and complexity. It allows end users to manipulate and derive data for analysis purposes by applying analytical operations such as ratios, cumulative totals, trends and allocations across dimensions and across hierarchical levels.
OLAP can be used for data mining or the discovery of previously undiscerned relationships between data items. An OLAP database does not need to be as large as a data warehouse, since not all transactional data is needed for trend analysis. Using Open Database Connectivity (ODBC), data can be imported from existing relational databases to create a multidimensional database for OLAP.
Two leading OLAP products are Hyperion Solution's Essbase and Oracle's Express Server. OLAP products are typically designed for multiple-user environments, with the cost of the software based on the number of users.
In the OLAP world, there are mainly two different types: Multidimensional OLAP (MOLAP) and Relational OLAP (ROLAP). Hybrid OLAP (HOLAP) refers to technologies that combine MOLAP and ROLAP.
MOLAP
Excellent performance- this is the more traditional way of OLAP analysis. In MOLAP, data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats. MOLAP is the most standard approach to OLAP solutions. It uses a multidimensional database which directly stores the information contained in the various cubes. This is the best performing solution when using SQL Server Analysis services.
Advantages:
MOLAP cubes are built for fast data retrieval, and are optimal for slicing and dicing operations.
They can also perform complex calculations. All calculations have been pre-generated when the cube is created. Hence, complex calculations are not only doable, but they return quickly.
Disadvantages:
It is limited in the amount of data it can handle. Because all calculations are performed when the cube is built, it is not possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this is possible. But in this case, only summary-level information will be included in the cube itself.
It requires an additional investment. Cube technology are often proprietary and do not already exist in the organization. Therefore, to adopt MOLAP technology, chances are additional investments in human and capital resources are needed.
ROLAP
This methodology relies on manipulating the data stored in the relational database to give the appearance of traditional OLAP's slicing and dicing functionality. In essence, each action of slicing and dicing is equivalent to adding a "WHERE" clause in the SQL statement. Relational Online Analytical Processing (ROLAP) provides the same solution but uses a relational database for storage of the data. This approach translates native OLAP queries, written in a language called multidimensional expressions (MDX) into the appropriate SQL statements. This is primarily done to prevent the need for another copy of the data. The data created directly by the online transaction processing (OLTP) applications are used. The primary disadvantage to this solution is that it does not, generally speaking, perform as well as a MOLAP database.
Advantages:
It can handle large amounts of data. The data size limitation of ROLAP technology is the limitation on data size of the underlying relational database. In other words, ROLAP itself places no limitation on data amount.
It can leverage functionalities inherent in the relational database. Often, relational database already comes with a host of functionalities. ROLAP technologies, since they sit on top of the relational database, can therefore leverage these functionalities.
Disadvantages:
Performance can be slow. Because each ROLAP report is essentially a SQL query (or multiple SQL queries) in the relational database, the query time can be long if the underlying data size is large.
It has limited by SQL functionalities. Because ROLAP technology mainly relies on generating SQL statements to query the relational database, and SQL statements do not fit all needs (for example, it is difficult to perform complex calculations using SQL), ROLAP technologies are therefore traditionally limited by what SQL can do. ROLAP vendors have mitigated this risk by building into the tool out-of-the-box complex functions as well as the ability to allow users to define their own functions.
HOLAP
HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. For summary-type information, HOLAP leverages cube technology for faster performance. When detail information is needed, HOLAP can "drill through" from the cube into the underlying relational data. HOLAP) is a hybrid approach to the solution where the aggregated totals are stored in a multidimensional database while the detail data is stored in the relational database. This is the balance between the data efficiency of the ROLAP model and the performance of the MOLAP model.
If you're developing applications, you ultimately do not care which model is being used because your code will work against any kind of OLAP model (unless you're creating the cubes yourself). It is only a concern when a large scale project is being rolled out and you must decide which solution will meet its unique needs.
source: OLAP sites
Why OLAP?
Knowledge is the foundation of all successful decisions.
Successful businesses continuously plan, analyze and report on sales and operational activities in order to maximize efficiency, reduce expenditures and gain greater market share.
All businesses collect data using many different systems, and the challenge remains: how to get all the data together to create accurate, reliable, fast information about the business. A company that can take advantage of reliable information and turn it into shared knowledge, accurately and quickly, will surely be better positioned to make successful business decisions and rise above the competition.
OLAP applications are developped to provide fast and interactive ad-hoc exploration, comparison and analysis of data, regardless of database size and complexity. It allows end users to manipulate and derive data for analysis purposes by applying analytical operations such as ratios, cumulative totals, trends and allocations across dimensions and across hierarchical levels.
OLAP can be used for data mining or the discovery of previously undiscerned relationships between data items. An OLAP database does not need to be as large as a data warehouse, since not all transactional data is needed for trend analysis. Using Open Database Connectivity (ODBC), data can be imported from existing relational databases to create a multidimensional database for OLAP.
Two leading OLAP products are Hyperion Solution's Essbase and Oracle's Express Server. OLAP products are typically designed for multiple-user environments, with the cost of the software based on the number of users.
In the OLAP world, there are mainly two different types: Multidimensional OLAP (MOLAP) and Relational OLAP (ROLAP). Hybrid OLAP (HOLAP) refers to technologies that combine MOLAP and ROLAP.
MOLAP
Excellent performance- this is the more traditional way of OLAP analysis. In MOLAP, data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats. MOLAP is the most standard approach to OLAP solutions. It uses a multidimensional database which directly stores the information contained in the various cubes. This is the best performing solution when using SQL Server Analysis services.
Advantages:
MOLAP cubes are built for fast data retrieval, and are optimal for slicing and dicing operations.
They can also perform complex calculations. All calculations have been pre-generated when the cube is created. Hence, complex calculations are not only doable, but they return quickly.
Disadvantages:
It is limited in the amount of data it can handle. Because all calculations are performed when the cube is built, it is not possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this is possible. But in this case, only summary-level information will be included in the cube itself.
It requires an additional investment. Cube technology are often proprietary and do not already exist in the organization. Therefore, to adopt MOLAP technology, chances are additional investments in human and capital resources are needed.
ROLAP
This methodology relies on manipulating the data stored in the relational database to give the appearance of traditional OLAP's slicing and dicing functionality. In essence, each action of slicing and dicing is equivalent to adding a "WHERE" clause in the SQL statement. Relational Online Analytical Processing (ROLAP) provides the same solution but uses a relational database for storage of the data. This approach translates native OLAP queries, written in a language called multidimensional expressions (MDX) into the appropriate SQL statements. This is primarily done to prevent the need for another copy of the data. The data created directly by the online transaction processing (OLTP) applications are used. The primary disadvantage to this solution is that it does not, generally speaking, perform as well as a MOLAP database.
Advantages:
It can handle large amounts of data. The data size limitation of ROLAP technology is the limitation on data size of the underlying relational database. In other words, ROLAP itself places no limitation on data amount.
It can leverage functionalities inherent in the relational database. Often, relational database already comes with a host of functionalities. ROLAP technologies, since they sit on top of the relational database, can therefore leverage these functionalities.
Disadvantages:
Performance can be slow. Because each ROLAP report is essentially a SQL query (or multiple SQL queries) in the relational database, the query time can be long if the underlying data size is large.
It has limited by SQL functionalities. Because ROLAP technology mainly relies on generating SQL statements to query the relational database, and SQL statements do not fit all needs (for example, it is difficult to perform complex calculations using SQL), ROLAP technologies are therefore traditionally limited by what SQL can do. ROLAP vendors have mitigated this risk by building into the tool out-of-the-box complex functions as well as the ability to allow users to define their own functions.
HOLAP
HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. For summary-type information, HOLAP leverages cube technology for faster performance. When detail information is needed, HOLAP can "drill through" from the cube into the underlying relational data. HOLAP) is a hybrid approach to the solution where the aggregated totals are stored in a multidimensional database while the detail data is stored in the relational database. This is the balance between the data efficiency of the ROLAP model and the performance of the MOLAP model.
If you're developing applications, you ultimately do not care which model is being used because your code will work against any kind of OLAP model (unless you're creating the cubes yourself). It is only a concern when a large scale project is being rolled out and you must decide which solution will meet its unique needs.
source: OLAP sites
nice information
ReplyDeleteBest HR software in UAE
Cloud based HR software in UAE
Best HR software in Abu Dhabi
Human Resource and Payroll Software in UAE
Great reading and extremely comprehensive post. much covers everything keep posting more blogs. ISO Lead Auditor Course in Oman
ReplyDeleteOLAP (Online Analytical Processing) is a technology that Hosting Spell enables fast, multidimensional analysis of large data sets for business reporting and decision-making.
ReplyDelete