## Thursday, 1 September 2011

### OLAP

WHAT IS OLAP??

OLAP is a technically unique, agile, and flexible method for organizing data, especially the metadata, about an object or hierarchy of objects as in a system or organization in a multidimensional manner so that the data can be interactively managed and retrieved in any combination of dimensions and/or attributes for unique, investigative inquiries or for the recurring routine reporting of the information resulting from the integration of the selected dimensions about the object.

It performs multidimensional analysis of business data and provides the capabilities for complex calculations, trend analysis, and sophisticated data modeling.

How to use OLAP tool??

In using OLAP tools, you typically arrange one or more dimensions along the rows as well as the columns. You then place one or more facts in the data portion of the grid.

What are cubes??

Cubes are collections of facts and dimensions. In practical terms, you'll probably have a separate cube for production data and sales data because there is not a one-to-one relationship between all the facts in sales information and the production information. Within a single cube every fact must have a one-to-one relationship.

Why is it valuable??

OLAP is valuable because of its flexibility. Once the facts and dimensions are defined within the OLAP server, OLAP tools provide an easy way to analyze data by simply dragging and dropping dimensions and facts into the appropriate locations.

OLAP is useful in helping to determine why the data appears the way it does. For instance, if the sales for North America are way up or way down for a given quarter, it's easy to expand the North American geography into the states to see which state or states may be responsible for the difference.

Types of OLAP

Like everything else in technology, there's not a single way to solve the technology problem. There are three basic approaches to OLAP servers: MOLAP, ROLAP, and HOLAP.

Multidimensional Online Analytical Processing (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.

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.

Hybrid Online Analytical Processing (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.

Group- HR1

Author- Rupali Varshney