PowerPivot – XXL, Excel on Steroids

In my last post about why I use SQLite in combination with Excel for datasmithing tasks, I listed the more traditional backends (Excel itself, MS Access, RDBMs & MOLAP cubes) that one would expect to “compete” with such an idea.   But I suspect that if that same post appeared  two years or so into the future, there would be a fifth contender, PowerPivot cubes.

PowerPivot (at the time it was called ProjectGemini) is due to be delivered as a free add-in to the next version of Excel (2010) ,like the Analysis ToolPak or the Data Mining add-ins for Excel 2003.  (See this OLAP Report Project Gemini, Microsoft’s Brillaint Trojan Horse for a good overview of the tool).

Donald Farmer ,who works on the project, having seen the SQLite as the MP3 of data post and recognising that the use cases behind combining SQLite with Excel were similar to those of Project Gemini, kindly offered me a demo of the product.  Well, the phrase “Excel on steroids” has been much used in the past (in particular of add-ins such as Essbase, Palo or TM1) but this “ya gotta see”, Donald likes to call it XXL.

Millions of rows of data in-memory on a 4GB PC being “modeled” using a “user-friendly” pivot-table-like interface. And when I say, modelled, the user isn’t being confronted with concepts such as dimensions, levels, attributes, facts and so on, but a classic star schema model is nevertheless being built behind the scenes.  And it’s this model that allows PowerPivot to escape some of the inadequacies of pivot tables, e.g. allowing for rules and hierarchies to be defined.  The resulting model can then be saved and shared as a file (keeping to the document-centric ethos of Excel) but it can also be posted to and managed by SharePoint.

SharePoint will be extended to allow the IT function to manage and audit shared models to whatever degree the organisation requires, but the single file format will also allow smaller groups to share without the need for IT involvement (essential if bottom-up adoption is to be encouraged).  SharePoint will also add the “Web2.0 collaboration layer”.

How will MS make money from this if it’s free?  The first clue is the SharePoint backend, more functionality means more reasons to purchase and use MS’s server stack and the same applies to Excel itself. I, like many others, are very happy using Excel 2003 and look on Excel 2007 the same way the market in general has looked on Vista; i.e. pretty, but lacking a strong enough reason to upgrade unless forced to do so. (Excel 2007 also has the ribbon issue, not one I find a major problem myself, but others do).  But I would upgrade to a version Excel that offered Project Gemini capabilities and I’m sure others would follow (and more importantly to MS’s revenues, thousands of corporate accounts would too).

PowerPivot offers proof that MS realises, what those of us on the ground have know for years, that BI projects are in the main, Excel-centric; all the ‘hard sums’  and awkward decisions end-up back on the desktop.  MS has decided to publicly recognise that fact and profit from it. The timing is both economically and technically opportune; PC speed and cheap memory means that a huge chunk of even a large corporation’s datasets can be analysed by a PC (according to this, the median size of original data in OLAP datasets is about 5GB); and there’s obvious cost-benefits for companies facing difficult times requiring more to be done with fewer resources.

What will the effect be on tools such as Essbase, TM1, Palo etc. ?  Well, let me put it this way, if their owners are making strategic plans for 2010 onwards and they’re not taking account of the PowerPivot effect perhaps they should.  Most likely PowerPivot will help increase the overall market for OLAP tools, with the incumbents tending to specialise in their existing niches (e.g. Palo in Budgeting, with the added value of being free and open source, which has a premium over just being ‘free’).

So will I put away my Excel-SQLite fixation then? No, for two reasons:

  • PowerPivot is not here yet, and the proof of the pudding will be in the eating. Also, when it does appear it will only apply to Excel 2010 (or whatever) and as many companies are still on Office 2000 (and a few on 97!), it’ll be at least  5 years before a significant percentage of sites upgrade.
  • The SQLite addition to Excel offers not just BI capabilities but also makes a nimble ETL and data integration engine. I’m also experimenting with Amazon S3 integration to enable simple work-flows for small distributed teams (or even same-office groups where the WAN is the new LAN).

Whether you agree or not in the validity of  “workgroup BI“, be aware that MS does and it thinks that BI is about to enter a new phase,  for proof see MS’s Nic Smith’s The History of Business Intelligence video.

UPDATE: 19th Nov 2009

Last evening I downloaded for the 1st time both Excel 2010 Beta and the PowerPivot (new name for Gemini) add-in.  First impressions; yep, in the flesh it’s just as impressive as the above demo led me to believe it would be.  As I said on Twitter last night Datasmiths of the world; download the Excel 2010 Beta and PowerPivot add-in; this ya gotta see!!!

Why not join me on Twitter at gobansaor?


