Few companies have had the impact on analytics as a field that Microsoft has had. Microsoft Excel is by all measures the most successful, and enduring analytics tool of all-time. While Excel wasn’t the *first* spreadsheet software, it has become synonymous with the concept of spreadsheets and is by far and away the most recognizable spreadsheet software. At this point, much like SQL, it seems like spreadsheets are going to be with us forever. I’m ok with that. I love a good spreadsheet.

However, there’s another Microsoft analytics technology that in the preceding decade has quietly captured an enormous amount of the business intelligence market. I’m talking, of course, about Power BI. The overwhelming distribution power of Microsoft, via Office 365 and other bundling and packaging channels has made both Excel, and now, Power BI ubiquitous analytics tools. There’s also some technical lineage between the two tools to unpack with Excel using the multidimensional expression language (MDX) primarily to communicate with OLAP technologies like SQL Server Analysis Services (SSAS), or more modern variants such as Azure Analysis Services and Power BI building on this with the more recent Data Analysis Expressions (DAX) language.

Today it’s my pleasure to introduce two exciting new Cube Cloud features: our Cube Cloud for Excel Add-in and our DAX API for Power BI. Both of these new features continue to build on our commitment to support the Microsoft ecosystem and will provide large compatibility and accessibility improvements for users who integrate Cube Cloud with Excel and Power BI.

