Wednesday, March 20, 2013

What is OLAP(on line analytical processing)? || Types of OLAP?

The term ‘on-line analytic processing’ is used to distinguish the requirements of reporting and analysis systems from those of transaction processing systems designed to run day-to-day business operations.
On line transaction processing (OLTP) focuses on capturing and updating information efficiently. This works best in a normalised, relational database, where every piece of data is stored in only one place, as part of a single record in a specific table. Management reporting, on the other hand, usually requires many records to be summarized, and information from different parts of the database to be combined, e.g. to derive a useful ratio. Good performance requires a different data structure, and the use of aggregates.
OLAP tools represent data as if it were held in one or more multi-dimensional arrays, known as cubes, with cells like a spreadsheet. These cubes often have more than 3 dimensions, so strictly speaking they should be called hyper cubes, but it is much easier to visualize and explain how OLAP cubes are structured in plain 3-D.
The edges of the cube represent the important dimensions of the business, such as time, country and product. One edge usually represents different measures, but some tools use separate cubes for each measure.

Each cell can be uniquely identified by specifying a member from each dimension e.g. {1999, Cost of sales, UK}. By selecting one or more members from each dimension, the user can slice and dice the cube to view almost any subset of the data from different perspectives.
Dimension members may be organized into a hierarchy, with summary level members such as year, region or product group. The user can then drill down from one level to the next to see more detailed data, and then drill back up.

Most OLAP tools also enable the user to switch instantly between tabular and chart formats, and to save favorite views of the data as reports for future reference.

Types of OLAP:

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. 


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.
Excellent performance: MOLAP cubes are built for fast data retrieval, and is optimal for slicing and    dicing operations.
Can 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.
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.
Requires 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.


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.
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.
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. 

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.
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 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.

No comments:

Post a Comment