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.