Although the latest CTP3 release of PowerPivot has addressed many of the short-comings of the original release (allowing drill-thru for example), there’s still one major missing: a published API accessible from VBA (or even .NET).
Like Marco Russo, I would settle for the ability to automate the PowerPivot refresh function. What’s doubly annoying about this is that there’s obviously an undocumented method of doing this as Predixion are doing it and so are PowerPlanner.
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 ..
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…
… 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 ..
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. [UPDATE: Mar 2012, but here’s a solution to the linked table refresh problem]
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 publish proven code.
UPDATE: Here’s the code.
Ideally the PowerPivot window should be closed during the refresh as the tables displayed by it will not show as updated, no problem in that the model that the workbook’s pivot see will be, but can be confusing.