OLAP and query language: how to write OLAP queries

1. OLAP essence
2. OLAP types
3. Reasons of usage
4. OLAP Structure
5. OLAP language: OLAP query and MDX
6. OLAP vs OLTP queries
7. Top Questions about OLAP
8. Summary

There are lot of talks around OLAP as like as its vendors. Particularly, there is a necessity to figure out its base. What language lays in its queries? This is what our today’s discussion is dedicated to.

OLAP essence

Let’s start from the very beginning – OLAP definition. Online Analytical Processing is a method, which allows analyzing multidimensional data through queries. This is a business intelligence tool useful for data analysis, reporting, forecasting and planning. The data can be analyzed from the different points of view. For example, the company sells cosmetics and wants to analyze cream in different categories:

• The quantity of sold items in September;
• Compare the number of sold creams in Paris and London in September;
• Compare the numbers of the sold items in Paris in October and September.

How does it work?

This kind of analysis performed as follows:

The data collected from multiple sources

↓

The data is stored in the data warehouse

↓

The data is sorted and organized in different data cubes and categorized by dimensions

Brief history

The term OLAP firstly appeared in 1993. It was invented by ‘the father of the relational database’ Edgar F. Codd in his article Providing OLAP (On-Line Analytical Processing) to User-Analysts: An IT Mandate. But the idea of processing multidimensional data dates back to 1962, when Ken Iverson published his work A Programming Language, APL. This language was used in many business applications that are functionally similar to modern OLAP-systems.

Features

The key OLAP features usually are:

• Data views in multidimensional manner;
• Complex calculations support;
• Time intelligence;
• Easy-to-use interface.

There are some more reasons to use OLAP. But before overviewing the core benefits, let’s learn what OLAP types exist.

OLAP types

So, there are about nine different OLAP types, but we can distinguish three main:

1. ROLAP

Relational OLAP are the systems that have direct access to existing relational databases or use data uploaded to their own local tables. ROLAP works in the following way: The system sends the query to a relational database – the required data turns back The key benefit of this system is that the questions’ number is not limited as there are no boundaries generated by the cube content. It is also possible to drill down in detail to the database lowest level. Also, relational DBMS provide security and administration.

2. MOLAP

Multidimensional OLAP in comparison with previous type stores data in multidimensional database. It means I is possible to manipulate data as a multidimensional array, so that the speed of calculating aggregate values is the same for any of the measurements. Multidimensional OLAP architecture includes:

• MOLAP server
• Database server
• Front-end tool

MOLAP involves creation of an explicit, physically stored multidimensional cube (or several cubes) with the execution of analytical queries only on them, without reference to the relational database. In this case, the highest productivity is achieved.

3. HOLAP

Hybrid OLAP, as it is clear from the name, combines the features of both ROLAP and MOLAP. Wherein, HOLAP uses advantages of both and minimize disadvantages. Talking about Hybrid OLAP architecture, the cube structure and pre-computed aggregates are stored in a multidimensional database. It promotes 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. Some other OLAP types are:

• WOLAP (Web OLAP) – a three-tier architecture includes client, middleware and database server. The access through web browser.

• SOLAP (Spatial OLAP) – combines possibilities of standard OLAP and Geographic Information Systems in one UI.

• DOLAP (Desktop OLAP) – provides opportunity to work with dataset on user’s desktop. To do that it is just necessary to download data from the source.

• Mobile OLAP - related to wireless networks or mobile devices. Mobile OLAP implementation allows you to work with OLAP data and applications remotely via mobile devices.

Finally, it’s time to take a closer look at OLAP system advantages.

Reasons of usage

As the competition in the business world is rather high, every business requires additional tools for successful and compatible service. Data management is not an exception. OLAP system is one of the efficient tool. You ask why? Let’s overview some reasons for that:

• Fast and efficient analysis operation in real time

The structure of OLAP system allows getting quick response to queries in real time. Users can pose unlimited queries as soon as they appear and get response immediately. It promotes service quality as issues resolved quickly.

• Forecasting with “What if” action

It is possible to analyze not only existed data and numbers but to forecast future perspectives. Thus, OLAP allows users replace the values to know what other outcomes may occur. Analyze ongoing business situation, foresee losses and future troubles in order to prevent them.

• Flexible self-service

OLAP systems help users to create reports themselves. Interface is usually rather simple and there is no need to ask professional IT specialists to assist in report generation.

• Single platform for all data operations

All business issues connected with data can be solved through OLAP system. Users from different departments can operate reports, budgeting, forecasting and planning actions. It is really convenient to have a single tool and don’t spend money for multiple services.

• Multidimensional data representation

