oData in-process Server – auto refreshing PowerPivot linked Excel tables

To test out the new threading facilities in HAMMER I picked on that perennial question that I get asked “Can your PowerPivot refresh code, refresh linked tables?” to provide me with a suitable task.

As regards the original code, the answer is no; linked tables cannot be refreshed via the XMLA method I use. You could of course, simply automate the export of the Excel tables to, say, a CSV format and then refresh from the disk. Biggest problem with that approach is PowerPivot’s insistence on using absolute file addresses, which makes sharing the resulting workbook more cumbersome (requires that the saving folder on each machine be the same). Another solution is to use some variation on SendKeys to automate the “button push”, can be done, but pretty it’s not.

So what’s an automate’r to do? Use PowerPivot’s oData import facility, that’s what.

In a previous example I had demonstrated using oData to provide a feed from a master-workbook to client work-books, so I already had code in Python to generate a simple oData feed (it’s essentially an ATOM feed; yeah, the same format used by many blog engines (usually alongside RSS2.0), and offered as a output format by many web services, Twitter and Google Docs, for example).

Rather than using HTTPlistener (which is very powerful, but requires Admin privileges) this time I used HAMMER’s internal simple HHTP/1.0 socket based server (which is multi-threaded and uses callbacks to communicate with either Python or Javascript GET/POST handling functions). Using a PYTHONTHREAD command enabled me to spin of the oData-generating-code as an in-process server running on an independent thread; leaving the main Excel thread to fetch the necessary ranges from the workbook and then issue a PowerPivot refresh command (both of theses operations require being run single-threaded and in the main thread).

In PowerPivot itself, the range tables are fetched using a “127.0.0.1:8081/range/” endpoint.

So, for example, to fetch a table from a range whose top-left-hand-corner is Sheet2!A1, use the URL “127.0.0.1:8081/range/Sheet2!A1”.

For an Excel 2007/2010 table named InvoiceHeaders use “127.0.0.1:8081/range/InvoiceHeaders[%35All]” (the %35 is URL encoded “#”).

While the workbook is in “manual mode”, the in-process server will fetch on-demand any valid tabular range (but be careful not to “lock” the workbook, by, for example, editing a cell, while this is happening).

When in “automated refresh mode”, the VBA code will lock out any on-demand refreshes (it’s best in any case, to close PowerPivot’s window before doing any XMLA refreshes); then use the list of ranges provided to fetch the tables required, and initiate an auto-refresh.

Everything should go smoothly as long as the list matches exactly (case sensitive) the URLs assigned to tables with PowerPivot.

To try this out, download the latest version of HAMMER, go to the distribution folder, open the InProcess_oData workbook.

In the Control sheet, click the Enable HAMMER button, followed by the Start oData Server button. If you think port 8081 is likely to be already in use on your PC, go to the PythonCode sheet and change to a free port.

Then go to the Data sheet, change the tables, and refresh PowerPivot using the “arrow” button provided.

Make sure to click the Stop Server button, before exiting Excel, otherwise the port-listening thread will keep Excel alive in the background.

Of course, auto refreshing same-workbook “linked” tables is but one use this could be put to. Other possible uses would be taking advantage of Excel powers that the PowerPivot import facility still lack, such as XML Maps. Or, using 3rd party libraries or bespoke code to access “non-standard” (i.e. the majority that don’t use ATOM!) web services or data sources (such as XBRL).

Have fun…

Advertisements

10 responses to “oData in-process Server – auto refreshing PowerPivot linked Excel tables

  1. Pingback: Automating PowerPivot Refresh operation from VBA | Gobán Saor

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

  3. Pingback: Excel – as a fractional horsepower HTML5 server | Gobán Saor

  4. Pingback: Excel as a Google Visualization API Data Source | Gobán Saor

  5. My powerpivot in excel is hiding some of my columns (values). The powerpivot data-column is still included in the fieldlist, as values (sum of) – but after a refresh they disappeared mysteriously. And they are not hidden (as you may choose for columns and rows) as the column are still there, but they are empty.

    Any suggestion on how to bring them back in my excel pivottable ?

    I got some 8 other column-values in my pivottable, and they work like a charm. And those two that is missing has similar properties to the others, mostly sum of..

    Any input will be helpful.

    /Bjorn

  6. Bjorn,

    Is this related to using the above code and/or VBA refresh, or just a standard manual refresh?

    Tom

  7. Yes, I have implemented a test of Hammer refresh – and thanks for the great code!!. Appreciate your sharing experiences.
    Sorry I missed this in my comment. The updates works like a charmt. I did however get this bug(?) with the hidden values.

    • Can you send me an example workbook (tom at gobansaor dot com), showing this happening? What version of PP are you using? Was the PowerPivot window open when you issued the auto refresh (not a good idea)?

      • The problem is solved!

        It seems that there were problems with consistency between the list of fields in the PivotTable Tools tab (options), and the field list that is under power pivot tab. My column data were visually added to the list of fields in the PivotTable Tools tab, but had disappeared from the field list, which is under power pivot tab.
        I entered the fields again in the field list that is under power pivot tab, ran update again – and whola`, everything is working correctly.

        Thank you very much for your prompt response and courtesy.

        /BG

  8. That’s the sort of problem I love to solve i.e. those that solve themselves 😉

    What you’ve described is a known bug in PowerPivot.