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:
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:
[sourcecode language="text" padlinenumbers="true"]
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]))
)
)
)
)
)
)
[/sourcecode]
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.
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:
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.
UPDATE 8th September 2020: This problem has finally been fixed as part of the general SuperDAX performance improvements in Analysis Services 2019 CU5. See https://powerbi.microsoft.com/en-us/blog/improving-power-bi-performance-when-querying-multidimensional-models/ for more details.
Thanks for the heads-up and for the profiler reminder as well, this is a good one to keep an eye on.
Yikes – this is a big one w/ MD’s heavy reliance on caching for performance… which version of SSAS were you working with?
2014, but it applies to all versions of SSAS Multidimensional as far as I can see.
Not nice
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
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.
Does anybody know if there is any update from Microsoft on that?
Not that I know of, sorry.
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.
Disabling the Total not be the solution for this issue. I tried, the problem remains 🙁
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??
It’s hard to say, I’m afraid. Do both instances have exactly the same database/data on them?
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.
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.
For it is worth, I am getting the same performance problem with SSAS 2016 SP2, CU3. My performance testing was with Excel 2010 which was OK (5 – 15 seconds) Vs Power BI for same and similar reports of at least 2 minutes but commonly the queries never finished. The cube already has more aggregations than I would normally use (to mitigate reported performance issues), as many attribute relationships as I could define and many hierarchies.
Changing the Power BI table so that totals were turned off fixed the issue.
With numerous reports from our users from various different cubes and reports (Power BI included), I will be following this one up on Monday morning.
Thanks heaps to Chris for publishing.
I just saw SP2 CU3 has been released – might check this out. Not holding my breathe though since this is more likely to be solved by a change to the DAX that Power BI is using
Just noticed my typo…SP2 CU4 has just been released
Alguien ha encontrado la solución?
—-
Has anyone found the solution?