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