Today we’d like to highlight the most principle part of OLAP technology – OLAP cube. If you had ever wondered what is a cube in BI in general and want to define OLAP cube technology you will likely find the answer here.
The Technology of an OLAP Cube
Big data analysis and relevant information representation imply aimed at better business execution, analysis, and planning. One of such business intelligence technologies constitutes an OLAP system (Online Analytical Processing). This technology is based on a multidimensional structure of data; the structure itself has a form of an OLAP cube.
Let’s also take a closer look at OLAP and data cubes basis. Multidimensional OLAP cubes used in OLAP technology are the basis of data storage and organization. The OLAP data cube definition entails that the cube comprises all the data in a snowflake or in a star schema whose middle is a fact table consisting of data aggregations and reconciling various dimensions. The dimensions include information about the aggregations and how they can be analyzed. The multidimensional OLAP cube is called a hypercube if it involves more than three dimensions.
But let’s talk in more detail about OLAP cube representation – it’s architecture.
OLAP cube data model
Continuing introduction to OLAP cubes, it’s important to mention their building parts – cube components. Let’s start a bottom-up.
| Measure |
|
| Member |
|
| Level |
|
| Hierarchy |
|
| Dimension |
|
OLAP Types
Although any online analytical processing system draws on a multidimensional structure, there are several types of OLAP cubes. Here we are mentioning only the most well-known ones:
| MOLAP | ROLAP | HOLAP |
| is considered to be a standard form of OLAP and as a rule, mentioned as OLAP. In this OLAP cube example data is stored in a multidimensional array but not in a relational database. The system requires pre-computation before being run. | In contrast to classic OLAP, ROLAP operates explicitly with relational databases and doesn’t need pre-computation. Nevertheless, the OLAP cube database has to be thoroughly drawn up to be exploited in ROLAP. | HOLAP, as the name implies, is a hybrid of MOLAP and ROLAP reconciled. This type lets users specify which amount of data will be stored in MOLAP and in ROLAP respectively. |
OLAP cube pros and cons
Like every technology and approach in BI, there are also advantages and disadvantages of OLAP cubes. And, of course, before implementing this technology it’s relevant to make sure that benefits of OLAP cubes have the greater number.
Cons:
- High cost: it’s not so cheap and fast to implement such technology, however it’s an investment in the future and can pay the company back;
- Computation capability: the core limitation of OLAP cube. Some systems provide lack of computational power which is greatly reduces system’s flexibility;
- Potential risks: sometimes it is not possible to provide huge amounts of data, and there is a great difficulty in providing valuable links to the decision maker.
Pros:
- Multidimensional data representation: such data structure allows users to see information from different points of view;
- High speed of data processing: OLAP cube usually processes a typical user query within 5 seconds, the users don’t have to spend much time on calculations and composing complex heavyweight reports;
- Detailed and aggregated data: a cube is organized with multiple dimensions which makes it easy and fast to navigate through tons of information;
- Familiar business vocabulary: instead of manipulating database table fields, the end user interacts with common business categories such as products, customers, employees, territory, date, etc.
As you can see advantages of OLAP cubes are not only in greater number but they are also more powerful. Every tool has its risk, however in case of OLAP cubes it’s worth it.
How to Work with an OLAP Cube
The OLAP cubes operate through MDX queries, which are implemented almost instantly. Due to numerous dimensions of the cube the query defines dimension members to indicate a particular cell which contains all requisite data for analysis or planning. Beyond that, due to the techniques of building OLAP cubes, their structure makes it possible to rotate the cube and see the data from various perspectives and foresee the market trends.
OLAP cube operations
There are far more besides rotate (pivot) that can be applied to the OLAP cube. Most frequently used are drill-up, drill-down and slice-and-dice. Let’s take a closer look at them.
Drill up
This operation, also known as OLAP cube roll up, involves collecting all the data that can be obtained and calculating all in one or more dimensions. Often this may require the use of a mathematical formula. As an OLAP example, you can consider a retail network with outlets in different cities. To identify models and anticipate future sales trends, data about them from all points is “rolled up” into the company's main sales department for consolidation and settlement.
Drill down
OLAP cube drill down is the opposite of drill up. The process begins with a large data set, and then is broken up into smaller parts, thereby allowing users to view the details. In the retail network example, analyst will evaluate sales data and look at individual brands or products that are considered bestsellers in each of the outlets in different cities.
Slice and dice
OLAP cube slice and dice is a process where analytical operations involve two actions: output a specific data set from a cube (the “slicing” aspect of the analysis) and view it from different points of view or angles. This can occur when all the data points of sales are obtained and entered into the hypercube. The analyst cuts a set of sales-related data from the OLAP Cube. Further, it will be viewed in the sales analysis of individual units in each region. At this time, other users can focus on assessing the cost-effectiveness of sales or evaluating the effectiveness of a marketing and advertising campaign.
Furthermore, there are plenty of other supplementary tools such as nesting, blending and OLAP cube reader for visualization, which we will discuss a bit later.
Applications of OLAP and data cubes
To start working with OLAP cubes you need to choose the right tool for it first. From wide variety on the market, we suggest to pay your attention on the following:
- IBM Cognos
- Micro Strategy
- Apache Kylin
- Essbase OLAP cubes
Building an OLAP cube with Hadoop is also possible, especially using analytical tool. Yon can download OLAP cube software for free, and try it yourself for. However, OLAP data cube implementation is not the last challenge. While operating with the OLAP cube data there is a need to compose MDX queries and create current reports. MDX queries are indeed considerably challenging to be created single-handedly and tested afterwards, given the correlation of relations. In addition, for successful report preparation, a user must navigate the data in a meaningful way and be aware of the way how to compile all relevant information. For this aim, there is a Cubes Viewer, a visual tool to analyze and deal with data in an OLAP system, developed to run in a browser. offers a function of a CubesViewer which will help a user to explore the data, design, generate and embed charts. Due to the viewer, the charts and dynamic analytics can be subsequently displayed on all the websites and applications as an HTML version of Ranet OLAP can be used in any browser. Owing to operating in the browser chosen views can as well be saved and shared. CubesViewer built-in Ranet OLAP affords even non-professional users an opportunity to examine data from various dimensions and aggregations, create complex queries and draw up sophisticated reports. Thanks to the viewer it’s easier to leverage raw facts, data series, and charts. The viewer embedded in our system will not require any additional installation or space.
User’ questions
Online analytical processing technology nowadays is more than still relevant. It brings profit, time saving and easy familiar tools to your business. What about further development of data cube and OLAP technology? Of course, it definitely will take place. But now, let’s one more time make sure in the form of user’s questions thread.
What is OLAP cube?
OLAP cube presents an array of multidimensional data. They are used in online analytical processing technology for data storage and future utilization. Cube has a form of star or snowflake schema in the middle of which is a fact table consisting of data aggregations and reconciling various dimensions. OLAB cubes are suitable for business reports, analysis, forecasting, management and budgeting. The technology allows users searching for specific product, project, etc., and receive result quickly.
What is a measure in an OLAP cube?
Measure is the smallest component in OLAP cube data model. This is the real data in OLAP cubes. Measures are stored in their own dimensions, which are called measure dimensions.
What is dimension in OLAP cube?
An OLAP dimension takes the highest level in the OLAP datacube structure. It is the metadata element, which describes the core economic indicators of enterprise’s activity, for example, products, customers, sales region, and period of time etc. OLAP dimensions help in data grouping and filtering on various grounds. Then this information can be detailed into other hierarchies, and that is how you can obtain an in-depth business data analysis.
What is the purpose of OLAP cube?
To begin with, the main purpose of OLAP technology is to provide possibility for fast and efficient data processing and retrieval through queries. Thus, the purpose of an OLAP cube is to store and process data in the way to improve the query performance and speed.
Is there any alternative to OLAP cube?
There are two main options, which serve as OLAP cube alternatives:
- Data lakes: stores data in an unstructured manner, and there is no hierarchy or organization between the individual pieces of data. Thus, the data is stored in its pure form – it is not processed and not analyzed. In addition, data lakes accept and store all data from all data sources, support all types of data and schemas are applied only when the data is ready for use.
- In-memory database: a database hosted in RAM. This technology offers another alternative, allowing you to combine analytical and transactional databases into a single database that can support both online transaction processing (OLTP) and OLAP.
What is the difference between data mart and OLAP cube?
The main difference between data mart and OLAP cube can be expressed just in one sentence. The first one is a concept, while OLAP cube is its implementation option. Data mart is a data warehouse slice, which is an array of thematic, narrowly focused information, aimed, for example, at users of one working group or department. It can be implemented using OLAP cube technology.
