none
Clearing/changing table filters when data is refreshed in SharePoint

    Question

  • For memory reasons my Power Pivot model cannot load the required fact source data in my 32 bit Excel client. Now, is there any way that I can have a table filter on my fact table in Excel and remove the filter when published to SharePoint or before the Data refresh schedule in SharePoint executes? I would like to be able to process the model with complete dataset at SharePoint level.

    In this case I'm not able to create any objects/views in the source database, which could do the trick.

    Any input on getting around the problem is very appreciated.

    /Stig

    Thursday, November 07, 2013 9:13 PM

Answers

  • Hello,

    As we know, the PowerPivot data model have a natural advantage that data is highly compressed, all of data should load into the PowerPivot data model. Currently, I don't think we have such functionality to achieve your requirement.

    Thanks for your understanding.

    Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, November 12, 2013 8:02 AM

All replies

  • Hi Stig,

    I suggest you take a look at the following PowerPivot data model limits:

    Product or platform Maximum Limit
    Excel 2013

    32-bit environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add-ins that run in the same process. A data model’s share of the address space might run up to 500 – 700 megabytes (MB), but could be less if other data models and add-ins are loaded.

    64-bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources.

    SharePoint Server 2013

    Maximum file size for uploading to a document library:

    • 50 megabytes (MB)  default
    • 2 gigabytes (GB)  maximum

    Maximum file size for rendering a workbook in Excel Services:

    • 10 megabytes (MB)  default
    • 2 gigabytes (GB)  maximum
    Excel Web App in Office 365

    250 megabytes (MB) per file upload

    10 MB rendering limit for Excel workbooks

    We have a limits that can't update exceed 2GB Excel workbook to SharePoint Server. How about the size of your PowerPivot data model? If your PowerPivot data model exceed 2GB size, I think we should consider reducing the PowerPivot model data.

    For more information, please see:
    Data Model specification and limits: http://office.microsoft.com/en-in/excel-help/data-model-specification-and-limits-HA102837464.aspx

    Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, November 12, 2013 2:05 AM
  • Thanks Elvis,

    The question is more functional in the sense that I would like to have a model in Excel that is processed with few data. Then when uploaded (as a small file) it should be refreshed witout the "client " filter and become a bigger file.

    So I thought of this more of as a functional issue, but the size might be real my issue. For now I think I have a go with a 64 bit Excel and figure out the file size. Any input on my functional aspect is welcome.

    Thanks Stig

    Tuesday, November 12, 2013 7:06 AM
  • Hello,

    As we know, the PowerPivot data model have a natural advantage that data is highly compressed, all of data should load into the PowerPivot data model. Currently, I don't think we have such functionality to achieve your requirement.

    Thanks for your understanding.

    Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, November 12, 2013 8:02 AM