HAMMERing away at Automated PowerPivot Refresh

See here for an updated version of the VBA code that automates the refresh of PowerPivot models. Having spent the last week delving deeper into the process I’ve made a few changes.

The original code doesn’t work if more than one PowerPivot model is opened; the new method uses a combination of DMV (tabular views of AS engine’s metadata) Sessions and Object Activity (in particular  looking for ‘Permissions’ issued to a workbook’s sessions).

I’ve also allowed for a single table refresh. Again, this required some more DMV queries to determine the DimensionID associated with the table.

Excel linked tables can be refreshed (i.e. the XMLA will run OK) but it has no effect, as this doesn’t trigger a fetch of new data from the workbook. To automate linked table refreshes means using the dreaded SendKeys, nasty, but works after a fashion.

Another interesting observation, the refresh operation works fine without the PowerPivot add-in being enabled (but the associated DLLs would have been still visible and presumably used). Not sure what use that knowledge is, but interesting none the less.

So far so good, seems to be working; but as Marco pointed out, this is not supported  (but is documented and would be perfectly valid to use against a ‘normal’ AS model). So, test, test again and make sure you have a backup of any important workbooks.

I’ve also managed to get the code working in VB.NET and have ported it into my HAMMER micro ETL tool. In fact, having the ability to quickly and easily fetch and render DMV views using HAMMER helped enormously in identifying what DMV queries would help with the multi-model and DimensionID problems.

I’ve tested against Denali CPT3  (but should work with PowerPivot V1) XP SP3 32bit; 64 bit should work too (let me know if not).

To enable actions such as PowerPivot refreshes (which require access to the Excel Object model) I’ve made a few breaking changes and added some new commands.

  • BREAKING CHANGE, the main HAMMER function is no longer thread enabled (accessing the Excel Object model from within a threading UDF is not to be recommended). If you’re sure you’re thread-safe use the hammerThreadEnabled function, if you don’t know what I’m talking about, don’t 🙂 Internal Threads are not affected by this.
  • BREAKING CHANGE, commands are now case-senstive, nothing really to do with the PowerPivot changes, just something I’ve been meaning to do for sometime.
  • PPCONNECTION (New command) – will set up an ADODB connection within HAMMER to enable ADO queries against PowerPivot DMVs. Once established can  be used by subsequent HAMMER calls.
  • ADOCONECTION (New Command) – like above but for any ADODB connection, requires a valid connection string. Also, once established, can  be used by subsequent HAMMER calls.
  • ADO (Modified Command) – can still use a connection string for once-off connections but if  an ADOCONNECTION is in force will use that instead.
  • HAMMER_ppREFRESH (New Helper function) – spawns a background thread to refresh the PowerPivot model and refresh associated pivots. Background thread may be still be running when function returns. Takes two optional arguments, table (if a single refresh is required) and timeout (in seconds, to allow for long running refresh).
  •  HAMMER_ppREFRESH_inline (New Helper function) – like above but operates as a normal UDF, will not return until action is complete. In theory breaks the UDF no-side-effects rule, but appears to work fine! Also, this is the function to use if calling via VBA’s Application.Run command.
  • PPREFRESH (New command) – performs a PowerPivot model refresh but doesn’t do a connection refresh.  Main use-case is for use within VBA code to allow for finer control.

Advertisements

