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?