Speed of analytics is critical, irrespective of the size of an enterprise or industry. OLAP, by far, is the most successful and the only technique that can deliver the interactivity that organizations need today. In this e-book, we’ll talk all about OLAP, its benefits and how it was reinvented by Kyvos with a revolutionary AI-powered smart aggregation technology to deal with the scale and complexity of the modern data ecosystem.
OLAP (Online Analytical Processing) is a computing approach that organizes data into multidimensional structures.
It was first introduced when the computational power of systems was not as robust and scalable as it is today. The biggest challenge of that time was to extract data from multiple tables, which needed various joins and group-byʼs to aggregate data. Since computing power was limited, the execution of these heavy queries took minutes and hours (sometimes days too) to bring back the results.
OLAP solved this problem by pre-aggregating all the measures and dimensions at every level to build multidimensional data models, also known as cubes, that delivered quick query responses. With OLAP, business users could build reports faster, run ad hoc queries, and perform operations such as slicing, dicing, drill down to uncover patterns, find relationships, identify anomalies and get useful business insights.
Example:-
To understand this better, let us take a simple case where a beverage company wants to compare the sales of different flavors of a product across various regions in the United States. They fire a query:
“How many more cherry flavor drinks were sold in New York in the third quarter last year compared to this year?”
Imagine if they were logging hundreds and thousands of transactions each day across stores and regions. They would need to scan millions and billions of rows to get this answer. which would lead to delayed responses. On the other hand, OLAP can pull out this information in seconds as the data is already aggregated.
OLAP Cube
An OLAP cube is like a multidimensional model that can be twirled around to gain insights from any angle. The significance of OLAP increases as query complexity increases and business users deepen their analytics.
There are different OLAP approaches depending on how data is arranged and stored. It is important to understand these terms to determine the most appropriate choice for the enterprise.
In ROLAP, data is stored in relational tables. Complex SQL queries are fired to access the ROLAP database and calculate information. Queries are processed on-demand without pre-aggregation. Though ROLAP can handle large volumes of data, query response times are slow as all computations are done at runtime.
MOLAP is a multidimensional cube approach that pre-computes and pre-summarizes data into various combinations. The pre-aggregated data is stored in multidimensional cubes that can be accessed to serve queries. This maximizes query performance, making MOLAP most suitable for slicing and dicing operations and ad hoc analysis.
HOLAP has attributes of both MOLAP and ROLAP. It combines relational and multidimensional approaches to store data and the approach used to access data depends on the processing application. Thus, HOLAP provides more flexibility in handling data, but response times may not always be consistent.
| ROLAP | MOLAP | HOLAP |
| Uses relational database to store data. | Uses multidimensional structure to process and store data. | Uses multidimensional structures to store aggregations & relational database to store details. |
| Data is stored in columns and rows. | Data is pre-computed & stored in multidimensional cube. | Has attributes of both ROLAP and MOLAP |
| On-demand SQL queries are used to retrieve data | All aggregations and calculations are done in advance for faster retrieval. | Aggregations are done selectively and other queries are fetched on-demand. |
| No pre-computation as queries are served on-demand. | Optimizes storage and caching to enhance performance and reduce response times. | Allows flexibility for handling data depending upon needs. |
| Legacy approach was popular when analytical needs were simpler. | Used for ad hoc analysis and complex calculations. | Used for moderate query performance requirements. |
| Limited by SQL functionalities. | High performance for deep-dive operations such as slice and dice. | Performance is slow for deep-dive operations. |
| Difficult to model complex data with multiple hierarchies. | Allows for accurate modeling of real-life business use cases using multidimensional structures. | Architecture is difficult to understand as it supports both MOLAP and ROLAP. |
| Slow query response times. | Fast query response times. | Inconsistent query response times. |
MOLAP is the most popular approach as it delivers much higher performance than ROLAP and HOLAP.
In MOLAP, records are organized into various dimensions and each dimension can have multiple levels of hierarchies. It helps users view their data from different perspectives and run interactive queries. Here are some operations that can be performed on an OLAP cube to get these different views.
Increasing the level of aggregation
Roll-up operation is performed by removing one or more dimensions from the cube. During roll up, the current view is taken and a further group-by is done on one of the dimensions.
Example:-
Suppose an organization has the sales information of stores located in four different cities:
Now, if the organization wants to see sales information ‘by country’ instead of ‘by city,’ it needs to perform a roll-up operation.
In this example, the dimensions have been reduced from 4 to 2 by uniting the values of stores that belong to the same country. The sales data of stores in New York and Seattle (U.S.) have been combined, along with the sales data of stores in Toronto and Montreal (Canada).
Decreasing the level of aggregation or increasing detail
Drill down is performed by adding new dimensions or expanding the existing ones across any axis of the OLAP cube. It is the converse of roll-up.
Example:-
Suppose, in the above example, an organization wants to see sales data ‘by month’ instead of ‘by quarter.’ For this, the OLAP cube can be drilled down to display month-wise information.
Selecting parameters
Slicing is a method of selecting one dimension from the cube and creating a sub-cube.
Example:-
Consider an OLAP cube with the sales data for the last 5 years. Now, if an organization wants to see the sales for the year 2024, it can slice the cube to view the data for only 2024.
Projecting for alternate views
Dicing involves extracting data from multiple dimensions & analyzing it from different viewpoints.
Example:-
In the above example, the sales data is organized by year. Now, if an organization wants to explore the same data by product or by customer, it can perform a dicing operation.
OLAP has been around for several decades now, helping business users access their data directly and letting them interact with it in a meaningful way. However, when legacy OLAP solutions, such as SSAS, TM1, Essbase, etc., are brought into the world of modern data platforms, they fail to perform. These tools cannot deal with the massive increase in data volumes, the explosion of cardinality and dimensions and the large variety of data sources.
Some of the key challenges of Legacy OLAP are –
In Legacy OLAP, aggregations are stored in-memory. As a result, enterprises have to limit their level of aggregation and analytics depending on the available memory.
Legacy OLAP can handle hot queries, but there is a degradation in performance for cold and complex queries.
Users need to manually select the hierarchies and levels to build an aggregation. Therefore, a lot of time and effort is needed to build optimized cubes.
Legacy OLAP was not designed to leverage the elasticity of the cloud. Expensive server machines and resources are needed to grow capacity.
Legacy OLAP limits the size of the data that a single cube can hold. As a result, multiple cubes are needed to accommodate enterprise data, leading to siloed reporting.
To overcome these challenges and keep up with the pace of ever-growing data, enterprises need a cloud-native OLAP solution that can surpass the limitations of speed, scalability and complexity.
Kyvos AI-powered smart aggregation technology is a cloud-native solution that eliminates the limitations of legacy OLAP and enables interactive analysis on any scale of data on modern data platforms.
Built natively for the cloud, AI-powered smart aggregation technology leverages the computing capacity of modern data platforms to build highly optimized data models across multiple dimensions. Organizations can connect their existing BI tools to these data models and analyze data without compromising on scale or complexity.
Enables aggregations on any scale of data with as many dimensions and measures as needed. As more data lands in the data analytics environment, our AI-Powered smart aggregation technology can scale out transparently to process incremental data models.
Delivers instant responses, irrespective of data size or query complexity. As the heavy-lifting is done in advance, users get sub-second responses. All queries, warm or cold, are served fast.
Provides intelligent recommendations based on data profile and query patterns, making it easy to process optimized data models without the need for technical expertise.
Enables cost-effective data analytics on the cloud by combining performance with elasticity. Depending on the expected load, users can increase querying capacity during peak hours and release resources when not needed.
A semantic layer enables users to define their metadata and business logic in one place and achieve a single source of truth, simplifying analytics for all.
Analyze any scale of data in sub-seconds.
No need for technical expertise to write any code.
Explore, slice and dice and examine all aspects of data with granular details instantly.
Drag and drop interface to define data relationships and business logic in one place.
Supports parallelism, both for building data models and querying, while keeping a check on the costs. Additionally, the price-performant querying approach reduces data analytics costs significantly.
ML-powered smart recommendation engine enables creation of smart aggregates based on data and query patterns
Kyvos’ AI-powered smart aggregation technology leverages the scalability, flexibility and elasticity of the cloud to deliver analytics at a scale and speed that is impossible to achieve using any other OLAP solution.
Speed of analytics is critical, irrespective of the size of a business or industry. As more data get accumulated into the analytics environment, achieving interactive speeds becomes challenging. Besides, working with analytical restrictions is not an option anymore. Enterprises need to rethink their analytics strategy and build a truly scalable environment that enables users to analyze as much data as required in the fastest possible way.
OLAP, by far, is the most successful and the only technique that can deliver the interactivity that business users and teams need today. To meet the growing analytical needs of businesses and maximize ROI on data investments, organizations can upgrade to Kyvos’ AI-powered smart aggregation technology. Kyvos makes it easy for them to migrate from traditional OLAP as well as non-OLAP solutions to a next-gen platform, designed for the modern data ecosystem.