Encouraged by the fact that it’s obviously possible, armed with hints from the comments on Macro’s post, and from this
Chris Webb post and a late night twitter conversation with
Mark Stacey, I decided to try.
I used VBA, but this could also be done within a .NET add-in.
The first thing to do is get an
ADODB connection to the local embedded AS engine. I tried using the connection string details used by the Excel PowerPivot model connection (see under Data->Connections) but couldn’t get past an authentication error. There may be a way around this, but I decided to short circuit the problem by using the Excel Object Model to directly fetch the already established connection’s ADODB handle via ..
ActiveWorkbook.Connections(“PowerPivot Data”).OLEDBConnection.ADOConnection
One problem with this method is that when a workbook is 1st opened the default PowerPivot Data connection will not yet be established, needs something like a PivotTable refresh or a Cube formula call to fire it up. But again this can be automated.
Next step is to issue an
XMLA command like the one in
this post (no need for the CubeID property, but if want, you can specify it as “Model” or ”Sandbox” depending on the version of PowerPivot; “Model” seems to be the new name of the cube in the latest version).
But how to get DatabaseID? I used a “select distinct object_parent_path from $system.discover_object_activity” DMVcall to get a list of database objects and parsed out the DatabaseID from this object…
GHOME1300\LocalCube.Databases.CBBB19B2CD9B4017A8A0
… where GHOME1300 is my machine name and the DatabaseID is CBBB19B2CD9B4017A8A0
The DatabaseID can also be seen in the un-zipped Excel file but it appears to change when the workbook is loaded so will need to be refreshed each time.
The important bit of the XMLA command is this ..
<Object><DatabaseID>CBBB19B2CD9B4017A8A0</DatabaseID></Object><Type>ProcessFull</Type>
Having issued the command, the PowerPivot model will refresh all its external connections and rebuild the Model (aka Sandbox) cube. Linked Excel tables however, appear not to be affected by this, which is a pity.
The workbooks pivot tables still require to be refreshed separately, but this too can be automated via an ActiveWorkbook.Connections(“PowerPivot Data”).Refresh or a PivotTable refresh.
One thing to be aware of, is that the PowerPivot Refresh happens asynchronously (i.e. command will potentially return before the refresh has finished) therefore a delay may be required before issuing a connection or pivot table refresh. This asynchronous behaviour may be the reason why an API has not yet been provided (similar problems arise when automating CUBE formulas from VBA). There are various last_updated datetime attributes available on DMV tables, perhaps these could be used to signal when a refresh has completed.
As I said, this issues a complete refresh, I’ve not been able to restrict to a single table refresh (using the DataSourceID, the XMLA was accepted but it generated a DLL error). Those with more knowledge of XLMA processing commands might have more luck.
UPDATE: Both the asynchronous nature of the Process call (which turns out not to be asynchronous, the call actually blocks, must have been imagining things during my late night experimenting 
) and the lack of a single table refresh are no longer problems, see comments on next post.Will produce an updated example in due course. Here’s an updated version of the code.
So not quite a full blown PowerPivot API, but I’ve always said I’d be happy with a simple Refresh ability from VBA, looks like I might have it.
This is not yet fully proven, so proceed with caution. Hopefully over the next week or so I’ll attempt to put together a version of this for inclusion in HAMMER and/or microETL. UPDATE:
Here’s a HAMMER version.
UPDATE:
