Quick Read

  • Understanding what OLAP is and how OLAP systems work.
  • Knowing about the types of OLAP, including ROLAP, HOLAP, and MOLAP.
  • Introducing Kyvos AI-powered smart aggregation technology built for the cloud.

Back in the day, when organizations didn’t rely on separate systems for storing transactions and performing analytics, all the data, such as sales records, financial data, etc., was stored and analyzed in the same database. These databases worked fine when data volumes were low.

However, as businesses expanded, the data also increased significantly and things got messy. For instance, when a user ran a report on last year’s sales data while thousands of transactions were happening in real-time, the system got overloaded. Since computing power was limited, the execution of heavy queries took minutes and hours (sometimes days, too) to bring back the results.

Due to this, decisions were delayed. Another challenge was running complex queries with various joins and group bys that required extracting data from multiple tables. These queries locked tables for extended periods, preventing new transactions from being recorded.

To counter these limitations, two technologies, OLAP (Online Analytical Processing) and OLTP (Online Transaction Processing) came to the rescue. Both were invented to handle different types of data needs: OLAP to analyze aggregated data and OLTP to process (insertions, updates and deletions) real-time database transactions.

What Is OLTP?

OLTP is designed to handle large volumes of real-time transactional data and perform numerous operations while ensuring data integrity and quick response times. These systems enable multiple users to access and modify transactions simultaneously.

What Is OLAP?

OLAP is a computing method that organizes data into multidimensional structures to facilitate users to get information and analyze data from multiple viewpoints. It enables business users to run ad hoc queries, generate reports faster, and perform OLAP operations, such as slicing, dicing and drill down, to uncover patterns, find relationships between data sets, find anomalies, etc.

Let’s understand OLAP with a simple example — A regional company is trying to break into the US market. They are selling four flavors of fruit drinks – mango, cherry, strawberry, and grapes. And they particularly want to know what’s going on in four specific markets of the US –

  • Los Angeles
  • Chicago
  • New York
  • Phoenix

Lots of individuals are buying these fruit drinks in the US. Every purchase is stored in a database of the store from where it was bought. To perform business analysis on this data, they begin by analyzing sales of their products. The below table shows their sales information for the last two quarters of 2020.

Sales for last two quarters

Figure 1 – Sales for last two quarters

Both quarters have the same figures which means that the company has identical sales performance in both quarters. Next, they analyze the same sales data from a few other perspectives like sales of individual flavors and the sales according to the region.

Sales by flavors and regions

Figure 2 – Sales by flavors and regions

As you can observe, all the three tables show the exact figures – $40000 for all the three views. This means you can be assured that you are looking at the same information – the fruit drink sales of the company with multiple views, each broken into three different categories – time, geography, and product. These categories are called dimensions.

There isn’t much to analyze if we look at data this way. Since the data is the same in all three dimensions, let’s see what happens if we squeeze the existing three dimensions together to create a single multidimensional view of the data. This is where OLAP comes into the picture.

Sales breakup by flavor and region for each quarter

Figure 3 – Sales breakup by flavor and region for each quarter

Now, if you observe this table, you will see the patterns and anomalies that lie in your data. For example –

  • Mango and cherry flavor fruit drinks don’t sell in Los Angeles and Chicago in the Q3.
  • Strawberry flavor doesn’t sell in New York and Phoenix in Q3. On the other hand, It’s vice versa in Q4.
  • The grape flavor doesn’t sell in Los Angeles in both Q3 and Q4.

These are some patterns, and you can observe more patterns by interacting with your data from multiple viewpoints. This kind of data interaction reveals new and interesting information instead of isolated, single dimension data lists.

Figure 4 – An OLAP cube representation

The above diagram shows the pre-aggregated and pre-calculated data stored in an OLAP database or OLAP cube. Each cube cell holds a sales value for the intersection of each unique combination of time, geography, and product. Now, users can perform ad-hoc analysis, slice and dice their data by different dimensions, pivot rows, and columns, and drill down through hierarchies to create on-the-fly specialized reports.

Since data is pre-aggregated, the results are retrieved faster, enabling analysts to ask as many questions as they want and gain insights by exploring every interesting pattern and valuable information in a database.

