Business Intelligence – PowerPivot for Excel 2010

It’s very difficult to be enthusiastic about a Microsoft product that promises to “help improve IT efficiency” when I’ve lost so much time with Windows playing up. Apologies for the rant, but when I loaded a document about PowerPivot from the Microsoft website, the processor went to 100% on some set of background tasks Windows wouldn’t let me see or manage.

This stopped me from making any progress on writing this article, which was going to be a lot longer. It will now have to be only a short introduction! Then came an error message presented from Adobe Reader. No wonder Apple doesn’t want Adobe software on their iPad!!

Anyway ... back to PowerPivot

Last week we looked at “Business Intelligence – what the hell does it mean?” David Carter pointed out that a common BI tool is the pivot table functionality within Excel. This started in Excel in primitive form right back in 1993.

Pivot tables (which are also available in other spreadsheets and BI software) can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table (called a "pivot table") displaying the summarized data.

PowerPivot (formerly known as Project Gemini) takes this a stage further as an add-in for Excel 2010. As Robert Cain at “ArcaneCode” says in his article “Introducing Microsoft PowerPivot”
“PowerPivot sits inside Microsoft Excel [2010 only]. It not only can create basic pivot tables but has all the full featured functionality of Excel at its disposal. It can format pivot tables in a wide array of styles, create pivot charts and graphs, and combine these together into useful dashboards. Additionally PowerPivot has a rich set of mathematical functionally, combining the existing functions already in Excel with an additional set of functions called Data Analysis eXpressions or DAX”

He’s summarised this article with further diagrams in a concise presentation. This also describes the relationship with SharePoint 2010 to distribute PowerPivot reports across an organisation.

Robert’s article and presentation date back to last November. From the official Microsoft site, it’s not worth seeing the videos (unless you’ve got time to waste, and haven’t eaten recently!). But it is worth reading the latest pdf I mentioned: (It seems to load better from Internet Explorer than Firefox)

PowerPivot vs SQL Server Analysis Services (SSaS)

So if PowerPivot is effectively bringing Analysis Services functionality from Microsoft SQL Server into Excel on the desktop, is there any future role for Analysis Services?

Microsoft’s view is that “power users” in the end–user community will be able to use PowerPivot for many reporting requirements, but SSAS will remain an IT-department tool for more complex requirements.

PowerPivot vs PerformancePoint

Then wasn't PerformancePoint supposed to be "Microsoft Business Intelligence"? Well yes PerformancePoint is still here (despite losing its budgeting/forecasting capability). If you've got a stiff drink to hand, then this white paper explains how to use PowerPivot within Sharepoint 2010.

PowerPivot vs OLAP

I’ve also heard it said that “pre-calculated cubes become obsolete overnight”. Perhaps suppliers of traditional multi-dimensional OLAP software would like to comment

Availability of Excel 2010

Excel 2010 will be available in all three versions of Office 2010 for home, SME and Pro use. It will run on Windows XP (SP3 only), Vista, Windows 7 and equivalent server systems (see full technical requirements). As of 12th May, Office 2010 has been a “release candidate” trial version of the Pro version. Beta versions of the home and SME versions are also available.

Full release of all three versions is expected towards the end of June 2010, though no precise date has been published, pending feedback from the trial and beta users. A comparison of the contents of the three versions is here, where you can also place advance orders. Microsoft obviously benefit from this, but could anyone explain why it would be worth doing? Cheaper?

Upgrade from earlier versions of Excel

Those who buy Office 2007 after 5 March 2010 will get a free upgrade to Office 2010 – but there are stringent conditions that must be checked first. Earlier purchases will not be entitled to any upgrade discount. Once Office 2010 is fully available, worth looking whether 2007 can be bought more cheaply than 2010 (from an official source), and then upgrade for free. But check carefully how this would be done before purchase, especially if you have Office 2003 or 2007 already installed!