OLAP servers comparison

1. What is OLAP server?
2. Functions of OLAP server
3. OLAP server vs OLAP client
4. Various types of OLAP server
5. In addition

Information. Nowadays we have to much of it even talking about business data. Luckily, with the data amounts growth the number of the processing BI technologies raise too. It becomes easier and fast to operate business data using such tools as OLAP. However, even making this choice to use OLAP from variety of other tools, following questions arise: what types of OLAP servers are the best, how to define olap server architecture in data warehouse? Let’s try to figure out this together.

What is OLAP server?

Following the tradition, let’s start with OLAP server definition. We’ve already heard a lot regarding OLAP technology. Briefly, Online analytical processing, which we know as OLAP, is a computing technology for data querying, storage and analysis from different perspectives. OLAP server, in its turn, means that aggregated data calculations and storage are performed by the server. In other words, OLAP server is what does all the work and where the actively accessed data is stored. It can process large amounts of data with multiple users.

The multidimensional structure is organized in such a way that each data element is located and accessible based on the intersection of the dimension elements that define this element. The design and data structure of OLAP databases are optimized to quickly search for information in any orientation. OLAP server can operate the processed multidimensional information to provide users with consistent and fast response. The server can also populate its data structures in real time from different databases.

Functions of OLAP server

Being operational data engine OLAP server has the clear roles and functions. Here are the core ones:

• Data transformation: before storing in data warehouse, data should be cleansed and transformed. OLAP server uses such operations as filtering, summarizing, restructuring and data combining for this purpose;
• Storage: the data should be stored in such a way as to maximize system flexibility, manageability and overall availability;
• Analysis: OLAP includes rapid calculations, ad-hoc reports, what-if analysis, forecasting for efficient data analysis;
• Data presentation and access: server presents convenient data view and simple individual access for every company’s user.

OLAP server vs OLAP client

But before moving to OLAP servers types classification there is one more important thing to mention. Talking about OLAP products there are two basic types of their classification:

• Regarding the method of data storage for analysis;
• Regarding the location of the OLAP machine.

The first group we will overview in the next paragraph as it is related to OLAP server and its types. But let’s now stop on the second group and make a clear line here. So regarding the location of the OLAP machine there are two product types:

1. OLAP server

As we already know from the definition OLAP server is an OLAP product where aggregated data calculations and storage are performed by the server. The client application receives only the results of queries to multidimensional cubes stored on the server. OLAP servers support all three main types of data storage: ROLAP, MOLAP, HOLAP (we will highlight them in more detail a bit later).

2. OLAP client

OLAP client works differently. The construction of a multidimensional cube and OLAP calculations are performed in the client computer’ memory. OLAP clients are also divided into ROLAP and MOLAP. However, some of them may support both data access options. Sometimes OLAP client server architectures perceived as synonyms, but as you can see they are two different types. Each of these approaches has its own pros and cons. There is a popular belief about the advantages of server tools over client tools. However, in a number of cases the use of an OLAP client for users may be more efficient and profitable than using an OLAP server. A good example of this case is Ranet OLAP client tool.

Why do I need it?

Ranet OLAP is a ready-to-use data analysis tool that helps to create interactive reports, forecasting and business planning. The tool is easily extendable and can be integrated into existed system used by organization. Different modes help to create reports of any complexity and make Ranet OLAP open for specialist with and without IT skills. Try it yourself for free as Ranet OLAP provides 30-day free trial and demo version.

Various types of OLAP server

Finally, we are moving to different types of OLAP servers’ and OLAP database products discussion and comparison. Regarding the method of data storage for analysis there are 3 major OLAP servers and 5 other types. Three main OLAP types include:

ROLAP

Relational OLAP server or ROLAP is a form of OLAP where analysis is performed under the data stored in relational database. Thus, Relational OLAP architecture includes: database server, ROLAP server and front-end tool. To access those databases, the system uses SQL language.

How does ROLAP work? When a decision maker requests a measure value for a specific set of dimension members, the ROLAP system checks whether these members point to an aggregate or to the value of the lowest level of the hierarchy. If an aggregate is specified, the value is selected from the relational table. In other case, the value is taken from the data mart.

Relational tables of ROLAP architecture allows you to store large amounts of data. Since in the ROLAP architecture, the values of the lowest level ​​are taken directly from the data mart, they ​ will always correspond the current state of affairs. In other words, ROLAP systems have no lag in terms of the lowest level data.

• The functionality of the systems is limited by the SQL capabilities, since the user's analytical queries are translated into SQL sample statements;
• It is difficult to recalculate aggregated values when the initial data changes;
• Slow query performance and high demand for different resources.

Vendors: Microsoft Analysis Services, MicroStrategy, Cognos Powerplay, Infor BI OLAP server.

MOLAP

This abbreviation stands for Multidimensional online analytical processing. In multidimensional OLAP server, the cube structure is stored in a multidimensional database. This OLAP engine architecture includes: database server, MOLAP server and front-end tool. Pre-processed aggregates and copies of hierarchy the lowest level values are stored in the same database. In this regard, all requests for data are satisfied by a multidimensional database system, which makes MOLAP systems extremely fast.

Multidimensional OLAP database allows users to add additional dimensions when in ROLAP there are additional tables. Also, the structure of the MOLAP cube provides particularly fast and flexible data and calculation modeling.

