Forum Discussion
Advocate IIExcel pivot on analysis server slow, same table selection in powerbi desktop is fast
My customer wants to continue using Excel pivot.
We have a tabular model in azure analysis server.
When making a pivot on 2 columns (1 from the fact, 1 from the dimension table), it is very slow and a memory allocation error occurs.
Making the same selection in pbi desktop in a table, is no problem.
What is the difference? Both are live connections on tabular, no import?
Thanks,
Johan.
9 Replies
v-cherch-msftMicrosoft Employee
JohanSorry, that's an old link. Probably related to multi-dimensional also.
Thanks anyway.
- d_gosbell
Super User
Johan wrote:What is the difference? Both are live connections on tabular, no import?
The difference is that Excel sends MDX queries and Power BI sends DAX queries.
DAX is the "native" language of a tabular model. While MDX queries potentially have slightly different semantics. So while a lot of queries will have similar performance there are some edge cases where the engine has to do a lot more work in order to build the sort of result set that Excel expects. It sounds like you have hit one of those cases
- Johan
Yes, I found out that when you put a dax (evaluate) formula in the text field of the connection properties, it is much quicker and even imports the data into Excel.
Also I found out that the problem occurs when you place 2 dimension fields next to eachother, without a measure. In PBI (DAX) no problem, it is in Excel (MDX).
Conclusion is to find workarounds and best practices. It is undocumented behaviour.
Thanks all.
- d_gosbell
Johan wrote:Also I found out that the problem occurs when you place 2 dimension fields next to eachother, without a measure. In PBI (DAX) no problem, it is in Excel (MDX).
With Excel Pivot tables it's always been a best practice to start by adding a measure to the pivot table, then start adding dimension fields. Otherwise the pivottable generates a cartesian product of all the possible member combinations.
Power BI has a similar behaviour, but the UI actually generates an implied measure for you to prevent this (which you can do much more efficiently in DAX)
Hi,
Faced the same problem recently . Users have their's saved excel file(cube) working periodically .Suddenly out of nowhere,nobody can refresh excel connected to ssas tabular on-prem . Refreshing takes too much time and load analysis server RAM heavily . Anyone found soluton or workaround ?Thanks in advance
Hello,
I am seeing couple of cases which is releated to AAS & SSAS, which is pointing to similar issue and manage to fix by doing modification to the server setting, please check this doc which I have created to fix the issue.
https://kingfishertechtips.in/calculation-items-error-server-out-of-memory/
