locked
Excel slicer does not respond RRS feed

  • Question

  • We have an Excel 2010 workbook connected to MS Access through PowerPivot. This workbook is being consumed by users without PowerPivot; for some users the workbook functions as expected, for others the slicers do not function.

    When selecting an item in any slicer, there is no response. what can cause this?

    the workbook loads with out issue, but when a user selects an item...
    1) slicer very briefly changes to highlight the one selected item
    2) cursor turns to the waiting wheel
    3) slicer does not keep selection; data does not change

    This occurs in about a second; however, on a 'responsive' computer the process with expected results takes about 2 minutes.

    Wednesday, January 4, 2017 10:48 PM

Answers

  • Hi Rogge,

    I try to reproduce your scenario, it works fine as the following screenshot. Are there lots of numbers in your resource data? If it returns error message? Could you please share more details or screenshot for further analysis?



    In addition, I suggest you update your Excel 2010 version to 2013 or 2016 version. For Excel 2010, the 32 Bit version can only address 2GB of memory which effectively limits the size of the PowerPivot workbook to about 500-700Mb.  The 64 bit version used to be limited to 4Gb file size. For Excel 2013, the maximum files size for a workbook containing a Data Model is 2 GB in the 32-bit version of Office, and the maximum memory that can be consumed by a workbook is 4 GB.  64-bit version imposes no hard limits on file size. You can upgrade your office to allow more bigger file size, for more details about updating 2010 to 2013 version, you can refer to this article.

    Best Regards,
    Angelia

    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.

    Thursday, January 5, 2017 2:59 AM
  • It is definitely a memory issue but the issue is likely caused by the 87 columns you have in the data model. Power Pivot is a column.data store and it compresses the data one column at a time. The more columns, the less efficient the compression. I am pretty confident that if you removed the columns you are not using (assuming it is a large number), the problem will go away. This is easy to test. Download power pivot utilities from here http://www.sqlbi.com/tools/power-pivot-utilities/ Install and then run the option to check for unused columns ( you may need DAX Studio too - I'm not sure) Make a duplicate copy of the workbook and delete the columns that are not used (assuming it is a large number) Save and test. If that doesn't work, you could consider changing the table design of the remaining columns from being a short, wide table to being a long narrow table. This normally requires unpivoting the data, but it does depend on the structure.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Friday, January 6, 2017 8:24 PM
    Answerer

All replies

  • Hi Rogge,

    I try to reproduce your scenario, it works fine as the following screenshot. Are there lots of numbers in your resource data? If it returns error message? Could you please share more details or screenshot for further analysis?



    In addition, I suggest you update your Excel 2010 version to 2013 or 2016 version. For Excel 2010, the 32 Bit version can only address 2GB of memory which effectively limits the size of the PowerPivot workbook to about 500-700Mb.  The 64 bit version used to be limited to 4Gb file size. For Excel 2013, the maximum files size for a workbook containing a Data Model is 2 GB in the 32-bit version of Office, and the maximum memory that can be consumed by a workbook is 4 GB.  64-bit version imposes no hard limits on file size. You can upgrade your office to allow more bigger file size, for more details about updating 2010 to 2013 version, you can refer to this article.

    Best Regards,
    Angelia

    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.

    Thursday, January 5, 2017 2:59 AM
  • oops we have Excel 2013 - this is at work so i have no control over which version of excel is installed (I requested Excel 2016 64-bit about 9 months ago)

    We have about 1.383 Million rows (the work book saves to about 55MB, the back end data is about 1.29 GB )

    No error message is returned; the slicers do not respond... so before and and after screen shots will be the same.

    Thursday, January 5, 2017 6:54 PM
  • Do the other users have power Pivot installed? 1.3 m rows I Is pushing it for 32 bit, but what is more important is how many columns you have

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Friday, January 6, 2017 12:01 AM
    Answerer
  • 77 data columns, 10 Computed Columns, 24 Calculated Fields

    Some have PowerPivot some do not; the workbook functions for some users w/o PowerPivot...

    This, like everyone has said, is most likely a memory issue: It works for those with at least 12 GB, the user with the issue has 8 GB. I don't know enough about the memory allocation, garbage collection, swapping, caching etc to know if 50% more RAM helps, when only 2GB is addressable by Excel.

    Friday, January 6, 2017 3:06 PM
  • It is definitely a memory issue but the issue is likely caused by the 87 columns you have in the data model. Power Pivot is a column.data store and it compresses the data one column at a time. The more columns, the less efficient the compression. I am pretty confident that if you removed the columns you are not using (assuming it is a large number), the problem will go away. This is easy to test. Download power pivot utilities from here http://www.sqlbi.com/tools/power-pivot-utilities/ Install and then run the option to check for unused columns ( you may need DAX Studio too - I'm not sure) Make a duplicate copy of the workbook and delete the columns that are not used (assuming it is a large number) Save and test. If that doesn't work, you could consider changing the table design of the remaining columns from being a short, wide table to being a long narrow table. This normally requires unpivoting the data, but it does depend on the structure.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Friday, January 6, 2017 8:24 PM
    Answerer