27 responses to “HAMMERing away at Automated PowerPivot Refresh

  1. Pingback: Automating PowerPivot Refresh operation from VBA – The Code | Gobán Saor

  2. Pingback: Automating PowerPivot Refresh operation from VBA | Gobán Saor

  3. Pingback: DAX Table Queries in Excel | Gobán Saor

  4. Tom, thanks for this… It works for me…
    Only one problem – some of my PowerPivot tables update fine, but then I get a ‘Run Time error ‘-2147467259 (80004005)’, Operation Cancelled. ‘
    This only happens when pulling in large amounts of data (over 100,000 rows) – from an Access database. Other than this, it’s perfect – any suggestions?

    • Rob,

      Your fetch is being timed out. If you’re using the VBA code see line:

      If doXMLA(cnn, lDatabaseID, lDimensionID)

      change to :

      If doXMLA(cnn, lDatabaseID, lDimensionID,500)

      ..or however many seconds your largest import takes (the default is 30 seconds).

      If using HAMMER the optional 2nd argument to HAMMER_ppREFRESH and HAMMER_ppREFRESH_inline specifies the number of seconds to wait.

      Tom

  5. Thanks for the rapid reply Tom,
    I’m using VBA, so thanks – issue now solved. As I think I said before – you are a genius!
    Rob.

  6. What exaclty does the xmla portion due in the VBA code? Can I just get rid of it?

  7. It’s what forces PowerPivot to do a refresh!

    It’s the most important line of code.

  8. I was afraid you’d say that. The code works perfectly on my home PC but in my office it errors out on comm.Execute in the doXMLA Fucntion. I keep changing the timing in Sub Refresh so far I am at 500 seconds. Any Ideas? I am using the PPRefresh only. Is that a possible issue? I am attempting to add your code into my own so I can schedule the refreshes from Windows Scheduler. Thanks!

  9. Are you getting the same error as Bob above? If so you’re being timed-out, go the full hog and set it to 5000.See what happens.

    You could also refresh each table one by one.

    How long does a normal refresh take when initiated normally?

  10. Tom, thanks for such a great macro. I am using it with a large Access file and it works fine.
    Pablo

  11. Tom,
    Sorry about this, but I’m struggling to update certain PPVT Tables separately. Some tables (those that are discrete tables in Access) will update with no problem after defining them as ‘ltable = “tablename” in the VBA. Others (with queries and linked tables) crash out with this error: http://db.tt/LIuwwSxa
    They refresh separately from the PPVT window, but when called from the VBA cause the error. I’ve checked permissions in the various places, and they seem OK…
    Any ideas for the next thing to try would be most welcome,,,

    Rob.

    • Rob,

      Linked tables & query based ones may be handled in a different manner than straight tables. Could you send me an example database and your code to tom@gobansaor.com and I’ll have a look at it. What version of PP are you using?

      Tom

  12. Hi Tom,

    Just emailed you the examples… I’m running v.10.50.2500.0…
    Many thanks,

    Rob.

    • Hi Bob,

      The bad news is it works fine for me, the good news is I know why it
      doesn’t work for you. You’re getting DimensionIDs like so
      R$f438cec1-d023-4cf5-a0a6-059e992edb84$981879bf-f8d9-4de0-98ef-4ad0d2c90c77
      , yes?

      In the code look for the line …

      mdx = “select table_id,rows_count from $System.discover_storage_tables
      where not left(table_id,2) = ‘H$’ and not dimension_name = table_id
      and dimension_name='<<<

      >>>'”

      .. see the

      not left(table_id,2) = ‘H$’

      ..this needs to be

      not left(table_id,2) = ‘H$ and not left(table_id,2) = ‘R$’

      … make that change and it should work.

      By chance I’m not being affected because the R$ row comes after the
      rows in $System.discover_storage_tables that provide me with a valid
      DimensionID.

      Just pure chance that this bug has not been encountered
      before (and there maybe other variations on the bug yet to be found). Might also be due to the fact I’m using Denali RC which required a rebuild of the PP database which may have a re-sorted the $System.discover_storage_tables DMV.

      That’s the problem with being a hacker, I can only work around what I
      observe, and I’d not seen this R$ row before. (Even though I should have as it would appear there’s a H$ row per each table column and a R$ row per each relationship the table is involved with).

      I’ll put out a fixed version of the code in a few days or so, but the
      above fix should do in the meantime.

      Well done Bob, you unearthed the first real “bug” in the code.

      Tom

  13. Thanks Tom,
    This fix solves it for me – I can now selectively choose which of my PowerPivot tables to refresh by putting the names on a worksheet and adding some lines of VBA to the code – I’m a happy man…
    Rob.

  14. Pingback: PowerPivot VBA Refesh Code – Bug Fix | Gobán Saor

  15. Thanks Tom for your hammer!
    I’m using code like this in my worksheet:

    lRet = Application.Run(“HAMMER”, “PPCONNECTION”, “PPREFRESH”, “OK”)
    If lRet = “OK” Then
    ActiveWorkbook.Connections(“PowerPivot Data”).Refresh
    Else
    MsgBox (“Problem – ” & lRet)
    End If

    How can I set Can I set the timeout parameter here?
    Nando

  16. Hi Nando

    lRet = Application.Run(“HAMMER”, “PPCONNECTION”,”120″,””, “PPREFRESH”, “OK”)

    PPREFRESH takes two arguments, 1st is the timeout in seconds (eg above 120 seconds, set to 0 for no timeout), 2nd allows for a single table to be refreshed (eg above set to “”, will refresh all tables).

    Tom

  17. Pingback: HAMMER and Threads Redux – The adventure continues… | Gobán Saor

  18. Arun Kumar Mohan

    Hi Tom,
    Thank for a wonderful code, it works great. Auto refresh was one of the requirement in my prototype project. You code did Magic!!!. I’ve a specific requirement here……

    I would like to pass filter values to the PowerPivot query from an excel sheet, typically what i want is – a user need to input the parameter from excel and with a button click i wanted to pass the input parameter to the PowerPivot query. Can you give some insight on this. It will be very helpful if you can give me some ideas here.

    Thanks
    Arun

  19. Arun Kumar Mohan

    Thank you Tom for the reply. I apologize for not clear on my question. I’ve a query in PowerPivot like “Select * from Table where Div in (North, South)”. I would like to know is there a way to change the Query filter from North, South to East or west?. The reason i ask this is, I’ve a query which has a where clause on SKU. I can certainly run the PowerPivot query for all SKUs and use Slicer on front end so user can filter it but there are thousands of SKU’s and retrieving everything within powerpivot will degrade its performance. I wanted user to run the powerpivot query for the sku’s they like and see the results. In that case excel size will be less and performance will good. Your input on this will be very helpful.
    Thank you
    Arun

  20. Arun,

    The slicers and row and column selectors do this already, on selection they issue filter requests to the PowerPivot engine (via MDX) that ensure only the data required is returned (pre aggregated) to the PivotTable cache. Obviously all the potentially useful data must 1st have been loaded into PowerPivot, but as a columnar datastore, it’ll have no problem handling cardinalities in the 1000s range (many millions could however be a problem).

    Tom

  21. Pingback: Why use a dimensional data model for PowerPivot? | Gobán Saor

  22. Getting the same runtime error as rob even after changing If doXMLA(cnn, lDatabaseID, lDimensionID,5000)

  23. Alex,
    Might not be a timeout issue then; the error can 1 of many possible errors; check the comments on the various blog posts for ideas. But stick with it, it does work.