Excel pivot on analysis server slow, same table selection in powerbi desktop is fast

[Johan]

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.

[Anonymous]

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/

[Anonymous]

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

[Anonymous]

After several back and forth , i have found that the reason was custom format string of YoY% calculation in newly created time intelligence calculation group.

[d_gosbell]

---

@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) 

[Johan]

Thanks. The 2 fields come from a related fact table and dimension table. 

Like: dimCustomer.City and factSales.ProductGroup. 

But apparantly indeed Excel Pivot handles this differently then PBI-desktop. I thought it was the tabular model engine managing this, but apparantly it's the client's behaviour.

[v-cherch-msft]

Hi @Johan 

Please check if below article could help.

https://businessintelligist.com/2012/06/07/improving-performance-of-excel-reports-against-analysis-s...

Regards,

[Johan]

Sorry, that's an old link. Probably related to multi-dimensional also.

Thanks anyway.