A PowerPivot success story

February 4, 2011

Ive always been impressed with and realised the (forgive the pun) “power” of PowerPivot. However recently ive been charged with leading my organisation down a path of Microsoft BI across the enterprise – 1000+ employees.

So now ive really forced myself to join the PowerPivot revolution in a more hands-on fashion, seeing it as an ideal tool to give to our managers while we build the formal warehouse. ive used it to prototype chargables and utilisation tracking across our timesheets and im happy with the result so far. Now that ive had more experience with it and am much more comfortable with it i had agreat success story today at a client.


The client called me in to advise on some best practices around Microsoft BI and help them address some issues around their attempt at building a cube. I also ran them through Sharepoint 2010/Excel Services/PerformancePoint (whic hthey LOVED)! But thats besides the point.

The current situation saw them loading masses of data straight from production servers into classic Excel pivot tables (e.g. 1gb+) and refreshing that daily to then carry around – more sales force oriented. They also have a number of power users who are very comfortable with Excel but were hampered by its classic pivot limitations. They also manage to bring down their prod DB every now and then. Not good for an ERP (or any system really)!


Prototype Solution:

So I fixed up their cube to make it work (though hit was far from best practice – based on as single denormalised table!) and then i pulled those rows into PowerPivot – which they had never seen before.

Asked them to take a look at the row count – 1.7 million

Asked them to save the file and take a look at the size – 6.65Mb! “How does it do that?”  was the question. Enter explanation of Vertipaq 🙂

Once they understood the flexibility of multiple data sources, custom measures, plus the compression factor they went nuts! Add to that the web based delivery via Sharepoint – sold! They now have a lot to play with and think about and to add to this they are much closer to making a decision to implement Sharepoint 2010. All this in 3 hours!

I think the best thing Microsoft did was make the PowerPivot add-on free. Now thats value 🙂


OLAP PivotTable Extensions – extending analysis in Excel

August 24, 2009

Anyone developing cubes who uses Excel as an analysis/debug tool may find there are a couple of limitations. One of these is knowing what MDX is generated by Excel to query your OLAP cubes.

Yes, you could run a SQL Profiler trace on the SSAS instance and find out that way but its easier to install the OLAP PivotTable Extensions. This will let you view the MDX directly in Excel amongst other things.

Im surprised i only just came to know of this today since this utility has been on Codeplex for some time. Better late than never!