The above example is an over-simplified, hypothetical use case. Real business data is much more complex, and the questions are tough. You have to deal with a large number of dimensions, perform heavy calculations on the fly, as well as deal with large datasets. Imagine the power of OLAP in such a situation!

OLAP vs. OLTP

What Is an OLAP Cube?

An OLAP cube is a multidimensional data structure that enables quicker and more efficient processing and analysis. With a data cube, different operations can be performed to explore and manipulate the data, such as slicing, dicing, drilling down, rolling up and pivoting. These operations help answer various business questions in a time-efficient manner. OLAP cubes can enable users to discover patterns from data to make informed decisions, hence making it an important tool for business intelligence and data analysis.

How Do OLAP Systems Work

OLAP systems work by retrieving or extracting data from multiple sources, such as relational databases, data warehouses, data marts, or other centralized data stores and clean and organize it into multi-dimensional data cubes.

Types of OLAP – ROLAP, MOLAP, HOLAP

There are three different types of OLAP based on how data is stored in the database.

ROLAP: stands for Relational Online Analytical Processing. It stores data in the form of rows and columns. ROLAP does not pre-compute data; it can be accessed through SQL queries on demand. Thus, ROLAP empowers users to analyze and view data, and is capable of saving storage space while working with massive historical datasets that are not often queried. It can deal with large datasets, but the larger is the dataset more is the processing time. Thus, performance becomes an issue with rising data volumes and concurrencies.

MOLAP: MOLAP is an acronym for Multidimensional Online Analytical Processing. In MOLAP, data is pre-aggregated, summarized, and stored in the form of a multidimensional array. It enables users to model data and visualize it from multiple viewpoints. Since all the complex calculations are done in advance, users can easily perform slice and dice operations on their data with fast response times. However, traditional MOLAP is less scalable than ROLAP, as a limited amount of data can be stored in a multidimensional cube.

HOLAP: Hybrid OLAP is a combination of both MOLAP and ROLAP features. It uses both relational and multidimensional structures to store data, and which one should be used to access data depends on the processing application. Thus, HOLAP provides a mid-way approach to both the methods described above.

Businesses choose the OLAP type based on their business requirements. MOLAP is by far the most used out of the three because it provides high performance. However, the exponential growth of the data has outgrown the capabilities of traditional MOLAP solutions. They fail to handle the explosion of cardinality and dimensions and multiple data sources.

To solve the above challenges and deal with the scale and complexity of modern business data, Kyvos introduced AI-powered smart aggregation – an innovative technology that eliminates the limitations of traditional OLAP solutions and can deal with growing data volumes and scale effortlessly.

OLAP Modernization with Kyvos

Kyvos modernizes OLAP by providing a cloud-native scalable architecture that overcomes all the limitations of legacy tools, such as SSAS, TM1 and Essbase.

For years, businesses have relied on legacy systems that process queries sequentially. This resulted in multiple fragmented, siloed data models that are hard to manage. Kyvos solves these issues with a unified data model that provides a single, consolidated view of all the enterprise data. It distributes workloads across multiple computing resources simultaneously so that complex calculations, aggregations and data transformations happen much faster, even at an enterprise scale.

Seamless Integration with All BI Tools

Kyvos ensures that analysts and business users can continue working in their familiar environment as it can be integrated with all BI tools like Tableau, Power BI, Excel, etc. Using Kyvos, businesses can conduct time-based comparisons across any period, i.e., days, months or quarters, and spot patterns and anomalies instantly.

The platform also supports advanced MDX functions and distinct count measures on any amount of data, which means users can perform advanced calculations on their data without any performance bottlenecks.

Accelerate Financial Analytics

Analyzing massive amounts of financial data comes with its own set of challenges, such as complex hierarchies, multi-level drill-downs and intricate calculations. Kyvos simplifies this process by offering support for unbalanced and ragged hierarchies and parent-child and alternate hierarchies with custom rollups.

The platform also supports weighted rollups to provide precise results and with adjustment refreshes in under 30 seconds, it ensures that any modifications made to data are instantly reflected in reports and dashboards.

Choose Kyvos for Scale and Performance on the Cloud

Businesses need a solution that can break all the barriers of traditional OLAP while relieving them from the worries of future data workloads. Kyvos can help transform their business by combining the flexibility and agility of cloud platforms while delivering lightning-fast performance when analyzing massive data on the cloud.