OLAP servers comparison
- What is OLAP server?
- Functions of OLAP server
- OLAP server vs OLAP client
- Various types of OLAP server
- 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.
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.
Being operational data engine OLAP server has the clear roles and functions. Here are the core ones:
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:
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:
- 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).
- 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
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
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:
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.
- Possibility to use ROLAP with data warehouses and various OLTP systems;
- The possibility of manipulating large amounts of data in OLAP relational database;
- Security and administration provided by relational DBMS.
- 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.
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.
- All data is stored in multidimensional structures that significantly increases the speed of requests processing;
- For complex operational analysis functions extended libraries are available;
- Sparse data processing is better than in ROLAP.
- 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.
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.
- In HOLAP aggregate maintenance and storage is optimized. This helps to economy disk space to promote the speed;
- Cube technology usage provides fast queries performance;
- 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:
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.
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.
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.
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.
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.
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:
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:
What is the difference between OLAP and relational database?
If oppose OLAP database vs relational database the following differences will be found:
- Data is organized in cubes;
- Used for high performance analytical reporting;
- Pre-aggregated and summarized data presentation;
- Have more powerful analytic capabilities then relational.
- 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.