Category Archives: Python

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 “” endpoint.

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

For an Excel 2007/2010 table named InvoiceHeaders use “[%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…


HAMMER and Threads Redux – The adventure continues…

Sometimes, like Scott of the Antarctic, your data shaping activities may need to “go outside” of Excel and it ” may be some time” before you return. That’s primarily why the HAMMER.exe command-line version exists; data can be packed up via the DELEGATE or SUBMIT commands; picked up by external HAMMER processes (I often use JSDB’s Grid micro-messaging JavaScript toolkit to orchestrate such workflows, but perhaps now I’ll use this – Amazon’s latest gem – AWS SWF !); then returned safely (unlike Scott) to Excel for further processing.

But, when “going outside” is not desirable or feasible, another solution is required, namely threads. There are those who regard threads as evil and I too would have to admit I generally would not use them without good reason. But sometimes they’re needed, usually to keep a controlling UI (in this case Excel) responsive and capable of monitoring and managing background long-running tasks.

I’ve already added threading capability to HAMMER via the hammerThreadEnabled function and via “internal HAMMER threads” (using NET4.0’s latest parallel programming additions).

Both of these “lock” the Excel main thread until complete, with hammerThreadEnabled being also dependent on Excel’s dependency graph to determine if threading will happen or not. So I figured I needed to bite the bullet and add a new set of threading commands to better handle long-running tasks, be they tasks that eventually return data or a status to the main Excel thread (such as a long running save to ( or fetch from) a high latency web service end point), or those that run continuously (such as a Excel as a web service). The main new commands are:

  • PYTHONTHREAD – like PYTHON but spawns a thread to run to Python script. (NET4.0 runtime required)
  • JAVASCRIPTTHREAD – as above, but using JAVASCRIPT plus no need for NET 4.0.

Both commands “take ownership” of the HAMMER session’s SQLite database  and return the new thread’s ID (in the form PYTHREAD1,PYTHREAD2, JSTHHREAD3 etc), and are likely to be the last command in a HAMMER function call’s sequence.

The thread ID can then be used by the following helper commands to monitor, re-join and clean-up such threads:

  • SQLFROMTHREAD – like SQL but takes two arguments, 1st is the thread id, and the 2nd is the SQL to apply against that thread’s SQLite database. e.g. = … “JSTHREAD1″,”Select * from messages”,”SQLFROMTHREAD” … In general, only Select statements would be issued against this database, but SQLite is thread-safe so data updates are possible but with obvious potential for SQLITE_BUSY locks and time outs. (Likewise, the thread itself can communicate back to main thread using an APPDB connection, as APPDB is always attached uniquely to the main Excel thread).
  • THREADRETURN – will report on whatever return values (be that an error, or on success, a string or a table value) once the thread completes. Again, it uses the thread ID as its argument. If the thread is still running, the command returns “Waiting”.
  • DELETETHREAD – will attempt to terminate an already running thread. It will then, or if already terminated, clean-up and close any resources associated with the thread, mainly its database, but also its Python and/or JavaScript engine instances.

It’s also possible to issue a HAMMER call that will re-join a completed thread’s database, this will happen if a HAMMER function call’s 1st argument is a valid thread-name e.g. =HAMMER(“JSTHREAD2″,”Select * from results”,”SQL”). If the thread is still running, the HAMMER function will exit with a return value of “Waiting”. With this, it’s possible to not only access the thread’s database but also its Python and/or JavaScript engine context. Multiple such HAMMER calls can be made until a DELETETHREAD command is issued or the Excel session is terminated.

As SQLite is thread-safe, SQL can be used as a simple and robust method of inter-thread communication. If the pattern of threads only updating their “own” database while the main Excel thread updates only “APPDB” is followed, very little locking or timeout issues should arise (ha, famous last words – okay, not as famous as Scott’s). There’s also nothing to stop more adventurous inter-thread single database usage if the need or impulse arises (“Do ya feel lucky Punk, well do ya!”). And don’t forget, normal disk-based databases can also be used alongside, or instead of, the more usual in-memory ones.

This use of SQLite falls neatly into my view of SQLite not so much as a database but as means of mapping and managing  shared memory using SQL as an  API.

It’s often said that SQLite should be viewed not as an alternative to Oracle/MySQL/MSAccess/whateverRDBMS but as an alternative to a file-open statement. But for me, I primarily use SQLite as an alternative to rolling-my-own in-memory shared data structures  (be that across threads or application stacks).

Two other new commands added with this version are:

  • VERSION – will return the version of the internal HAMMER (XLite) engine is use.
  • LISTCOMMANDS – returns the list of commands available.

There’s also been a small breaking change to the JAVASCRIPT command. Scripts will now only return a table if the return value is a JavaScript Array of Arrays (List of Lists) object or a JSON string representing a LOL. All other objects will be converted to a JSON string representation or scalar string value.

A few extra JavaScript focused helper methods have also been added to the passed-in “db” object:

  • db.jsCreateTable(tableName, JSONaLOLorLOOobject) where the 2nd argument can be a “JSON table” in either List of Lists or List of Objects format. A table will be created using either the first “line” of the LOL or the “names” of the objects fields in the LOO to provide the column names (no types applied, as no need most of the time).
  • db.jsInsertRows(tableName,JSONaLOLorLOOobject) – as above but will insert/append the pass’d “rows” to the specified table.
  • db.jsonSQLQuery(SQLSelectStatement,typeOfJSON) will return a table in List of Lists format if typeOfJSON = “LOL” or as a List of Objects if “LOO”.

There’s also a new JavaScript command: loadAssembly(dotNetAssemblyName) – which will load a .NET assembly allowing the use of any .NET library within JAVASCRIPT.

Both the JAVASCRIPT and threading commands are at a very early stage, only minimal testing so far, so use with care.

To download the latest version of HAMMER, use this link.

HAMMER Alongside, as a COM Server

Although it has always been possible to call HAMMER from within VBA via the Application.Run method, this is a somewhat clunky way of doing so and it can also be very inefficient, particularly for tight loops. But now, with this release (V 1.2.0 (Beta) ) core functionality is exposed as a COM Server, which means easier and more efficient interfacing between VBA and the .NET multi-threaded enabled world of the datasmith’s HAMMER.

I call this “HAMMER Alongside”, to differentiate it from HAMMER Inside where I use HAMMER internals to craft stand alone XLLs. With this COM Server method, the standard HAMMER add-in (installed or just-in-time registered) is needed alongside either a VBA add-in or a VBA enabled workbook. The extra “moving parts” are easily justified by the extra flexibility that this method allows (particularly to those with a reasonable grasp of VBA, but perhaps lacking any familiarity with the .NET world).

By learning a small amount of IronPython (here’s a good starting place) it’s relatively easy to hook up VBA to any .NET library through HAMMER, without having to invest time and money in learning heavy duty development environments such as Visual Studio. Using “Internal Threads, it’s also possible to take advantage of .NETs multi-threading capability from VBA. (Note: both these options require .NET4).

So with VBA becoming a first-class language with regards to HAMMER, why offer Python as an alternative scripting choice? Well, one reason is to offer a means to access .NET’s power as per the last paragraph. But, the primary reason, is related to the ability of HAMMER transformations to be “detached” from Excel and run via HAMMER’s command line tools. The .NET 4 version of the command line tool supports Python, so it is possible to initially build out a micro-ETL transformation within Excel (using Python to perform the business logic alongside SQL) and then transfer that logic to the command-line tools with a minimum of modification.

This could be useful , for example, when a PowerPivot model moves to the server, its related HAMMER-powered micro-ETL processing could do likewise (most enterprise ETL tools support call-outs to command-line processes).

The COM server can be accessed from VBA only via late-binding like so:

Set comServ = CreateObject(“hammerCOMServerV1”)

The server’s methods are:

  • hammer(…) – works the same way as the UDF version.
  • hammerVersion() – returns HAMMER version, again the same as UDF version.
  • hammerVersionOK(version) – e.g.  isOK= comServ.hammerVersionOK(120) will return TRUE if the current version is >= V1.2.0
  • arrayResize(anArray) enables the creation of “toFit” UDFs.
  • arrayToSheet(anArray) likewise for “toSheet” UDFs.
  • arrayToRange(anArray,pasteToWhereString) enables “toRange” UDFs.
  • hammer_ppRefresh_inline(optional table,optional timeout) – refreshes a PowerPivot model, again the same as its UDF equivalent.

Download the latest version of HAMMER from here …

Exposing an Excel PowerPivot model as a Web Service

In my last post I demonstrated a simple way to interrogate an Excel based PowerPivot model using either MDX,DMV or the new DAX Query Table commands. This is a great way to dig deep into a PowerPivot model, for example, this new Denali DMV discover_calc_dependency can be use to  describe the dependencies between a model’s DAX measures.

Being able to directly access the PowerPivot model opens up all sorts of interesting options, one such is a web service.

In the past I’ve written about exposing Excel as a simple DIY web service, see here for a JavaScript example and here for a Python version. Both were capable of serving PowerPivot data but they both had to trigger events in Excel to do so.

With this newly discovered ability to query the model directly, I figured a third attempt at a PowerPivot Web Service was overdue.  This time I’m using the new kid on the block, HAMMER, and in particular using its in-built IronPython interpreter.

I could have built a simple server using VB.NET or C# and added it as a HAMMER command, but the nature of a web service makes it hard to frame as a parametrised command. It was for this type of requirement that I added the ability to script using Python, i.e. situations where a generic command would always come up  short, for example:

  • Should the service allow only local connections?
  • Should it use authentication, and if so, what type?
  • Should it allow free-format commands or offer only packaged commands?
  • Should it use HTTPListener (which is in-built and very powerful, but requires Admin privileges) or use a more basic socket based utility that can run without Admin privileges.
  • Should it block or handle asynchronous requests. (For services that access the Excel Object Model, it has to be blocking, but perhaps if the service simply accessed the AS engine, it night support multi-threaded queries? Must try it sometime.)

For this example I decided to use HTTPListener (in blocking mode and without authentication) and to expose services that accept free-format MDX,DMV or (if Denali) DAX Table Queries.

See the PPWebService workbook example in the latest (V0.5.0) version of HAMMER. To activate, make sure latest version of HAMMER is installed (or use activate button on Hammer sheet) and click the Expose PowerPivot Model as Web Service button (again on Hammer sheet). This will start a server on port 8070.

The service exposes 6 endpoints:
  • /exit – this will shutdown the service.
  • /html – this will return data as a HTML table (ideal for importing into Excel using a Web Query).
  • /xml – this returns a ‘canonical’ XML table (ideal for importing to Excel as an XML Map).
  • /json – returns a JSON table (as a list of lists).
  • /odata – returns a ATOM-base ODATA feed suitable for direct import into PowerPivot using its Data Feed import facility.
  • /csv – returns in CSV format.

Each service (except /exit) expects to be followed by a MDX, DMV or DAX command. e.g. /html/evaluate(InvoiceHeaders). In the case of /csv an optional file name may be specified e.g. /csv/invhead.csv/evaluate(InvoiceHeaders).

It’s also possible to issue a POST request with the command in the message body.

As with all URL requests, the commands may need to be URL encoded (use the HAMMER command “_URLENCODE” if need be).

For the latest versions and articles on HAMMER follow the HAMMER tag on my blog …

VBA Multithreading, .NET integration via HAMMER

In a previous post I urged all potential datasmiths to learn a scripting language (I suggested Python). But what of VBA, the granddaddy of the scripting world? Well yes, if you have a need to automate Excel then you must learn VBA. VBA is to Excel as JavaScript is to the modern browser, its tight integration with Excel’s Object Model and its superb debugging facilities makes it the optimal choice for automating Excel.

VBA is now a one trick pony (Office automation), but Python opens all sorts of API scripting and product automation doors. My use of IronPython as HAMMER’s scripting language is one such door, a door to the otherwise mainly-closed-to-VBA world of multi-threading and easy .NET library integration. [UPDATE: Feb 2012 – HAMMER now also offers JavaScript as a scripting option]

The HAMMER function can be called from VBA using the Application.Run command like so…

retArray = Application.Run(“DATASMITH.HAMMER”,inArray,”Select dept,count(*) from table1″,”SQL”)

… the 1st parameter is the function name, parameter two is the 1st argument to the function, parameter three is the 2nd and so on.

By utilising HAMMER’s IronPython functionality (requires NET 4.0 runtime), VBA routines can access the full power of the .NET platform with data passed back and forth using tables. Admittedly for many complex .NET utilities utilising VB.NET or C# may be a better approach (due to better IDE and debugging features of such languages) but for standard library calls, IronPython is an ideal option. It also has the benefit that the “code” can be stored within the workbook.

HAMMER also offers the power of multi-threading to VBA via its internal threading functionality (requires Excel >= 2007 and Net4.0 runtime). The multi-threading example in the hammerThreads.xlsx workbook could easily be wrapped in VBA code, perhaps to allow it to be controlled by a user-form.

I’ve added two new commands specially designed for use within VBA scripted HAMMER scenarios:

  • APPDB – Opens an application-wide shared in-memory database. This will allow tables (and Python objects) created in one function call to be accessible in an other function call (assuming both issue the APPDB command as their 1st command). This replicates the functionality of microETL which by default exposes a application-wide SQLite in-memory instance and a common Python workspace.
  • CLOSEAPPDB – This will close and clear the shared c#-SQLite and IronPython instances. Equivalent of microETL’s xLiteReset() function.

Be careful not to if you …

  • use the APPDB instance from in-cell UDF calls to HAMMER that are likely to be scheduled as multi-threaded (the helper functions HammerToSheet & HammerToFit are safe, as they are always single-threaded),
  • or use within “internal threaded” HAMMER commands

as although c#-SQLite is thread safe, the implementation logic is not. UPDATE: Feb 2012

I’ve added new threading features to HAMMER, see here, and my implementation is now thread-safe (well I think it is!); however, the warning still applies, think twice before updating a single database instance (such as APPDB) across multiple threads – but read-only access should work fine.

Here’s a list of the HAMMER commands implemented so far …

You can download the latest version of HAMMER here …