PowerPivot vs SSAS Quiz

Last week, at the PASS Summit, I did a session on ‘Comparing PowerPivot with Analysis Services’. The aim of the session was to compare the two products in terms of positioning and functionality, and help people work out which tool would be appropriate for their product  – and the reason I submitted this session was because I’ve seen an awful lot of people over the last year who are confused about this issue, and it’s not an easy question to answer. Although there are many things that both tools do equally well, there are some things that PowerPivot is good at and that SSAS is not, and there are other things that SSAS is good at and which PowerPivot is not.

Anyway, to make the presentation a bit more fun I came up with the idea of creating a quiz (like the kind you find in women’s magazines) to help make the decision. It took the form of an Excel spreadsheet with a series of yes/no questions, and once the questions had been answered the spreadsheet would tell you which tool you should use. Since a number of people have since asked me to share the workbook, I’ve decided to make it available via the Excel Web App here:


All you need to do is answer each question by entering 1 under either the Yes or the No column, and then when you’re finished look in cell C67 for the answer. You might also want to download a local copy to Excel and play with it there, rather than edit the document online. The way it works is that each question has a weight attached to the yes or no answer, and that’s found in the hidden columns F and G. A positive weight favours SSAS, a negative weight favours PowerPivot; I should also point out that the weights aren’t always equal. So, for example, in the question about security, if you answer that everyone in your organisation should be able to see all your data that favours neither SSAS or PowerPivot, but if you answer that you do need to restrict access to data then that favours SSAS (because only SSAS has features like dimension and cell security).

Before anyone complains to me about the questions being stacked in favour of SSAS or PowerPivot (and I’d like to point out one more time that I am not some kind of PowerPivot-hating BI Luddite, I do like PowerPivot and I’m also excited about using BISM too, so there), I’m going to add the following disclaimer: these questions should only act as a guide, and I cannot guarantee that this worksheet will give the correct answer in every case. It only represents a personal opinion! Before you use it, I suggest you review the weights associated with each question and change them according to your own ideas. Oh, and before you show this sheet to the boss you might want to delete the pictures of hearts at the top…

9 responses

  1. Pingback: Erika's BI Blog » PASS 2010

  2. Pingback: PowerPivot? But I use pivot tables in Excel « Simran Jindal's Blog

  3. I’m a BI consultant in Montréal, Canada and the BI Department for the consulting company I work for is struggling with this question.

    It seems that Microsoft has put 2 of it’s own products into competition (SSAS Multidimensional and SSAS Tabular or PowerPivot). We are trying to figure out where one is better than the other. It’s pretty obvious for PowerPivot to be positioned as a Personal/Self-Service BI or even team BI (with Sharepoint) but since SQL 2012 with it’s SSAS tabular mode, it’s not so clear anymore.

    Personally, I see Tabular Mode SSAS as the more official enterprise BI endpoint of a temporary and personal analysis done by some departmental analyst excel guru that actually found something interesting and worthy of deploying formally at the enterprise level. But even then, that analyst has to raise the flag that this should be incorporated into the enterprise BI which I worry would never (or almost) happen and thus enforce a spreadmart mentality and worsen the nightmare that a properly built data warehouse is trying to get rid of.

    Now add into the mix, the newly released Data Explorer for Excel and things suddenly take a turn for the worst in decentralizing everything and creating even more mashups and spreadsheets here and there in the enterprise. We are then left very far from the “one vision of the truth” that is the whole reason many businesses undertake a BI initiative in the first place.

    What’s you’re thinking on this? Is Microsoft “cannibalising” traditional BI with such a big push on personal and “datamart-less” BI?

    • I don’t think MS is cannibalising traditional BI, no, but it is going after the self-service BI market -possibly at the expense of, in terms of resources, of its traditional BI efforts – and it is trying to create a coherent story for both self-service and traditional BI. The Tabular model is part of this last effort to bridge the gap between self-service and traditional BI because it is so closely related to PowerPivot. Choosing between Tabular and Multidimensional remains difficult though because Tabular is clearly receiving all the love, but it’s still very immature compared to Multidimensional. Unfortunately you’ll have to make a decision about which one to use based on current capabilities and how you see the platform evolving over time. This question was covered in some detail in the book I co-wrote with Marco and Alberto last year on Tabular: http://www.amazon.co.uk/Microsoft-Server-2012-Analysis-Services/dp/0735658188/ref=sr_1_1?ie=UTF8&qid=1365195220&sr=8-1&keywords=bism+tabular

Leave a Reply to David Laplante Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: