locked
Powerpivot slow- seems to be unnecessarily repulling data RRS feed

  • Question

  • I entered a sql query into POWERPIVOT and it retrieved of 29,000 rows of data from a Sql Server Database. I then built a POWERPIVOT pivot table to view the data. Now every time I make a change to the pivot table (move a field, set a filter, etc) it takes about 10 sec to serve up the report- which is WAY TOO LONG given the amount of data involved. While it does this I get the status messages in the lower right reading:

    ....RUNNING OLAP QUERY ... followed by... READING DATA....

    When I copy these sames 29,000 rows to a separate excel file and build a normal pivot table on top of the data, the resulting pivot table returns results almost instantly. So the problem is not the quantity of data.

    What I suspect is that the POWERPIVOT pivot table is running the sql query against the Sql Server database every time I manipulate the pivot table. I don't want this. I just want a report based upon the one data pull I did, and then want the ability to get fresh data when  needed.

    Is there some setting that I have to change in order to stop POWERPIVOT from rerunning the sql code?

    On the other hand, maybe POWERPIVOT requires 10 secs to simply recalculate a pivot table? And it isn't requerying the database? Is that possible?

    Thanks.


     




    • Edited by JohnZofo Thursday, October 18, 2012 2:24 PM fix typos
    Wednesday, October 17, 2012 11:02 PM

Answers

  • Hi John

    PowerPivot never re-queries the underlying source when slicing/dicing a pivot table.  All data is loaded in-memory, and the queries are then executed against this in-memory dataset.

    One of the reasons why PowerPivot slows down (even with small datasets like the one in question) is because

    a) there are many other pivot tables on the same workbook.  As such, refresh actions on one pivot table will attempt to refresh all other pivot tables (and charts)

    b) data may come from more than one in-memory table.  This is because Excel sends and MDX query to the PowerPivot engine, and if multiple tables are involved, the resulting MDX crossjoin statement can be very inefficient.




    Javier Guillen
    http://javierguillen.wordpress.com/

    • Proposed as answer by Elvis Long Tuesday, October 23, 2012 10:48 AM
    • Marked as answer by Elvis Long Friday, October 26, 2012 5:31 AM
    Friday, October 19, 2012 8:15 PM
    Answerer