Forum Discussion

Johan's avatar
Johan
Icon for Advocate II rankAdvocate II
7 years ago

Excel 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 an...
Need Help
Johan's avatar
Johan
Icon for Advocate II rankAdvocate II
7 years ago

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's avatar
d_gosbell
Icon for Super User rankSuper User
7 years ago
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's avatar
    Johan
    Icon for Advocate II rankAdvocate II
    7 years ago

    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.