OLAP and query language: how to write OLAP queries
- OLAP essence
- OLAP types
- Reasons of usage
- OLAP Structure
- OLAP language: OLAP query and MDX
- OLAP vs OLTP queries
- Top Questions about OLAP
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.
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
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.
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.
So, there are about nine different OLAP types, but we can distinguish three main:
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.
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.
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.
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.
For better understanding of OLAP system, it is also important to mention its structure. Let’s overview in more detail each OLAP component:
|Cube||A data structure of OLAP system. It combines indicators by levels and hierarchies for each dimension that needs to be analyzed.|
|Member||A hierarchy element that represents one or more data instances. There are two categories of member: - Unique: a single member without repetition. For example, if we compare cars sold in 2018, each month will be a unique member as it can occur only once in the year; - Not unique: a member which can repeat. For example, if we compare cars sold in the period from 2017 to 2018 each month can be considered as not unique member.|
|Measures||Measures are the basic values in the cube that are pre-processed, grouped and analyzed.|
|Calculated member||A dimension element. Its value is calculated at run time with a help of expression. The calculated values of the components can be derived from the values of other elements.|
|Dimension||A set of one or more organized hierarchies of levels in a cube. It is understandable to the user and used as a basis for data analysis.|
|Hierarchy||A logical tree structure that organizes dimension elements in a parental way.|
|Level||The way of data organization inside hierarchy at lower and higher detail levels.|
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.
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:
|Contains only two MDX clause and answers a simple question. Example:|
|Can contain all kind of MDX clauses and answers complex questions. Example:|
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:
|Custom query mode||Design mode|
|This OLAP query manager suits perfectly for non-technical users. MDX query is generated by the system automatically and IT support is not required.||Convenient for developers. The user creates query manually. The benefit is you can post a query of any complexity and it will be based on your preferences.|
More information about MDX queries and their examples you can find in our article Multidimensional Expressions.
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:
- Calculated member;
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.
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.