## Cube Cloud for Excel 
Last year, we added [MDX](https://cube.dev/blog/announcing-the-native-mdx-api-and-excel-integration-for-cube-cloud) to our roster of API endpoints, which is most commonly used in conjunction with Microsoft Excel. With MDX, Cube Cloud was the first cloud-native universal semantic layer to support Excel as a complete replacement to legacy OLAP engines frequently used by enterprise customers. Since its release this has been a popular API endpoint, and we have big plans ahead to continue improving it.

However, MDX unfortunately has a notable limitation - it’s only available to users who are using desktop versions of Microsoft Excel on Windows platforms. This left a whole group of users - such as Mac enthusiasts, mobile users, and users of browser-based versions of Excel out in the cold. This didn’t sit right with us, given our commitment to interoperability as a truly universal semantic layer. 

Today, we are launching the public preview of our Cube Cloud for Excel Add-in for, which is available for all Excel applications that support add-ins. This add-in provides a custom pivot table UI experience that allows users to drag and drop their metrics, columns, rows and filters to easily perform detailed analysis. For more information about the Cube Cloud for Excel, please take a look at our [feature blog](https://cube.dev/blog/introducing-multi-platform-microsoft-excel-add-in-for-cube-cloud?utm_source=cube&utm_medium=blog&utm_content=cube%20for%20microsoft%20enterprise%20blog).

![](https://ucarecdn.com/ddf707e6-168f-46b7-82e1-06b42e85e6b2/-/quality/smart/-/format/auto/)

## Cube Cloud DAX API for Power BI
Cube Cloud has supported Power BI as a visualization tool via our SQL API endpoint and our semantic layer sync capability. While this works, we always knew there was a better integration method far off in the distance, __DAX__. The Data Analysis Expressions (DAX) formula language provides the highest level of compatibility with Power BI, as it is the method by which it communicates with most native Microsoft analytic backend technologies that share lineage with Analysis Services, via the XMLA (XML for analysis) protocol. 

With the introduction of the Cube Cloud DAX API, users of Power BI can now connect to models in their Cube Cloud environment over the XMLA protocol. This provides improved feature compatibility and improved performance, while helping customers retire difficult to manage Analysis Services environments. It will also enable users of modern cloud data platforms such as Snowflake and Databricks to improve the user experience of these platforms with Power BI. Cube Cloud dynamically translates DAX over XMLA to the SQL dialects these platforms speak without the need for any import modes, or moving data out of these platforms. 

Bringing the DAX API online has been a major engineering undertaking at Cube and we are very proud of the results. We hope that you’ll take a look at this release if you’re using Power BI and struggling with performance or cost concerns with Analysis Services, or Fabric capacity SKUs. You can find more information about the public preview of the Cube Cloud DAX API in the [feature blog](https://cube.dev/blog/introducing-dax-api-for-native-microsoft-power-bi-connectivity?utm_source=cube&utm_medium=blog&utm_content=cube%20for%20microsoft%20enterprise%20blog).

![](https://ucarecdn.com/3566eb39-5098-450b-a163-2462286ba659/-/quality/smart/-/format/auto/)

## Conclusion

Cube Cloud has long supported Microsoft Azure as a public cloud infrastructure and we have many customers who run mission critical workloads there today. Additionally, we have long supported Microsoft databases as data sources. Customers who run Cube Cloud on Azure enjoy an excellent platform experience, including support for a powerful identity management platform in Microsoft Entra ID. Learn more about our integrations on the [Microsoft partnership page](https://cube.dev/partnerships/technology/microsoft/?utm_source=cube&utm_medium=blog&utm_content=cube%20for%20microsoft%20enterprise%20blog).

While today’s release is geared towards improving our support of the two most popular Microsoft technologies used by data analysts - __Excel__ and __Power BI__, we are nowhere near done with our work and will continue to expand support for the Microsoft ecosystem. 

If you have any questions about today’s release, or you’d like to learn more, or maybe take these public previews for a test spin please either reach out to your Cube account team, connect via the [contact form](https://cube.dev/contact?utm_source=cube&utm_medium=blog&utm_content=microsoft%20launch%20announcement), or reach out to me directly.

Brian Bickell

All Things Cube

Improving Cube Cloud support for Microsoft Excel and Power BI

Cube for the Microsoft Enterprise

One of the key questions I raised in my recent blog post on the [evolution of OLAP](https://cube.dev/blog/the-evolution-of-olap) was about the communication protocol between a standalone semantic layer, as the next generation of OLAP systems, and the data consumption layer.

Historically, OLAP servers relied on MDX. Many BI tools supported it, but as OLAP started to fade out with the rise of Big Data and later Cloud Data Warehouses, so did MDX. While some BI tools still support it today, most vendors actively discourage its use. Even Microsoft, the original creator of MDX, introduced a modern successor—DAX. DAX has since gained significant traction within the Microsoft ecosystem, yet it never achieved widespread adoption outside of it.

At the same time, we've witnessed a resurgence of SQL across the board. Not only did NoSQL databases fail to replace classic SQL-based OLTP databases like PostgreSQL and MySQL, but SQL has also solidified its position as the dominant protocol for data processing and transformation. Every major CDW is SQL-first, modern transformation tools are SQL-first, and even streaming architectures are increasingly moving toward SQL-first approaches.

Given this landscape, SQL is well-positioned as the ideal communication protocol between a universal semantic layer and BI tools. It's ubiquitous, its adoption is accelerating, and it's already the default language for data analysts. BI tools already support SQL, and the integration ecosystem is mature—virtually every programming language has libraries to communicate over SQL protocols.

## SQL interface challenge
While SQL seems like the natural choice for a universal semantic layer interface, it comes with a fundamental challenge: SQL was designed primarily for OLTP queries and lacks native multidimensional primitives—particularly the concept of a __measure__.

It's easy to introduce a new aggregation function like `MEASURE()` or `AGGREGATE()` to query measures, but the real challenge lies in how the query is evaluated. SQL follows a different evaluation flow from OLAP-focused languages, making it impossible to correctly execute certain analytical queries in SQL that contain measures.

The key difference between SQL and OLAP query languages like DAX is the evaluation flow:
- SQL follows a bottom-up evaluation flow, where child calculation nodes are evaluated first, and their results are passed upward in the query tree. Parent nodes do not influence child node evaluation.
- DAX follows a top-down evaluation flow, where parent nodes influence child node calculations. This allows for context-aware aggregation, a crucial feature in multidimensional analysis.

![](https://ucarecdn.com/a8ea8443-e36e-453a-a8d5-46ec9a83521e/)

To illustrate this issue, let’s look at an example. Suppose we have a __completed_percentage__ measure already defined in the semantic layer. Now, we want to query it from a BI tool using SQL:
```sql
SELECT
  DATE_TRUNC('day', created_at),
  MEASURE(completed_percentage)
FROM (
  SELECT
    city,
    state,
    status,
    created_at,
    completed_percentage
  FROM orders
  WHERE state = 'CA'
) inner_query
GROUP BY 1;
```

If SQL evaluates it bottom-up, we get the following inner query:
```sql
SELECT
  city,
  state,
  status,
  created_at,
  SUM(CASE WHEN status = 'completed' THEN 1 END) /
    NULLIF(SUM(CASE WHEN is_shipped = true THEN 1 END), 0) 
    AS completed_percentage
FROM orders
WHERE state = 'CA'
GROUP BY 1, 2, 3, 4;
```

Here’s the problem:
- Since SQL evaluates the inner query first, it aggregates the completed_percentage measure too early, before the outer query processes the data at a higher level.
- The outer query then tries to aggregate the already-aggregated value, leading to incorrect results.
- In an OLAP-style top-down evaluation, this measure would be correctly computed at the final grouping level, rather than prematurely at the row level.

This mismatch in evaluation strategies is one of the biggest challenges in using SQL as a communication protocol for a standalone semantic layer. 

### Solution

The solution to this problem is to rewrite the incoming SQL query so that it can be correctly evaluated bottoms-up. This means unwrapping all measure calls and ensuring aggregations are placed at the appropriate level in the query structure.

If we take our example query and rewrite it like this, we will get the correct results:

```sql
SELECT
  DATE_TRUNC('day', created_at),
  SUM(CASE WHEN status = 'completed' THEN 1 END) /
    NULLIF(SUM(CASE WHEN is_shipped = true THEN 1 END), 0) 
    AS completed_percentage
FROM orders
WHERE state = 'CA'
GROUP BY 1;
```

This transformation ensures that all calculations happen at the correct stage of query execution, avoiding the incorrect aggregation issues caused by SQL’s default bottom-up evaluation model.

Rewriting queries is not a new concept in the database world. It is a widely used technique in query optimization, where databases internally transform SQL queries to improve performance.

At Cube, we built our own term rewrite system that takes SQL queries with measures as input and produces valid, executable SQL that aligns with bottom-up evaluation. We also handle SQL dialect variations across different data sources—including Snowflake, Databricks, Redshift, and BigQuery—as each of these platforms has different syntax, functions, and optimization behaviors.

Our query rewriting system is part of Cube’s open-source semantic layer engine, built in Rust and based on E-Graph theory. This allows us to efficiently explore multiple possible query rewrites and select the most optimal transformation dynamically. We are also planning to publish our research behind this E-Graph-based term rewrite system soon—so stay tuned!


Artyom Keydunov

Online Analytical Processing (OLAP)

About Query Interfaces for OLAP Systems

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)

Cube for the Microsoft Enterprise

About Query Interfaces for OLAP Systems

The Evolution of OLAP

Get Cube updates to your inbox

Upgrade your data stack today