Automating PowerPivot Refresh operation from VBA

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 ..

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 ..

<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. [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.

 

Advertisements

19 responses to “Automating PowerPivot Refresh operation from VBA

  1. Nice! Do you think it is possible to share a VBA macro ready to use?

    • Will do Marco; the “code” I have now is very much the suck-it-and-see-scripter type (VBA may be old, but she’s still the best scripting language out there to delve deep into Excel’s heart, especially if you know how to use the debugger and immediate window). I’ll put together a more polished version and share it within the next week or so.

      Tom

  2. I know what you mean about VBA vs VSTO! 🙂

    Marco

  3. VSTO is only for those who like a challenge 🙂

    I use ExcelDNA for all my .NET Excel requirements, more powerful, much simpler, faster and open source.

  4. Tom, unpolished will do, we can refine to our own needs. Come on man, I am impatient to see it, you know this is big 🙂

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

  6. Pingback: PowerPivot automatikus frissítése - Kővári Attila szakmai blogja - TechNetKlub

  7. Hi Goban,
    as far as I understood, linked tables won’t refresh with the macro? Am I right?
    Thanks!
    Gabriel

  8. So there’s no way to refresh linked tables automatically? (without opening PP window?)

    • This code will run without error if you specify a linked table but it has no effect. It is however fairly trivial to use SendKeys functionality to force the PowerPivot window to open and hence refresh any linked tables. The problem with the SendKeys method was any automation after the opening of PP was impossible (but could be done with more sophisticated software), that’s where this code comes in handy in handling the refresh of non-linked tables.

      Another option is to output any excel tables to a CSV (as the example workbook does) and then load.

      Tom

  9. Thanks a lot for your help Tom. I’ll try to output the Excel tables to a CSV file and the reload them.
    Gabriel

  10. Hi Tim
    Recently I tried to make a connection with the AMO library to the Vertipaq engine but without success. It would be very handy to retrieve information like QueryDefinitions and other stuff. Did you already try something alike?
    With ADOMD I succeeded to retrieve PowerPivot from one workbook into another by launching MDX queries.

    • Haven’t tried connecting with AMO, would be a bit different being .NET rather than COM; but I’d be reasonably sure it can be done. So keep at it, and let us know if you succeed.

      Tom

  11. OK. I will and hopefully I can bring some good news 🙂

  12. Tom

    After hours of searching, trying and failing I finally managed to make a connection to PowerPivot with the AMO library. The connection string I used looks like this: “Provider=MSOLAP;Data Source=$Embedded$;Locale Identifier=1033;Location=WorkbookFullName;SQLQueryMode=DataKeys”
    where ‘WorkbookFullName’ is the path and name of the workbook. You only get connected when the Vertipaq engine is loaded.
    Now documentation can be automated. This way I can export the data sources, columns and formulas to a worksheet and only add comments and descriptions manually, no longer copy and paste of column labels and formulas.

  13. Eddy,

    Excellent!

    I presume this is from within a .NET add-in?

    What’s the purpose of the SQLQueryMode=DataKeys , is it to allow access to the underlying meta data?

    Have you managed to create a standard ADO/ADOMD connection within .NET using the location attribute? I tried using in it VBA but it didn’t work (luckily I then discovered the always-open connection on the data connection object).

    Tom

  14. Tom
    Correct. I investigated if it was possible with VBA and DSO but by my opinion it is not. So the only alternative was .NET. I agree with you that VBA & Excel is the best marriage ever.
    The exact meaning of SQLQueryMode=DataKeys I haven’t figured out yet, I don’t have that much experience with SSAS. I just stumbled on it in next thread on Marco’s site
    http://sqlblog.com/blogs/marco_russo/archive/2011/08/08/workaround-for-lack-of-powerpivot-api-in-excel.aspx. With trial and error I finally got a connection when PP was loaded in memory.
    The same connectionstring can be used to connect with AMO as with ADOMD. Concerning ADOMD you may/can use the content you can find in the data connection properties of “PowerPivot Data”.