Tag Archives: XMLA

PowerPivot for Excel as an XML/A Server

Over the past three months or so I’ve taken a deep dive into the heretofore unknown world of AMO. I had up until then concentrated on using ADOMD to interact with PowerPivot but as the realisation dawned on me that the xVelocity engine in PowerPivot Excel was, in essence, the same engine powering SQLServer 2012’s tabular instances, I decided AMO needed some quality time. And, Oh Boy, have I been rewarded with a host of new and exciting possibilities; e.g. partitioned update of large tables, changing data sources at runtime, automated creation of Measures and Calc Columns  and …

…the ability to communicate with the PowerPivot engine using a SOAP Enveloped XMLA request, to which PowerPivot will respond in like fashion.

But Excel isn’t a HTML server, so what use is that?

Well, HAMMER can easily enable an Excel instance as a single or multi-threaded server and that’s what I did, and then tried accessing the PowerPivot Model cube from a variety of XMLA clients, including the excellent Saiku and a new exciting XML/A client, Roland Bouman’s XMLA4js JavaScript library.

Both worked, including both the DISCOVERY and EXECUTE command sets.

Obviously to use this in production would most likely require a reverse-proxy of some sort to front-end it, and even then, would only be suitable for a small client base. It’s a bit like the circus dog riding a bike, it’s not how well he rides, it’s that he rides at all.

But this scenario of a classic web server was not what really interested me, it was XMLA’s potential role in a steam-powered server setup that piqued my interest.

I’ve been mulling in my mind a simple means of allowing a internet-enabled client (most like a HTML5 mobile focused  web app) to query a PowerPivot model from anywhere, no VPNs, no dedicated servers. By using HAMMER to enable a PowerPivot workbook to act as a “pull server”, and having come across a neat trick to allow a Google Spreadsheet to operate as a proxy between such Excel “steam-powered servers” and remote JavaScript clients, I figured I could do just that.

Getting data out of PowerPivot in a tabular format via ADOMD DAX  queries was my initial approach; the draw back with this was, that the JavaScript client would then have to handle the pivoting of such tables. Seemed a pity when PowerPivot is a powerful and efficient pivot engine. Then I discover this XMLA feature, which in turn reminded me of Roland’s XMLA4js project; I had myself a means to specify a pivot (MDX via XMLA) and the means to render, in JavaScript, the resulting multidimensional XMLA delivered dataset.  Game on!

Obviously the interaction between the client and the “server” would be asynchronous in the extreme (with, how often the “server” searched for new requests and how many “servers” were assigned to a request tunnel, determining  how responsive the interaction would be). But HTML5 with its ability to store state locally (keeping track of outstanding requests, firing notification events when responses arrive etc.) and the understanding of the users of such a service that the tool in the background is “just” an Excel instance (maybe running on their own PC back in the office) would hopefully mitigate against a “I must have a response NOW” mentality.

This method of building simple focused web-apps would not only suit standard reporting needs but could also support more sophisticated “field” applications, such as “on-site” budgeting or complex pricing/discount calculations. The PowerPivot “server” (and its powerful hand-maiden, good ol’ fashioned Excel formulas) could remotely provide the back-in-the-office heavy lifting.

So is this a poor-man’s alternative to a SharePoint PowerPivot deployment?

Yes I guess, but I prefer to think of it as an alternative approach perhaps more suited to operational BI (rather than SharePoint’s BI dashboard approach – which in any case can be cost-effectively delivered by  this  PowerPivot hosted service ).

Also, remember models developed and initially exposed from Excel via this method could as the need (or the money) appeared be hosted directly in SQL Server 2012, while still retaining the client and/or proxy elements.

I’ve modified the InProcess_oData.xlsm example in the latest HAMMER version to demonstrate XML/A serving (see the XMLA Server Stuff sheet). The service will start on port 8479 and listen for the /xmla end-point.

To download the latest version of HAMMER, go to this page on my website.


HAMMERing away at Automated PowerPivot Refresh

See here for an updated version of the VBA code that automates the refresh of PowerPivot models. Having spent the last week delving deeper into the process I’ve made a few changes.

The original code doesn’t work if more than one PowerPivot model is opened; the new method uses a combination of DMV (tabular views of AS engine’s metadata) Sessions and Object Activity (in particular  looking for ‘Permissions’ issued to a workbook’s sessions).

