SSAS Multidimensional Caching-Related Performance Problems With Power BI Tables

Last week I was doing some performance tuning for a customer using Power BI on top of a large SSAS Multidimensional cube via a Live connection. Some of their reports were performing particularly badly and I uncovered a problem with the DAX generated by Power BI for tables with a large number of rows, fields from two or more large hierarchies and totals turned on.

The problem is very easy to reproduce; I’m going to do it using a simplified version of the Adventure Works cube that contains only the Date and Product dimensions. Take a look at the following table from a Power BI report:

image

It has the Date attribute from the Date dimension, and the Product attribute from the Product dimension, on rows and two measures on columns. The table has a large number of rows in it (both Date and Product are fairly large hierarchies) and if you look at the DAX generated by Power BI you can see that it only requests the first 501 rows. That’s fine – the real problem is that Power BI also generates a second DAX query to get the two values displayed in the Total line at the bottom of the table. The DAX looks something like this, and is easily identifiable because it uses the Row() function:

EVALUATE
  CALCULATETABLE(
    ROW(
      "Sales_Amount", 'Internet Sales Facts'[Sales Amount],
      "Tax_Amt", 'Internet Sales Facts'[Tax Amt]
    ),
    KEEPFILTERS(
      GENERATE(
        KEEPFILTERS(VALUES('Product'[Product.Key0])),
        CALCULATETABLE(
          FILTER(
            KEEPFILTERS(VALUES('Order Date'[Date.Key0])),
            OR(
              NOT(ISBLANK('Internet Sales Facts'[Sales Amount])),
              NOT(ISBLANK('Internet Sales Facts'[Tax Amt]))
            )
          )
        )
      )
    )
  )

This query has something in it – I don’t know what – that means that it cannot make use of the Analysis Services Storage Engine cache. Every time you run it SSAS will go to disk, read the data that it needs and then aggregate it, which means you’ll get cold-cache performance all the time. On a big cube this can be a big problem. This is very similar to problems I’ve seen with MDX queries on Multidimensional and which I blogged about here; it’s the first time I’ve seen this happen with a DAX query though. I suspect a lot of people using Power BI on SSAS Multidimensional will have this problem without realising it.

This problem does not occur for all tables – as far as I can see it only happens with tables that have a large number of rows and two or more hierarchies in. The easy way to check whether you have this problem is to refresh your report, run a Profiler trace that includes the Progress Report Begin/End and Query Subcube Verbose events (and any others you find useful) and then refresh the report again by pressing the Refresh button in Power BI Desktop without changing it at all. In your trace, if you see any of the Progress Report events appear when that second refresh happens, as well as Query Subcube Verbose events with an Event Subclass of Non-cache data, then you know that the Storage Engine cache is not being used.

image

Also look at the Duration column in the trace for these events which shows the time in milliseconds that they took to execute. This will tell you how much of an impact this problem is having on your report refresh times.

The easy way to stop this happening is to turn off the totals row in the table:

image

Displaying the totals in a separate table also seems to avoid the problem, although of course it doesn’t look as good. Only putting one hierarchy in the table apart from your measures, also seems to solve the problem. You could also try all the usual methods to improve SSAS performance such as building aggregations on the cube.

The dev team is aware of this problem but it’s unlikely to be fixed in the short term.

15 responses

  1. Hi Chris,

    Currently I am suffering right the same problem on one query !!!

    Thank you so much for sharing and give some light on this issues

    Alberto

  2. Thank you for sharing this! I was just facing the same problem on Profiler, but i did not realized that only disabling the Totals would be a workaround.
    It looks to be a problem related to DAX (PowerBI access MD with DAX). I am mostly using the SSAS MD, but I have a similar model with Tabular and the problem there is quite the same. I also tested with a similar table in Excel accessing the MD model with MDX without any problem.
    *Tested on SQL Server 2016 SP1 and SQL Server 2017 CTP2.

      • I have tested this against SQL 2017 Standard and SQL 2017 Enterprise and Enterprise doesn’t exhibit the problem in my case. We are licensed for Standard and don’t have a viable work around at this point.
        I just wanted to ask you, Chris – did you see this behavior on SQL Standard or SQL Enterprise editions?
        We thought it was related to the partitioning capability of the Enterprise edition of SQL, but could it be a bug in power bi when run against SQL Standard edition?

      • I would be *very* surprised if this was a Standard/Enterprise problem. Did you have exactly the same data and database in both cases? The problem is related to the amount of data returned by the query and the way the MDX query is written, and how it impacts the way SSAS does caching; it should behave in the same way on Standard and Enterprise Edition.

  3. Hi Chris,
    I am facing the same situation with SSAS 13.0.5149.0, but not with SSAS 12.0.200.8, it is strange. When using a Power BI / matrix visual on the 2014 version the report runs in seconds, when against SQL 2016 I’m facing what you have posted here. Both servers have similarity on configuration. both QRY on tracers runs 200 milliseconds apart, but on the SQL 2016 drags all the memory and app and server crashed. Any comment here??

      • Hi Chris (Michael here) – In response to your comment about Standard vs. Enterprise…
        Yes, the database and data are exactly the same except for the version of SQL (2017 Standard vs. 2017 Enterprise). I have a case open up with Power BI and hopefully they can try and repro.
        I did see on a SQL Profiler trace that Enterprise was indeed making several partitioned reads of the data but your article here has me confused because removing the grand total against Standard did alleviate the problem.

  4. If you do hear something back from your support case, please let me know. Performance will be better on an Enterprise Edition server because SSAS will read data from multiple partitions in parallel, but I would be surprised if caching behaved differently on Enterprise Edition and Standard Edition.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: