The WorkerThread Blog

we know stuff so you don't have to

Archive for November, 2010

Power to the Pivot

Posted by workerthread on November 24, 2010



I’ve been spending a good bit of time with PowerPivot in recent months, primarily as part of a proof of concept exercise for a client.  Like many others I have to say I started looking at it with a high degree of scepticism.  Surely I thought, this thing goes against all of the principles we’ve been trying to drum into users, particularly the importance of building a well designed Unified Dimensional Model with calculated members in our Analysis Service cube – then we get that “one version of the truth” that we’re all after when we build a Business Intelligence solution.  PowerPivot on the other hand is a free add-in for Microsoft Excel 2010 which lets you load very large amounts of data into memory work and with it in a new desktop version of Analysis Services called VertiPaq. So you build your models, including all the calculations, inside of Excel.

The thing is, the big majority of analysts who need to pull together and work with large sets of data often need to do so in response to rapidly changing requirements (and frequently, changing data sources).  And of course they spend most of their day using Microsoft Excel.  Show PowerPivot to someone who spends their time creating complicated Excel VLookups to build reports and pivot tables against data loaded from database extracts and you will quickly see their eyes light up.  And in all of the time I’ve spent working with Microsoft Analysis Services (since the first release in 1998) I’ve never seen as much excitement online about a BI tool as I’ve seen recently for PowerPivot.  Do a search on twitter for the #Powerpivot hashtag to see what I mean.

PowerPivot in Excel 2010

So if you have Excel 2010 you can download the PowerPivot add-in from Microsoft’s PowerPivot site here.  Note that there are two different versions of the download, one each for the 32-bit and 64-bit versions of Office 2010.  Right now most users are likely to be running the 32-bit version I suspect.  There are very good reasons for this – if you install 64-bit Office 2010 you may have problems with existing add-ins which are 32-bit only, and there is also an issue with SharePoint datasheet views with 64-bit Office.  On the other hand, if you are planning to use PowerPivot with very large data sets you will almost certainly need to install the 64-bit version of Office 2010 – as it says in this TechNet Installation Note“the 64-bit version of PowerPivot enables you to work with up to 4GB of data in memory, and the 32-bit version enables you to work with up to 2GB of data in memory”.  There is some guidance from Microsoft on 64-bit Office 2010 usage here.

Once you are up and running, you’re going to be impressed with the performance you get, even on large datasets.  Data can be loaded into PowerPivot from a good variety of sources – SQL Server, Analysis Service cubes, other OLEDB/ODBC data sources, text files, Excel data, also interestingly SQL Server Reporting Services, Atom data feeds and the Azure DataMarket.  Again, this is where lightbulbs start coming on over Excel Users’ heads because PowerPivot lets you create joins between disparate data sources.  So providing there is a matching column, it’s quite easy to create a relationship between line of business data, maybe residing on SQL Server, and other data you might have acquired from an online service such as Azure DataMarket.

To get the best out of PowerPivot, you need to learn how to use Data Analysis Expressions (or DAX for short).  One of the reasons Excel users are likely to warm to PowerPivot more than they have to Analysis Services cubes is that DAX calculations are closer to the type of functions they may already be using in Excel.  That’s not to say DAX doesn’t also require some effort to learn, it definitely does if you want to master it.  DAX has some great date calculation features built-in by the way, so very good for year-to-date, or same-period-last-year type reports.

So what are the downsides when you’re using PowerPivot with Excel?  For me the same issues have cropped up whenever I’ve been shown it to clients.  First, there is no easy way to automate the refreshing of your data from within Excel.  Right now the only option you have is to open the PowerPivot window and manually refresh one or more of your connections.  Because PowerPivot is a separate add-in you can’t automate it via VBA right now either.  If you publish your model to PowerPivot on SharePoint 2010 then you can get the server to schedule refreshes for you but if you don’t have this luxury then right now manual refresh is the only option.  There is an interesting post from Ken Puls on some of the bits he would like in the next release.

Something else that concerns me slightly is the need for governance when defining and using complex DAX calculations in Excel models.  As I’ve already said, a key benefit of the dimensional model, with all of your custom calculations defined in the cube, is that there is a single definition.  So if your company has a complex ratio calculation, or way of defining utilisation percentages or whatever then the possibility that two different analysts use different calculations (and arrive at different results) is significantly reduced.  DAX formulae can get pretty complex so ideally you would want to store “templated” definitions somewhere.   Over on PowerPivot Pro there is an interesting post on how you might use NotePad++ to create and troubleshoot the more complicated formulae.  Perhaps something similar could be used as a reference guide for sharing complex DAX calculations.

PowerPivot in SharePoint 2010

So as I’ve mentioned above, as well as being an add-in for Excel 2010, PowerPivot is also an option for SharePoint 2010 in conjunction with SQL Server 2008 R2.  Once you have PowerPivot running on a server in your SharePoint 2010 farm you can publish your models to “galleries” and your users can view your models in their browser, without even the need for Excel.  Again, a lot of work has gone into the SharePoint bits here, making good use of SilverLight to browse through published documents.  SharePoint administrators can also monitor usage of each model, identifying resource utilisation, which items have been opened most frequently and so on.  This would then be used to see which PowerPivot models might be suitable candidates for re-development as a true dimensional model in an Analysis Services cube.

PowerPivot on SharePoint 2010 can be an expensive proposition though.  You will need SQL Server 2008 R2 Enterprise Edition on the PowerPivot Server.  Your SharePoint 2010 farm will also need Enterprise Client Access licences.  And the server running PowerPivot will need to be pretty substantial (lots of memory, multi-core etc).  Here’s an MSDN document on recommended configurations.  I know we keep hearing the phrase “BI for the Masses” but these masses need to have pretty deep pockets.  Also note that the PowerPivot Analysis Services “VertiPaq” engine will be a separate instance to any existing traditional Analysis Services instance you might be running, almost certainly requiring its own server.

Getting Started

Here is some recommended online reading to get you started:

Loads of useful information on the PowerPivotPro and PowerPivot FAQ sites

Marco Russo’s blog (one of the authors of the book recommendation below)

The DAX articles on Chris Webb’s BI blog

Book Recommendations

If you want an in-depth understanding of how to use PowerPivot and DAX in Excel 2010 I would wholeheartedly recommend PowerPivot for Excel 2010 by Marco Russo and Alberto Ferrari.  Excellent explanations of how it all works, best practices and lots of examples.  You can download sample chapters here.

If you are interested in the SharePoint 2010 side of things, take a look at Professional PowerPivot for Excel and SharePoint which has useful information the PowerPivot architecture and how it’s all put together in a SharePoint farm.

Summing Up

This is already a long post and I haven’t even mentioned the next release of SQL Server (currently code named Denali but my spell checker keeps suggesting “denial”).  From the information recently released understanding how to use PowerPivot is going to stand you in good stead when the new release is available.  Here’s a good overview of the “Denali” roadmap.  I’ll try to follow up this post with news about Denali BI features when more information is available. 

For my own part I’m very excited about the possibilities that PowerPivot opens up.  Given that we are only in version 1 most of what I’ve seen is very impressive and I would definitely recommend taking it for a test drive.  And your Excel “power users” will definitely like what they see.

Let me know what you think – can you see a use for PowerPivot in your organisation?  Is “self service BI” a step in the right direction?

Technorati Tags: ,,,

Posted in Business Intelligence, Microsoft SharePoint, Office, PowerPivot, SQL Server | Comments Off on Power to the Pivot