Cube data representations allows users to work with information from different points of view. It is possible to consider all aspects and generate accurate unique report. There are lots of OLAP vendors and each of them more or less provides users with benefits mentioned above. And logical question now is how to choose the correct vendor then? Of course, practice makes perfect, so it is hard to know until trying. GalaktikaSoft can provide you with such opportunity. Ranet OLAP is data analysis tool released by GalaktikaSoft. It obtains about 50 elements, services and controls for comprehensive data analysis. Being one of the best OLAP query tools Ranet helps to get all the benefits form OLAP system and even more: it is very extensible and ready to work anywhere you are. One of the core benefit is that Ranet OLAP provides free 30-days trial and demo version. Try first before buying and be aware of its usefulness.

OLAP Structure

For better understanding of OLAP system, it is also important to mention its structure. Let’s overview in more detail each OLAP component:

OLAP vs OLTP queries

As the above mentioned systems are often contradicted it is logical that OLAP and OLTP queries are the subjects for comparison as well. Here are the core differences between them: Characteristics of OLAP queries:

• Process huge data amounts;
• Support decision-making processes;
• Discover patterns and trends in the data.

Characteristics of OLTP queries:

• More fast;
• Process small data amounts;
• They are simple queries.

OLAP language: OLAP query and MDX

The work with OLAP is based on query. In order to operate report and analysis you have to post query, so it is rather relevant to talk about OLAP query language. The basic OLAP query language nowadays is MDX. Multidimensional Expressions (MDX) is a query language focused on access to multidimensional data structures. MDX queries are aimed at work with OLAP members, dimensions and hierarchies. With MDX it is possible to query data from SQL server and get a dataset with axis and cell data. Talking about MDX query language structure, it includes the following components:

• SELECT: the principle MDX statement. SELECT points out the dimension members that will be parts of axis;
• WHERE: identifies the dimension or member that are used as slicer dimensions;
• WITH: calculates the sets named during SELECT and WHERE clauses processing;
• FROM: determines the source of multidimensional data to which our query is addressed to.

The relevant question now is how to write OLAP queries? First of all, there are two types of them:

1
2
3

SELECT 
{([Measures].[Unit Sales])} ON COLUMNS 
FROM SALES

1
2
3
4
5

SELECT 
{ [Measures].[Unit Sales], [Measures].[Store Sales] } ON COLUMNS, 
{ [TIME].[1997], [TIME].[1998] } ON ROWS 
FROM Sales 
WHERE ( [Store].[USA].[CA] )

And if the simple MDX query can be posed by a user without technical background, to write complex query you need an additional IT help. For that reason, Ranet OLAP provides Query builder. This tool has two basic modes:

More information about MDX queries and their examples you can find in our article Multidimensional Expressions.

Top Questions about OLAP

And to consolidate information we’ve learned today, we want to present you top 5 asked questions about OLAP and query language. Check them and maybe you will also find the answer to yours:

1. What is the difference between OLAP and OLTP?

Online Transaction Processing (OLTP) participates in the work of a particular system. OLTP is characterized by a large number of short online transactions (INSERT, UPDATE, DELETE). The main focus for OLTP systems is very fast processing of requests, ensuring data integrity in multiple access environments and efficiency, measured by the number of transactions per second. Online Analytical Processing (OLAP) deals with historical data or archival data. In comparison with OLTP, OLAP has a relatively low transaction volume. Queries are often very complex and include clusters. For OLAP query processing, response time is a measure of performance. OLAP applications are widely used in data mining.

2. Why OLAP can be useful?

The key OLAP benefit is that the system provides solutions for both users and IT-specialists. Users have possibility to access data and operate required calculations for reports and analysis. And for IT users, OLAP makes available to generate complex MDX queries. Besides, there are other 5 reasons in favor of OLAP:

• Fast and efficient analysis operation in real time;
• Forecasting with “What if” action;
• Flexible self-service;
• Single platform for all data operations;
• Multidimensional data representation.

3. What is OLAP structure?

OLAP is based on query language and has 7 main components:

• Cube;
• Member:
• Measures;
• Calculated member;
• Dimension;
• Level;
• Hierarchy.

4. What is the best OLAP vendor?

There are a great number of OLAP vendors and product nowadays. All them have the basic features in common. But our advice is to try first. You’ll never know what is the best variant for you until you try. GalaktikaSoft gives you such opportunity. You can try Ranet OLAP 30 days for free or use demo.

5. What is OLAP query?

Query language lays in the basis of every OLAP system. It is a request you need to post in order to get required information from a database. OLAP queries and tools in data warehouse assist in reports and analysis processing. The basic OLAP query language is MDX. Multidimensional Expressions is a query language for getting access to multidimensional data structures. MDX queries work with OLAP members, dimensions and hierarchies. With MDX it is possible to query data from SQL server and get a dataset with axis and cell data.

Summary

MDX language is not a unique one but nowadays is obviously the most popular. And it’s not a surprise as it provides security, fast responses and interactivity. Whether to use it or choose another language is up to you. Anyway, you can always try it with Ranet OLAP.