The search for cells is greatly simplified — an application can identify the location of a cell by the name, instead of searching by index or the entire model (using SQL SELECT statements). In addition, multidimensional models use advanced array processing techniques and algorithms to manage data and calculations. So, multidimensional databases can very efficiently store data and operate calculations for a fraction of the time required for relational products.

• The cube data is detached from the base table. You need to use special tools for the cubes and their conversion formation if there are changes in the basic values;
• It is difficult to change measurements without re-aggregation.

Vendors: Cognos Powerplay, Oracle Hyperion Essbase OLAP server, IdeaSoft, Ranet OLAP.

HOLAP

The next example of OLAP server is HOLAP. Hybrid OLAP server combines features of both ROLAP and MOLAP, hence the name - hybrid. HOLAP models take advantage and minimize the drawbacks of both architectures.

In HOLAP systems, the cube structure and pre-processed aggregates are stored in a multidimensional database. This allows rapid extraction of aggregates from MOLAP structures. The lower-level values of the hierarchy in HOLAP remain in the relational data mart, which serves as the data source for the cube.

• The capability to support both ROLAP and MOLAP apps and tools cause high complexity;
• Data duplication possibility is rather high;

Vendors: Microsoft Analysis Services, IBM DB2 OLAP Server and MicroStrategy. There are five more different OLAP servers:

DOLAP

Desktop OLAP is a single-tier OLAP technology. In this system’s architecture, you can download relatively small data cubes from a central point (data marts or data warehouses) and perform multidimensional analysis, disconnecting from this resource. In another embodiment, the user can create an OLAP cube himself without connecting to the server. Those systems are user-friendly and provide high speed query processing, but their functionality is limited. Vendors: Cognos PowerPlay, Crystal Decisions, Hummingbird.

WOLAP

The WOLAP architecture assumes the use of Web capabilities. Web OLAP systems perform analytical functions such as aggregation and granularity, provide high performance combined with all the advantages of Web applications. Usage of such systems greatly simplifies the task of installation, configuration, and deployment. The web application runs on the server, and therefore the client machine only needs a browser and the internet connection. Such a deployment strategy is especially convenient for data warehouse administrators, who often have to work with a wide contingent of remote users, which is not easy when using traditional client server architecture Vendors: Influence Software, MicroStrategy, Web Edition.

Mobile OLAP

The functionality of the Mobile OLAP model is considered relatively wireless networks or mobile devices. Mobile OLAP implementations allow you to work with OLAP data and applications remotely via mobile devices. Vendors: Hyper-M, Deister Software Axional.

SOLAP

Spatial analytical processing or SOLAP is designed to study spatial data. In this area, concepts from significantly different areas of knowledge of geographic information systems and OLAP are combined. The SOLAP model is designed for interactive and fast analysis of large data amounts stored in spatial databases. Vendors: JMap Spatial OLAP, GeoMondrian.

RTOLAP

Real-Time Analytical Processing or RTOLAP differs from ROLAP basically, because the additional relational tables are not created for storing the aggregates, and the aggregates are calculated at the time of the request. Only explicitly entered data is stored in a multidimensional cube. When performing a user request, the server selects data or calculates values. All calculations are performed on demand, and all data is in main memory. Vendors: Applix TM1, Acinta, Palo.

In addition

As a bonus, we’ve gathered top questions end-users ask about OLAP servers:

What is OLAP database?

OLAP is a computing technology for data querying, storage and analysis from different perspectives. OLAP server, in its turn, means that aggregated data calculations and storage are performed by the server. In other words, OLAP server is what does all the work and where the actively accessed data is stored.

Which characteristics of OLAP server are the must?

The purposes OLAP serves depends on the technology type. However, the must for all the following:

• Data transformation: before storing in data warehouse, data should be cleansed and transformed. OLAP server uses such operations as filtering, summarizing, restructuring and data combining for this purpose;
• Storage: the data should be stored in such a way as to maximize system flexibility, manageability and overall availability;
• Analysis: OLAP includes rapid calculations, ad-hoc reports, what-if analysis, forecasting for efficient data analysis;
• Data presentation and access: server presents convenient data view and simple individual access for every company’s user.

What differ OLAP server ROLAP, MOLAP and HOLAP?

The core differences lie in the method of data storage for analysis which we can find in their definitions:

• ROLAP or Relational OLAP - a form of OLAP where analysis is performed under the data stored in relational database.
• MOLAP or Multidimensional OLAP – a form where the cube structure is stored in a multidimensional database.
• HOLAP or Hybrid OLAP – a form which combines features of MOLAP and ROLAP as the cube structure and pre-processed aggregates are stored in a multidimensional database.

What is the difference between OLAP and relational database?

If oppose OLAP database vs relational database the following differences will be found:

• Rows data organization;
• Used for detailed or transaction-level reporting;
• Contains data at a lower level of detail than the OLAP data source;
• Better at real-time reporting.

What is the difference between OLAP systems versus statistical databases?

A statistical database is a system for statistical applications supporting. It is focused on socioeconomic applications, while OLAP targets business applications. Also, OLAP databases in the contrary of statistical database were designed for handling huge data amounts.

What is the best OLAP server?

So what OLAP server is the best? As you could see, they all have pros and cons, their own peculiarities and directions. It is impossible to point out a single best OLAP database as it all depends on your needs and preferences. As they say, you will never know until try it yourself. Such products as Ranet OLAP give you this opportunity.