locked
Should PowerPivot Be This Slow / Require This Much Memory RRS feed

  • Question

  • Hi Everyone,

    I'm on my first PowerPivot adventure and I wanted to find out if I should expect it to be this slow, or if I need some work in tuning my queries.

    Environment: Windows 7 64-Bit, Office 2010 SP1 64-Bit, PowerPivot 64-Bit on HP EliteBook with 8-GB of RAM.

    I've setup the following four tables in PowerPivot:

    • Computers: Queries view on MS SQL 2008 Server, returns ~8,000 rows and 10 columns.  The view is a result of a few DB joins, but they're all databases that exist on the same phyical host (and in the same instance).  There is an added 11th column to the table in PowerPivot where I've setup the forumla (changed table & column names to make the data generic):

      =If(NOT IsBlank(COUNTROWS(FILTER('LOOKUP_ONE', [hostname] = [name]))),"LOOKUP_ONE",If(NOT IsBlank(COUNTROWS(FILTER('LOOKUP_TWO', [hostname] = [name]))),"LOOKUP_TWO",""))

      This query runs in about 3 seconds in SQL Server Management Studio.

    • Installations: Queries a view in MS SQL 2008 Server (same as above) which also is the result of a few DB joins, all the same host.  This query returns ~660,000 rows across 21 columns.  There are no DAX formulas in use here.  If I query the db directly, it takes about 35 seconds to run in SQL Server Management studio (tighted up quite a bit from where I started, but still working on optimizing).

    • LOOKUP_ONE: Queries a database that isn't on the same phyical host as the last two, and for very strict security reasons, cannot be db_linked.  It returns just one column and about 2,500 rows.  Essentially just used for a "Is the computer on this list?" lookup.

    • LOOKUP_TWO: Is an import from a CSV file that is refreshed daily.  Unfortunately the system that houses this data uses a proprietary database that is secured and cannot be queried from 3rd party tools.  It returns just one column and about 850 rows.  Like the lookup above, this is another "Is the computer on this list" query.

    The Problem:

    For Computers, I can bring the data in rather quickly and I have 5 Slicers that allow me to look at the data in all kinds of amazing ways.  The slicers are connected to a flat Pivot Table, and a Chart.  The refresh is really quick (1s?) when I use them, and instant when I use filters on the Pivot Table.

    My problem comes when I look at the Installations.  Monitoring the Task Manager, the Excel process will chug and chug up to 5.25 GB of memory before completing any time I do any filtering of the data.  I had expected it to be all cached and easily manipulated, but it's at a couple of minutes each time, and a spike in memory.

    The Resolution:

    So, I'm looking to find out how to work in these ~660k rows with PowerPivot without needing so much processing power.  As I type this I wonder if it's my long and possibly complex DAX forumula that's processing so much so I think I'll try and remove that, but I'd appreciate any other suggestions.


    Friday, May 18, 2012 3:11 PM

Answers

  • Is it possible to create a relationship (in PowerPivot) between the LOOKUP tables and the Installation table?  You can then use the RELATED function.   I generally try not to use FILTER in the PP table as for each row in the source table a new table instance is being created for each FILTER.  This makes it very memory heavy.  Especially with a large table.   I'd also try and use CALCULATE instead of FILTER.

    Do you need all 21 colulms in the computer table?   If not, remove them from PowerPivot to save memory.

    An additional question.  How much memory do you have installed in your workstation?

    Sorry there are more questions here than answers but you seem to have a good grip of DAX hence by changing the model a little you should get the result you're looking for.

    Lee

    • Proposed as answer by Elvis Long Monday, May 28, 2012 10:19 AM
    • Marked as answer by Elvis Long Wednesday, May 30, 2012 1:17 AM
    Monday, May 21, 2012 11:59 AM

All replies

  • You might want to try using the CONTAINS function.

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

    Friday, May 18, 2012 8:20 PM
  • On 2nd thought, calculated columns should not affect performance when manipulating a pivot table. The problem is probably one of your measures.
    Friday, May 18, 2012 8:24 PM
  • Is it possible to create a relationship (in PowerPivot) between the LOOKUP tables and the Installation table?  You can then use the RELATED function.   I generally try not to use FILTER in the PP table as for each row in the source table a new table instance is being created for each FILTER.  This makes it very memory heavy.  Especially with a large table.   I'd also try and use CALCULATE instead of FILTER.

    Do you need all 21 colulms in the computer table?   If not, remove them from PowerPivot to save memory.

    An additional question.  How much memory do you have installed in your workstation?

    Sorry there are more questions here than answers but you seem to have a good grip of DAX hence by changing the model a little you should get the result you're looking for.

    Lee

    • Proposed as answer by Elvis Long Monday, May 28, 2012 10:19 AM
    • Marked as answer by Elvis Long Wednesday, May 30, 2012 1:17 AM
    Monday, May 21, 2012 11:59 AM