Recently, I’ve been reflecting on the parallels between the OLAP servers of the past and the modern vision of a universal semantic layer. To explore the similarities and differences between these two concepts, let’s take a journey through the history of OLAP servers, examining their inception and evolution.

## OLAP: There and Back Again
Back in the 80s and 90s, relational databases, also called Online Transaction Processing (OLTP) systems, were used for data management. They were designed to handle transactional workloads, but not analytical and multidimensional queries. This was mostly due to the row-oriented nature of storage and single-node architecture. That architecture was optimized for handling everyday transactional workloads—efficiently processing a high volume of short, simple transactions like inserts, updates, and deletes. However, it struggled with complex analytical queries that required scanning large datasets, performing aggregations, and executing intricate calculations.

__Online Analytical Processing (OLAP)__ emerged in the early 1990s to address these analytical processing needs. OLAP systems introduced multidimensional data structures known as "cubes," allowing data to be viewed and analyzed across multiple dimensions such as time, geography, and product categories. This multidimensional approach enabled businesses to perform complex queries swiftly, facilitating advanced data exploration techniques like slicing, dicing, drilling down, and pivoting. By pre-aggregating data and optimizing storage for read-heavy operations, OLAP systems significantly improved query performance and empowered organizations with deeper insights into their data.

![](https://ucarecdn.com/3dbabbe7-30ed-417d-88f5-16c35c2fea92/)

OLAP became a cornerstone of Business Intelligence (BI), with tools like Hyperion, Cognos, and Microsoft SQL Server Analysis Services (SSAS) leading the way. These systems enabled better decision-making and elevated the data analytics field significantly. 

To summarize, the inception of OLAP servers was due to the limitations of the OLTP systems and the need to provide two main functions: __analytics or multidimensional modeling and performance optimizations.__

But OLAP had its challenges. The in-memory architecture of OLAP servers was a double-edged sword. It enabled multi-dimensional structures that led to significant performance improvements but made it hard and very expensive to scale. Because of the RAM-based architecture, OLAP servers had to rely on vertical scaling, which has physical and economic limits. These limits became a bigger problem as the amount of data for analytical processing grew significantly. 

MPP databases and Hadoop emerged as alternatives by leveraging distributed computing and flexible data processing models. They scaled horizontally by adding more commodity servers, allowing them to handle more data efficiently. SQL on Hadoop and MPP databases paved the way for the emergence of cloud data warehouses (CDW). Following the success of SQL on Hadoop, CDWs embraced the SQL interface, adapted MPP architecture, optimized performance, and significantly simplified infrastructure management. 

A new generation of BI tools like Looker emerged to execute analytics on top of CDWs. Since CDWs are not suitable for multidimensional analytical modeling due to their tabular data structures, analytics modeling, which was previously done in the OLAP server, shifted to the BI layer, e.g. LookML in Looker, DAX in PowerBI.

While CDWs scaled more efficiently than RAM-based OLAP servers, they were not on par performance-wise, making additional performance optimizations a necessity. These optimizations hinge on __analytics modeling__, which provides context around the metrics, dimensions, and hierarchies involved, as well as how data is being queried, sliced, and diced. 

Without this context providing guidance on what to optimize, it is impossible to optimize at the CDW layer. 

Since this contextual information resides in the BI tool, BI vendors have started offering solutions that integrate analytics modeling with performance enhancements by incorporating in-memory OLAP servers inside their products and offering extract modes. Unlike direct or live connections, extracts work similarly to OLAP servers and move data from CDWs inside the BI tool in-memory engine. 

With __analytical modeling__, such as DAX in PowerBI, __and extracts__ now embedded in BI tools, the pendulum swings back toward the widespread adoption of OLAP servers—this time integrated directly within the BI platforms themselves.

That architecture works fine as long as the BI tool with the embedded OLAP server is the only consumer of the analytics models. However, once a new BI tool is added to the stack, the analytics modeling and in-memory aggregations will be duplicated to support that new tool.

For example, if an organization initially adopted Tableau on top of Snowflake, they might have built numerous calculations within Tableau’s calculated fields and used Hyper to extract data from Snowflake into Tableau. Later, the team decided to bring on Power BI to power dashboards for a new line of business. Now, analytics modeling needs to be done in DAX, alongside the existing calculated fields in Tableau. Moreover, the data itself is being duplicated multiple times: it resides in Snowflake, as the original source, and is also copied into both Tableau’s and Power BI’s in-memory engines. Data duplication inevitably leads to duplicated access control and security permissions across every environment where the data is stored.

In summary, embedding an OLAP server into the BI tool leads to the __duplication of analytics modeling, the data itself, and security permissions across all data and visualization tools__ used in the organization.

![](https://ucarecdn.com/54047caf-e14c-4738-824c-b75592bbb6f1/)

## Alternative Architecture
To avoid this duplication, we can extract OLAP server functionality, namely analytics modeling, and aggregations, from the BI layer and make it universal across all the data and visualization tools in the organization.

If we do this and make the analytics modeling and aggregations layer standalone, we will arrive at the next evolution phase of OLAP servers or the universal semantic layer idea.

![](https://ucarecdn.com/51dd6aa7-b40f-4057-819f-f84df83c28c4/)

While this architecture clearly offers the benefits from the analytics modeling and aggregations de-duplication perspective, the question around in-memory limitations remains open. Is there an alternative to in-memory aggregations for performance optimizations?

Additionally, decoupled architecture requires a communication protocol between BI and visualization tools and the standalone semantic layer. Historically, OLAP servers relied on MDX as the communication protocol. Is that still an option today, or do we need a better communication protocol?

I’ll dig deeper into these and other questions in future blog posts as I continue to explore the evolution of OLAP servers and the modern data stack.

Online Analytical Processing (OLAP)

The Evolution of OLAP

Get Cube updates to your inbox

Upgrade your data stack today