Excel as a DIY web service

PowerPivotPro's Core'n'Thin

UPDATE:

Over on powerpivotpro.com Rob has published several articles on handling PowerPivot under SharePoint.  One such article talked about a hub’n’spoke approach to PowerPivot deployment ( or Core and Thin workbooks as his team describes the approach). Lots of good advice (and the promise of lots of goodies to follow to help manage the whole SharePoint-side deployment).

But hey, what if you don’t have the readies (or interest, or permission) to splash out on a top-of-the-range SharePoint farm, is this Core and Thin approach to workbook development beyond your means?  No, it’s not, Excel as a web service is possible for all; not as powerful or sophisticated as with Excel Services et al. but doable in a ‘good enough’ sort of way.

But first, why would need a hub’n’spoke core/thin approach?

  • You might wish to use the power of PowerPivot but widespread deployment of Excel 2010 might not yet be feasible but you would like to share the results of your models with those without the newer technology.
  • Your model requires the power of a high-memory 64bit platform but those consuming the results only have access to 32bit platforms.
  • The model needs access to detail level data that you would wish to remain private but still need to share the model results with others.
  • You wish to adopt a more modular approach to model development, for example, supplying utility tables such as Time Dimensions or latest Price Lists as a service.
  • You wish to provide web service access to other non-Excel or non-Windows applications or to a web application.

So how is this done?

Through the magic of my microETL add-in of course! (With a little help from JSDB JavaScript scripts.).

Download microETL from http://www.gobansaor.com/microetl

Extract the zip file to a folder (make sure folder pathname has no spaces i.e. c:\testthis\ is OK but c:\test this\ is not). Open the WebService.xlsm (or WebService97-2003.xls if you’re not running Excel 2010 and/or don’t have the PowerPivot Add-in installed). Find the WebServiceControl sheet and click the Start Server button.

The workbook exposes a single endpoint “127.0.0.1:8080/xl”  and two services “service=Sales” and “service=LookupTables” (or just “service=LookupTables” if using the non-2010 workbook). The webservice returns a HTML table which is the ideal format for those accessing the service via Excel’s WebQuery (available since Excel 97). But of course with a modification to the JSDB server script (../javascript/WebService.js) many other output formats could be provided:

Is this the fastest web service provider in the world? Ah, no.

Will this “scale” as my model’s popularity takes over the world? No, it won’t.

Can I expose this web service to the public internet? No, but I guess you could front-end it with a an internet-grade reverse-proxy.

Is it secure?

The script as it stands will only allow access from the same machine (see line server.hosts=[“localhost”]), so yes. As you allow others to access it, then perhaps a simple token passing mechanism could be used to restrict access, combined with a VPN (such as Hamachi) or an HTTPS enabled reverse-proxy, such as Pound, to hide data on the wire. But for many, a combination of being behind the firewall and “security through obscurity” would be more than enough security.

Try these to access the PowerPivot driven Sales Service …

… and this to see what happens when TypeOfBike doesn’t exist…

Try these for the simple table driven service Lookup …

… and again when Lookup not valid …

Notes:

When you start the server, a Windows Console Window aka “DOS Box” will open and once the server starts your firewall will likely ask you to un-block JSDB.exe. You may safely do so as the server as configured will only accept connections from the same machine.

When you stop the service (via the Stop Server button or by sending it 127.0.0.1:8080/quit) the DOS Box will remain, so will need to be closed manually.

To manually kill the server, go to the Command Console and hit return. If you kill it using ^C or similar the COM hooks within Excel will likely remain, which could cause Excel to keep running even when you exit all your spreadsheets. If this happens, use Task Manager to kill the rogue Excel process.

If you wish to continue using Excel on the same machine as the server, maybe to try out Excel’s WebQuery functionality, open a new Excel process first. The server will time out if it finds that the process hosting the WebService workbook to be locked (which happens it you attempt to modify any spreadsheet running under the same Excel process).

If port 8080 is in use, server will add 1 until it finds a free port i.e. 8081 then 8082 etc.

UPDATE: 12th Feb 2011

See the next post for an other example of Excel as a Web Service but this time using microETL’s inbuilt Python functionality:

http://blog.gobansaor.com/2011/02/12/python-powered-powerpivot/

UPDATE: Spet 2011

See this  post for an other example of Excel as a Web Service but this time using HAMMER’s  inbuilt IronPython functionality to expose a PowerPivot model:

http://blog.gobansaor.com/2011/09/21/exposing-an-excel-powerpivot-model-as-a-web-service/

 

 

Advertisements

9 responses to “Excel as a DIY web service

  1. Pingback: Tweets that mention Excel as a DIY web service | Gobán Saor -- Topsy.com

  2. Pingback: Excel as a DIY web service

  3. Pingback: JavaScript as an Excel scripting language via JSDB | Gobán Saor

  4. Tom,

    Could you offer a multi-week course breaking down how this works.

    I would be your first student!!

    Greg

  5. Pingback: Python Powered PowerPivot | Gobán Saor

  6. Pingback: PowerPivot Nested Set Measures | Gobán Saor

  7. Pingback: Steam Powered PowerPivot | Gobán Saor

  8. Pingback: Exposing an Excel PowerPivot model as a Web Service | Gobán Saor