12 responses to “PowerPivot – XXL, Excel on Steroids

  1. Hey Tom,

    Great post and nice links – I can always rely on you to get me thinking or push me in the right direction.

    So…. as someone who is committed to Linux (for better or worse we are on the track now) how can we access something similar? Over the web? From Linux?

    Could I use this on amazon S3 or some other web service just for Clive and I to share?

    Or should I be tapping you up for the SQLite functionality on S3 or in OOo?


  2. Hi Martyn,

    Mmm… the post I intended to write next was where I explain Why Excel? (having already addressed Why SQLite?) but got side-tracked by the Gemini demo. Why I mix one of the poster children of FOSS (so free it doesn’t even have a licence!) with a spawn of the great evil one 🙂

    Well I do so because, quite simply, Excel is the best tool for serious data crunching (and by the looks of Gemini, MS intend to fight to keep it so). If you wish to be seriously cool and show that money is no object you buy yourself a Mac (I know that, from the Macs in my children’s orthodontist’s office, matching the duck-egg blue Mercedes sports parked outside). Likewise if you want to crunch data in an optimal fashion use Excel (which implies Windows, as the Mac versions’ VBA – scripting is an essential datatsmithing tool – is either absent or nobbled; and of course the coolness you spent good money on would be seriously diluted by using Excel).

    But using Excel doesn’t mean you have to buy into the whole MS stack no more than using Linux as a main platform excludes use of other platforms where they offer significant benefit. (It was the ability of Gemini to work outside of the SharePoint/SQLServer stack that appealed to me)

    You could either use desktop virtualisation or install Excel on an EC2 image (not S3, S3 is simply a ‘file server’). The problem with an EC2 image would be the latency (European images are in Dublin), remote connection delays might take away many of Excel’s touch-feely-ness. I would think virtualisation would be cheaper and more effective. Excel being highly efficient in its use of system resources could also be run very effectively from XP powered net books (with an in-built encrypted backup/load to/from S3 for example)

    As for OOo, SQLite or DerbyDB could be used as local document databases, but lacking true VBA support, many of the benefits of Excel VBA’s gigantic ‘open source community’ would be lost.

    That’s my opinion on Excel today, but although I tend to have strong opinions, they’re loosely held, if the landscape changes (and I keep a very watchful eye on it) and an open source (of which I’m a “true believer”) alternative offers a “good enough” replacement, I’ll be the first to endorse it.


  3. Good to see you posting again (it seems Twitter is taking up a lot of blogging time :-). This is excellent information.

    I agree that Excel is “the” tool of choice for business. As such they want to send you files in Excel and want their reports created in Excel. So Project Gemini will be a big hit with serious Excel users.

    I have not tried the Excel/SQLite combination you describe. Serious lack of expertise in VBA and C/C++ side are my main problem. Do you have a test prototype that you can share with the world that might help beginners like me?


  4. Hi Seán

    Good to hear from you again, and yes, Twitter has eaten into my blogging time but in a good way. I post things on Twitter than before would have either remained in my brain or in the dark recesses of my del.icio.us account. I’ve become a fan of micro-blogging, but of course blog posts will always be necessary for the “bigger ideas”.

    The other great advantage of Twitter is the people you get to know, not in a ‘yay, lets all be best-friends’ sort of way, more like how you get to know people you keep on coming across on school runs, or community events or product launches. Why not join up?

    As for Excel/SQLite I’m bit by bit building something that I intend to release, mainly by refactoring ‘old work’ (which by the nature of what we do, tends to ‘hacked together’ to a get a specific job done) into something more robust and generic. I’ve been mainly doing it for myself (but they say the best software is that which “scratches the author’s itch first”) so making it public was not my priority – paid work and life-in-general comes first – but I’m starting to think that there’s a demand out there for such a product, and more importantly for the services associated with it, so I’ve started to invest more time in finishing it off to use as a marketing ‘hook’ for my paid work. So as they say, stay tuned…


  5. I am going to look into Twitter more! And I’ll stay tuned for this product/hook. I agree that there is very little time left after you take out work and family.


  6. I noted that supposedly the CTP for project gemini will be available to Office 2010 testers and may come as early as mid to late August.

    -A different Sean. 🙂

  7. Pingback: TAG Cubes – SQLite Star Query Part III « Gobán Saor

  8. Pingback: Excel as the iPod of Downloaded Data « Gobán Saor

  9. Pingback: PowerPivot – Show Detail not allowed! « Gobán Saor

  10. Hi Martyn,

    Would you know or think if we could plug PowerPivot on top of Essbase (I was thinking about the Essbase XMLA connector)?

  11. Not sure; there’s an option to pick “other” OLEDB providers to import data, but don’t know if that would include OLEDB for OLAP drivers, easy to check.

    The better option might be to directly work with the backing star-schema models that typically feed Essbase cubes in datawarehouse environments.

    Tom (not Martyn)