See below 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.
The xll (a 32bit and a 64bit version) of the next HAMMER release is included alongside the sample workbook (go to Hammer sheet and press “Enable HAMMER” button, or use the setup xls if you wish to install). You’ll need .NET 4 to run this version of HAMMER.
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.
Download the latest Power Pivot refresh code.
Pingback: Automating PowerPivot Refresh operation from VBA – The Code | Gobán Saor
Pingback: Automating PowerPivot Refresh operation from VBA | Gobán Saor
Pingback: DAX Table Queries in Excel | Gobán Saor
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
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.
What exaclty does the xmla portion due in the VBA code? Can I just get rid of it?
It’s what forces PowerPivot to do a refresh!
It’s the most important line of code.
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!
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?
Tom, thanks for such a great macro. I am using it with a large Access file and it works fine.
Pablo
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
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
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.
Pingback: PowerPivot VBA Refesh Code – Bug Fix | Gobán Saor
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
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
Pingback: HAMMER and Threads Redux – The adventure continues… | Gobán Saor