none
Excel Plugin Performance

    Question

  • I've got a performance problem with MDS and the Excel plugin for one of my MDS clients.  Here are some env specs:

    1.  Model would be characterized as a medium sized model on the high end.

    2. 2+ entities have over 75,000 members

    3.  4+ entities have nearly 100 attributes

    4. 2+ entities have nearly 20 domain attributes

    5. There is no row level security but most entities have attribute deny permissions on 20% of the attributes

    6.  The model performs well from the web portal but is a train-wreck from the Excel plugin.

    My question:  Besides following Microsoft's recommendations for memory, SAN, and processor/cores is there anything that can be done tuning wise to improve the load time for the query/filter window  and the load of members after a filter scenario is chosen?  We have run tests with the optimal env hardware specs that Microsoft recommends and while better are not acceptable for my client.

    Also, does anyone know if the product road map for the Excel plugin will have plans to address these performance concerns pertaining to entity loads and filtering?  


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com

    Friday, September 13, 2013 3:26 PM

Answers

  • We've determined that a custom solution via the MDS web service API is the only viable solution.  The Excel plug-in is just too slow for medium and large size models.

    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com

    • Marked as answer by Chad - SWC Monday, September 30, 2013 5:03 PM
    Monday, September 30, 2013 5:02 PM

All replies

  • We've determined that a custom solution via the MDS web service API is the only viable solution.  The Excel plug-in is just too slow for medium and large size models.

    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com

    • Marked as answer by Chad - SWC Monday, September 30, 2013 5:03 PM
    Monday, September 30, 2013 5:02 PM
  • Have you tried to filter the data in the sheet to exclude unneeded attributes and, especially, DBAs? When a DBA is loaded in Excel, its entity is also loaded in order to populate the cell value-picker. If anything can be excluded, then once you get the data filtered, you can save it as a Favorite query. When you load the query, the filtering will be applied server-side and you should see some improvement. Favorites can also be exported to file that can be shared with others (each person's permissions will be applied when they access the data).

    You can experiment with the batch size and also the server message size setting - these can allow more data per round-trip request.

    http://technet.microsoft.com/en-us/library/hh710037.aspx

    I also encourage you to open a Connect bug for this issue. I believe this is the right place:

    http://connect.microsoft.com/SQLServer/

    Wednesday, October 09, 2013 10:17 PM
  • Yes.  Filtering does help and all  the users are taking advantage of it.  Unfortunately, it's really hard to explain to them why they need to wait 30-60 seconds for the filter screen to come up. The time it takes for the filter to render is completely unacceptable and I hope this is going to be addressed in a future CU or Hotfix. The current state of the Excel Plug-in is such a wreck I have to make sure new prospects understand what they are getting into when they decide to do an MDS project with SQL MDS 2012.  I am completely convinced these issues can be overcome hopefully sooner than later.  


    Chad Dotzenrod SWC | TECHNOLOGY PARTNERS 1420 Kensington Road, Suite 110 Oak Brook, Illinois 60523-2144 http://www.swc.com

    Thursday, October 31, 2013 3:02 PM