PowerPivot for Excel as an XML/A Server

Posted onJuly 10, 2012|Comments Off on 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 serversetup 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.