I’ve also allowed for a single table refresh. Again, this required some more DMV queries to determine the DimensionID associated with the table.

Excel linked tables can be refreshed (i.e. the XMLA will run OK) but it has no effect, as this doesn’t trigger a fetch of new data from the workbook. To automate linked table refreshes means using the dreaded SendKeys, nasty, but works after a fashion.

Another interesting observation, the refresh operation works fine without the PowerPivot add-in being enabled (but the associated DLLs would have been still visible and presumably used). Not sure what use that knowledge is, but interesting none the less.

So far so good, seems to be working; but as Marco pointed out, this is not supported  (but is documented and would be perfectly valid to use against a ‘normal’ AS model). So, test, test again and make sure you have a backup of any important workbooks.

I’ve also managed to get the code working in VB.NET and have ported it into my HAMMER micro ETL tool. In fact, having the ability to quickly and easily fetch and render DMV views using HAMMER helped enormously in identifying what DMV queries would help with the multi-model and DimensionID problems.

I’ve tested against Denali CPT3  (but should work with PowerPivot V1) XP SP3 32bit; 64 bit should work too (let me know if not).

To enable actions such as PowerPivot refreshes (which require access to the Excel Object model) I’ve made a few breaking changes and added some new commands.

  • BREAKING CHANGE, the main HAMMER function is no longer thread enabled (accessing the Excel Object model from within a threading UDF is not to be recommended). If you’re sure you’re thread-safe use the hammerThreadEnabled function, if you don’t know what I’m talking about, don’t 🙂 Internal Threads are not affected by this.
  • BREAKING CHANGE, commands are now case-senstive, nothing really to do with the PowerPivot changes, just something I’ve been meaning to do for sometime.
  • PPCONNECTION (New command) – will set up an ADODB connection within HAMMER to enable ADO queries against PowerPivot DMVs. Once established can  be used by subsequent HAMMER calls.
  • ADOCONECTION (New Command) – like above but for any ADODB connection, requires a valid connection string. Also, once established, can  be used by subsequent HAMMER calls.
  • ADO (Modified Command) – can still use a connection string for once-off connections but if  an ADOCONNECTION is in force will use that instead.
  • HAMMER_ppREFRESH (New Helper function) – spawns a background thread to refresh the PowerPivot model and refresh associated pivots. Background thread may be still be running when function returns. Takes two optional arguments, table (if a single refresh is required) and timeout (in seconds, to allow for long running refresh).
  •  HAMMER_ppREFRESH_inline (New Helper function) – like above but operates as a normal UDF, will not return until action is complete. In theory breaks the UDF no-side-effects rule, but appears to work fine! Also, this is the function to use if calling via VBA’s Application.Run command.
  • PPREFRESH (New command) – performs a PowerPivot model refresh but doesn’t do a connection refresh.  Main use-case is for use within VBA code to allow for finer control.

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…


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


I’ll give up Excel Pivot Tables when you take ’em from my cold, dead hands

Jedox, the company behind the open source MOLAP server Palo, has just announced an MDX driver. This means that it’s now possible to access Palo cubes using Excel Pivot Tables or indeed any tool that supports ODBO.  This is excellent news, as MOLAP to most Excel users IS a Pivot Table, and somewhat like the NRA, the NPTA’s (National Pivot Table Association’s) motto is “I’ll give up Excel Pivot Tables when you take ’em from my cold, dead hands”.

MDX/XMLA is now a de facto standard for OLAP servers, supported not just by MS SQLServer but by SAP BW, Hyperion/ESSBase and by Pentaho’s Mondrian. The new driver is not open source, nor is it for sale but instead comes free to those with Jedox support contracts. I’m sure lots of organisations will be more than willing to enter a support contract (starting at €3000 per server) to get their hands on this; think of the savings in training alone!

UPDATE: 2nd July 2009

Kristian Raue has announced on his blog that the ODBO/MDX driver will now come free with latest Palo BI Suite (both community and enterprise versions). Excellent news!

PALO ETL-Server and SAP

Jedox have just published a roadmap for their open-source ETL-Server, release date of March 2008, same date as the next release of the Palo OLAP Server. In a future release they intend to offer SAP RFC/BAPI and SAP-BW XMLA support, being an old SAP hand this looks very interesting.

There’s also a features page with a good overview of the ETL-Server’s technical architecture.