refresh in DAX keeps spinning RRS feed

  • Question

  • I connect to multiple external SQL data source connections.  2 of my tables refresh just fine, the other 2 spin and spin, never refreshing.  Each table holds less than 300K records but there are multiple calcluated columns/measures.   I cannot create a 'primary key index' as each row completely makes the record unique.
    Any help would be greatly appreciated.

    Monday, February 24, 2014 3:49 PM


  • There could be many causes for this so it would be hard to pinpoint based on a brief description. It could be anything from query design to server location/network speeds.

    Unless it is timing out and throwing an error, they are probably refreshing but just taking a really long time to complete.

    In my experience a slow refresh is generally related to either the SQL query for importing the data into Power Pivot or, as you mentioned the calculated columns.  Measures have very little impact during processing or refresh.

    While 300K records in normally not much data from a Power Pivot stand point, it may be struggling if you have a large number of columns in each table.  The less columns the better with Power Pivot so only import what you really need.  Also, the number of unique values in column impacts performance.  In Power Pivot, you want the fewest number of high cardinality columns that you can get away with, especially in your fact tables.

    If there are complex joins and/or other calculations in the SQL import query, that could also lead to poor performance.  Making sure the queries are as optimized as possible will help.  Maybe asking the DBA to create views in the SQL source that are exactly what you need is an option?

    Complex calculated columns also have a high impact at processing time, so look at optimizing those as well.  While FILTER() and X iterators (SUMX, MAXX, MINX, etc.) are incredibly powerful and useful, they can be extremely resource intensive if not used properly.  This often means trying to avoid using them to iterate over large fact tables If there are any calculated columns that you don't really use in your analysis, get rid of them.  If there are any that can be recreated in the SQL data source instead of Power Pivot, do so if you can.

    Any one of these suggestions (or a combination of them) may help and sometimes dramatically.  Otherwise, you may be stuck with just being patient and waiting for a long refresh to complete.

    Good luck!

    • Proposed as answer by Michael Amadi Thursday, February 27, 2014 4:20 PM
    • Marked as answer by Elvis Long Monday, March 3, 2014 9:59 AM
    Wednesday, February 26, 2014 10:53 PM