Welcome to the first in my blog post series on evaluating the different aspects when looking to migrate from Azure Analysis Services (AAS) to Power BI Premium Per User (PPU).

Apologies for this taking a few extra weeks to get started, life has been super busy, but as they say “Better late than never”.

In this post I am going to compare the Query Performance of an AAS Cube compared to a PPU Cube.

Details on the cube

I was fortunate enough to be able to get a copy of the Exact Aggs AAS Model, which I got from the following blog post: How to Make DAX Distinct Counts 100x Faster with Exact Match Aggs

I took this model and uploaded it to AAS. I then configured my AAS instance to use the S4 size which is the same size that is available for PPU, 100 GB RAM.

My next step was I then re-created the AAS cube using Power BI desktop, and uploaded this to my PPU App Workspace.

I also enabled the Query Caching on the PPU dataset as shown below.

UPDATE: 2021-05-14

I received some additional information on how Query Caching works from the Power BI team with regards to the Query Caching which only applies to published reports that are opened in the browser.

Finally, after all that work (And it was more work than I anticipated) I was ready to run some query performance comparisons.

Query Performance Testing for PPU

To do the query performance testing I completed the following steps below for each query.

  • I used Power BI desktop to connect to the PPU dataset.
  • Next, I created the following visuals below to replicate a report which users would typically use.
  • I then used the Performance Analyzer in Power BI Desktop to capture each of the queries from each of the visuals.
    • NOTE: The reason I used the exact query was to replicate what would happen if I were a user interacting with a report.
  • As always, I then went to DAX Studio
    • I copied each query into DAX studio and using the Run Benchmark, I ran the query 3 times on cold and warm cache.
  • Below are the different DAX queries that I used.
    • Sales Amount by Date and Colour (Line Chart)
    • Sales Amount and Colour (Table)
    • Sales Amount by Country and Country (Stacked Bar Chart)
    • Sales Amount by ModelName and Size (Stacked Bar Chart)

Below is the outcome of the queries, I am comparing the difference for the cold and warm cache in milliseconds (ms), where I was taking the time from AAS and subtracting it from PPU.

For example: Diff Cold (ms) = [Cold Duration AAS (ms)] – [Cold Duration PPU (ms)]

Observations

Below are my observations from the query performance comparison.

  • Cold Cache
    • In all queries AAS was faster than PPU
    • It must be noted that the difference is in milliseconds.
    • And to be fair it is so small that no one would actually notice any difference.
  • Warm Cache
    • Here it was interesting that the differences were a lot closer.
    • Once again because the gap was even smaller and with it being less than 16ms (according to Marco and Alberto less than 16ms is not worth measuring, if I recall correctly) the difference is almost nothing.
  • Result
    • In my opinion with the difference being less than 200ms the performance between AAS and PPU is almost identical.

I know one of the questions would be what about distinct counts, I did some testing and I found that distinct counts measure performance can vary based on a lot of different factors. And for me to try and give you the reader an accurate overview would not be valid. I have worked with some larger models and to get the distinct count measure quicker has resulted in a lot of optimizations on different aspects of the dataset. And each dataset is different, which means different optimizations need to be investigated.

Summary

Whilst each dataset is different, I do feel that my observations in terms of query performance are valid. I have done some testing on production datasets for multiple customers and the performance was similar to my observations in this blog post.

I had decided to use the AAS S4 size in order to get an accurate comparison between AAS and PPU. I feel that this was reflected in the query performance and numbers shown.

It has been fun doing this, and I hope that you found this useful.

My next blog post will be on Scalability.

As always, any comments or suggestions are most welcome.