locked
Cubes performance drops significantly when multiple dimensions are used RRS feed

  • Question

  • Hello,

    Our main fact table has 6 dimensions associated with it. Performance is good when up to 3 dimensions are used, but as soon as 4 or more dimensions are used in the Excel pivot table then performance drops significantly.

    In some cases, Excel comes back to life after half and hour and displays:
    Memory error: Allocation failure. If using a 32-bit version of the product, consider upgrading to the 64-bit versio or increasing the amount of memory available on the machine.

    I am pretty sure memory/CPU is not a problem as my PC has 32 GB of RAM and Intel Xeon E5-2660 v2 @ 2.20 GHz 10 Cores, 20 Logical Processors. Upgrading to 64-bit Excel is not an option as my company does not support it. The PC itself is running Windows 7 Enterprise 64 bit with Excel Professional Plus 2013. The SQL Server version is 2014.

    So, is there anything I can do to improve the performance?



    • Edited by amir tohidi Monday, April 8, 2019 2:59 PM Added SQL Server version
    Monday, April 8, 2019 2:48 PM

Answers

  • Hi Will,

    I finally managed to get hold of Excel 2016.

    I am pleased to say that the same report that was crashing Excel 2013 (after almost 20 minutes of hanging) is now refreshing in under 30 seconds :-)

    Thanks for your help.

    Thursday, April 18, 2019 3:19 PM

All replies

  • Hi amir,

    Thanks for your question.

    Per your description, you could try these possible options to improve cube performance.

    1) Optimize cube and measure group design

    2) Define effective aggregations

    3)Use partitions

    4)Write efficient MDX

    5) Use the query Engine cache efficiently

    6)Ensure flexible aggregations are available to answer queries

    7)Tune memory usage

    8)Tune processor usage

    9)Scale up where possible

    10) ScaleOut when you can no-longer scale up.

    For more details, please refer to:

    Best Practices for Performance Tuning in SSAS Cubes

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 9, 2019 7:34 AM
  • Thanks for your reply Will. I will look at the blog post and get back to you. 

    I still don't understand, however, why Power BI Desktop is able to render the same report connected to the same Cube without any issue. In fact, the performance is excellent. Any thoughts?

    Wednesday, April 10, 2019 2:05 PM
  • Just came across this. Looks like this is an Excel issue:

    https://www.purplefrogsystems.com/blog/2015/05/excel-creates-inefficient-mdx/

    Wednesday, April 10, 2019 2:38 PM
  • HI,

    u know - putting attributes on axis SSAS is doing a Crossjoin? -also putting attributes from same dimension results in crossjoins (SSAS will force an AutoExists as soon these attributes are not seperated by attributes from other dimensions) meaning Dim1 (10 Attributes) * Dim2 (10 Attributes) *......

    Did u also take a look at the different MDX of Excel and PowerBI?

    Kr J

    Thursday, April 11, 2019 2:01 PM
  • Hi Kr,

    Whilst I have the ability to look at MDX, our business users don't have the technical skills to do so. Also, from their point of view, they want to use our Cube to perform their own ad-hoc analysis. So, it is not possible to review every single report that is not performing well.

    I read somewhere that Excel 2016 might have fixed the poor MDX issue of Excel 2013. I am in the process of getting our infrastructure people to upgrade my PC to Excel 2016 to see if that makes a difference.


    • Edited by amir tohidi Thursday, April 11, 2019 2:12 PM
    Thursday, April 11, 2019 2:12 PM
  • Hi amir,

    Have your upgraded your PC to EXCEL 2016? Any updates about your issue?

    Also, you could read related article which is talking about Memory Considerations about PowerPivot for Excel for better understanding.

    Memory Considerations about PowerPivot for Excel

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 12, 2019 8:17 AM
  • Hi Will,

    I finally managed to get hold of Excel 2016.

    I am pleased to say that the same report that was crashing Excel 2013 (after almost 20 minutes of hanging) is now refreshing in under 30 seconds :-)

    Thanks for your help.

    Thursday, April 18, 2019 3:19 PM
  • Hi Will,

    I finally managed to get hold of Excel 2016.

    I am pleased to say that the same report that was crashing Excel 2013 (after almost 20 minutes of hanging) is now refreshing in under 30 seconds :-)

    Thanks for your help.

    It's happy to hear that you have solved your issue. And thanks for your sharing your experience.

    Well, since your problem has been solved, please kindly help close the thread by marking useful reply as answer.

    Thanks for your cooperation.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 19, 2019 8:57 AM