Category Archives: PowerPivot

You may have been wondering what’s the driving force behind the various changes I’ve made to HAMMER over the last few weeks,  namely threading support, a simple HTTP server and JavaScript. The driving force is to better position HAMMER (and through it, Excel) as a fractional horsepower HTTP server (see this post for more on fractional horsepower engines). Features such as threading and JavaScript are useful for many things; threading, for example, makes debugging scripts easier (see the Debug sheet and code in the sample InProcess_oData workbook) and also makes long running ETL processes easier to control and monitor. But, enabling the set-up of simple task-specific behind-the-firewall data servers, with as little ceremony as possible, is the ultimate goal.

But why, what purpose do these mini servers serve?

They’re obviously not intended as beyond-the-firewall public servers, they wouldn’t scale or be secure enough for such a task. Providing in-house feeds to other web enabled clients would be a more sutable task. For example, providing a feed from a “hub” workbook containing a PowerPivot model to other “spoke” workbooks (PowerPivot enabled or not) – a poor man’s alternative to doing the same via a SharePoint farm, if you like.

But it’s another seemingly unrelated technology that’s really sparked my interest in perfecting the fractional horsepower server: HTML5.

Generally when people think of HTML5 (if indeed they think of it at all), it’s mobile platforms that come to mind. (As it’s primarily Apple and Google, through their shared WebKit browser core, that have driven the development and adoption of HTML5). So what has this to do with Excel and the boring, but oh so profitable world, of corporate IT? Well, next time you’re in any spot where the global mobile workforce gathers, airport waiting lounges, hotel lobbies, etc. look at the technology kit that they’re using.

Only a few years back, the vast majority would have had a Windows laptop, if indeed they had any “data processing” device. Now, many, if not all, will either be using a smart-phone or a tablet device (iPhone or iPad but also increasingly Android powered phones/pads). All of these workers are still likely to have a laptop in a carry case or back in the hotel room, and certainly will have a laptop/desktop or their workplace desks. But on the move mobile is where it’s going.

So how do front-line datasmiths respond to this? Currently many of us build reporting solutions and really-simple-systems using Excel as the delivery agent, moving all or part of this to a mobile delivery agent will inevitably become increasingly attractive and/or demanded.

MS is already responding to this, e.g. PowerPivot and standard Excel spreadsheets are capable of being rendered via SharePoint’s Excel Services. But what if you don’t have access to a SharePoint farm, or you need a more robust UI, such as could currently be built using a VBA/.NET add-in? This is where HTML5 and fractional horsepower servers come in.

For me, there are two aspects of HTML5 that I think will make developing and deploying such “systems” possible and relatively easy:

• In built graphics and animation support within HTML5 supporting browsers, this makes it easier to display and build business charts and dashboards (see Google’s chart API playground to see what’s possible).
• Local off-line storage.

It’s HTML5′s local storage, that’s makes a fraction horsepower server scenario possible. In traditional web apps, it’s assumed that:

• 1st the client is always connected to a server,
• and that the server provides both the layout (html, javascript, css) and all the data (REST APIs etc.) that the web app consumes.

Now with HTML5 apps, the client doesn’t need to be always connected to its main server or to its data server(s). It can go offline, or it can stay connected to its main server (perhaps a public-internet-facing S3 hosted domain), and every now and then make contact with one or more data servers (which can be safely positioned behind the firm’s firewall).

An example:

A firm’s Sales Reps come into the office every Friday for wash-up meetings, to record sale completions and to get their journey-plans for the following week.

Each rep has a desktop computer, where they interface with the firm’s various systems. One such set of “systems” are PowerPivot based models that report on the year’s forecasts and actual sales.  Part of the process of preparing for a sales visit is creating a set of sales reports for each customer to be visited, last year sales, this year’s targets, and so on, sourced from the various PowerPivot models. Although the production of the reports is largely automated via Excel macros, currently the resulting sheets have to be printed.

There’s been talk of company supplied laptops for years and the budget for them has now at long last materialised. The reps however, have expressed a preference for using iPads when customer-facing, mainly because the sales conversation often require not only presenting the prepared sales reports and charts but also flicking through many of the 100 odd product manuals. Being able to hand around an iPad with high quality glossy images (and videos) of this year’s new products, plus a sales projection chart for the same products, is, they contend, a winner.

A simple mobile sales reporting app is therefore developed (using the JoApp framework and Google’s Chart API and this pure JavaScript columnar database) to cater for the type of sales reports the reps require. The existing Excel automation code is enhanced with a HAMMER server. The reps new iPads’ web-apps are configured to automatically download prepared and ad-hoc reports when they log-in to the office network.

This has worked so well, the reps now want the ability to feed back sales target changes, that they also wish to record on their iPads via another really-simple-system, to their personal Sales Plan workbooks.

Is this as simple as using a “pure” spreadsheets solution, no, but it’s nearly as simple as building a VBA/.NET powered Excel application to do the same. The problem with many Excel “applications” is that they often push Excel beyond its “comfort zone”. The benefit of a hybrid solution like above, is that Excel gets used where it’s really useful and powerful (reports and models, data gathering and dispersal) while at the same time taking advantage of the freedom and cost-benefits (and fun!) of the emerging mobile web.