A BI Consultant’s Musings on PowerPivot

February 2, 2010   //   Business Intelligence, , , , , , , ,

Today I reached a new milestone in my analysis of Excel 2010 PowerPivot (Beta). I’m ready to share my experiences with the product and get our customers excited about Microsoft BI! Before I go into the details on PowerPivot, I think it’s useful to look back on the past year. It was nearly a year ago that Microsoft made dramatic changes in the technology roadmap for BI. Those changes included rolling PerformancePoint M&A into the SharePoint SKU and killing PPS Planning. Over the last 12 or so months I’ve experienced a lot of different emotions from our customers out in the field. Some were disappointed and borderline angry, while others were glad to see that a new strategy was on the horizon. That horizon is nearly here and looks to be coming in the June timeframe. Don’t hold me to this date though, I’m just assuming the late May rumors for RTM are true. So here is a short list of major products getting close to launch that will change BI in positive ways:

  • SQL Server 2008 R2 – Includes BI enhancements to support PowerPivot, better SharePoint SSRS integration, and an improved version of SSRS report builder as well as a host of other new features and upgrades that build on the success of SQL Server 2008
  • SharePoint 2010 – As far as BI features, the notable ones are PowerPivot service, BI Gallery for PowerPivots, Better integration with SSRS, and a new BI search feature for finding analytic content published to SharePoint.
  • Office 2010 – Office 2010 and specifically Excel 2010 have been significantly improved as far as BI features go. I’ve invested a lot of time into Excel 2010 research and the big wins I see so far for BI include the following: â—¦PowerPivot – This add-on integrates with Excel 2010 (Formerly Project Gemini) and provides the ability to create local in-memory analytic solutions that are very similar to Analysis Services cubes. This morning I experimented with a data set from http://www.data.gov It was some Medicare data in a CSV file format with about 10 million records. Normally, I’d have to load that data into a table on SQL server to analyze it. With PowerPivot, the 10 million rows imported just fine and only took a few minutes to load.
  • Pivot Table Slicers – I can’t say enough good things about the slicers. They give the user a visually appealing way to filter a pivot and they aren’t bound to a single pivot or chart. In fact I created some samples where a single slicer was controlling the outcomes of three pivot tables and three charts.
  • Custom Calculated Measures – This feature is using DAX (Data Analysis Expressions). Basically, it’s an extension to the Excel formulas. I find these expressions extremely simple to create and there is a handy interface for checking syntax and offering a template for all of the parameters the expression needs. This is many times simpler than writing MDX (Multi Dimensional Expressions).
  • Sharing Content to SharePoint 2010 – Seems like a minor feature but it’s importance supersedes the rest. SharePoint provides the security, workflow, navigation, and data governance for all the PowerPivots people will want to publish. Without SharePoint, I think I’d be writing some criticisms on how PowerPivot is just another way to create unmanageable data islands all over the enterprise. SharePoint brings order to the BI universe and is the perfect mechanism for delivering the content. It also addresses the concerns of how to share the rich content with users who don’t use PowerPivot but want read access to the analysis. And finally, SharePoint provides the mechanism for data updates/refreshes. The PowerPivots are great but if they are stale they’re completely worthless the day after creation. Having a well thought out tool for data refreshes that is controlled in one place is extremely useful and important to insuring the success of the analysis asset.

Here’s a link to my next webinar on February 9th 2010. If you haven’t guessed it already, I’ll be talking about and demonstrating: PowerPivot, Microsoft BI, and SWC’s “Extreme BI” approach.