This year I went to the Microsoft BI Conference in food heaven, New Orleans. In between stuffing my craw with crawfish, I went to a bunch of sessions and actually learned a thing or two! A very productive week, indeed.
The theme of the conference was definitely PowerPivot. It’s here, it’s free, get it, use it, love it, blah blah blah. For the past year it’s all been demos and betas of PowerPivot, but now that it’s been released it was very interesting to see it in action, and to see how the story has changed.
When PowerPivot was first introduced, there seemed to be the notion that this could be a replacement for Analysis Services. And/or that one could use PowerPivot to prototype Analysis Services cubes. Well, the first session I went to cleared THAT right up. PowerPivot does not replace Analysis Services. PowerPivot can’t replace Analysis Services. While PowerPivot uses the Analysis Services engine, and while it can be seen as an Analysis Services cube by such things as Management Studio after it has been deployed via Excel Services, the similarities end there. Dave Wicker did a great job illuminating the differences between the two products at the end of his session, “Building Custom Extensions to the PowerPivot Management Dashboard.”
The PowerPivot engine reads DAX natively. There is nowhere where DAX input is translated to MDX to be read by the engine, it’s all DAX all the time. And while DAX can do some cool things, it’s no MDX. If I ever get around to writing my post about how to do correlations with DAX that will be made clear. There is no correlation function in DAX, there’s no standard deviation function in DAX, there aren’t a lot of things in DAX that exist in MDX and this makes a lot of things….trickier…in DAX that are quite easy in MDX.
PowerPivot also creates its measures in a different place. In Analysis Services, the measures are all created in one place. And they can be grouped via measure groups. In PowerPivot you can have measures all over the place, associated with various tables.
PowerPivot also has limitations when it comes to establishing relations between tables. There can only be one relationship between tables. Many to Many relations are not supported (although there are some workarounds). There are no KPIs. And hierarchies don’t exist. The lack of hierarchies is a big big BIG thing (in my opinion) that limits the power of PowerPivot. Analysis Services, of course, has all these things.
And of course, PowerPivot is all about its in-memory columns and its storage engine. So long as something is in the storage engine, it’s great. You can sort billions of rows (plus seven)! But as soon as you ask it to something in a formula via calculation…well…not so fast. And a lot of things can only be done at runtime (read: dynamic correlations).
So, the new story seems to be that PowerPivot does not replace Analysis Services. You can’t prototype off it, because it is just so very different. But that doesn’t mean that it is worse! Only different.
I attended a really great session by Dan Bulos, “So Many Tools, So Little Time” that did a fantastic job of going over all the main BI tools, and discussing their strengths and weaknesses, and when is the “right” time to use the “right” tool. Unfortunately, the real answer is “it depends,” but thanks to Dan’s talk we were given some great guidelines to, well, guide our decision.
PowerPivot is brand new, and the inclination is to figure out what all is possible in PowerPivot, but as Dan so keenly pointed out, just because something is possible in a tool, doesn’t mean you should use that tool.
I really do think PowerPivot is going to come in extremely handy for users. I already know of some people for whom PowerPivot is perfect and will add a lot of value to their day to day work. It’s ability to combine various datasets is amazing for on-the-fly analysis, and for all those things that, as much as we wish they would, simply don’t belong in a cube.
It will be interesting to see what the future holds for PowerPivot. We got a glimpse of it via the BI Keynote. Some of the things currently missing in PowerPivot will be showing up in the next version, like KPIs, the ability to use PowerPivot in BIDS, and various other new extensions. At the end of the day, we have to remember this is a V1 product, but a really really cool one, and it can only get better.