locked
Adding column with 12 million rows to Report Filter = No Performance! RRS feed

  • Question

  • Hello All,

    I've loaded 12 million rows into a table in a powerpivot workbook and want to add a Report Filter for the pivot table that allows the end user to filter on one of these 12 million values.

    No matter what I try, when adding this column (unique invoice ID's) to the Report Filter of a pivot table, and type in a value to filter by, the filter window displays "Searching...." and takes MINUTES to complete.

    To me and the end users, this isn't acceptable.  This is a 4 cpu box with 16 gigs of ram.  I imported the data sorted hoping it would help, but still takes minutes to filter.  Is this what we should expect?

    Note, the powerpivot workbook only contains this one table with 12 million rows and is 400 megs in size.

    Any advice on improving this performance?

    Thanks,

    Jeff.

    Wednesday, June 27, 2012 8:06 PM

Answers

  • the question that arises when i hear this requirement - how do you expect a user to select a single number out of 12M??
    scrolling down will not really be an option

    but i think the main problem is, that excel tries to get all distinct values in advances and 12M is way too much for the filter

    how about creating a hierarchy and spilt the Invoice-Ids up, e.g. by using LEFT()

    12
    + 1201
    + 1202
        + 120201
    etc.

    hth,
    gerhard


    - www.pmOne.com -

    Friday, June 29, 2012 6:59 AM
    Answerer

All replies