Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

PowerPivot vs SSAS Quiz

with 9 comments

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:

http://cid-7b84b0f2c239489a.office.live.com/view.aspx/Public/PowerPivot%20SSAS%20Comparison/SSASvsPowerPivotQuiz.xlsx

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…

Written by Chris Webb

November 19, 2010 at 2:50 pm

9 Responses

Subscribe to comments with RSS.

  1. PowerPivot-hating BI Luddite…

    Jules

    November 20, 2010 at 10:18 am

  2. [...] I started off PASS week with Chris Webb’s session on “Comparing PowerPivot with Analysis Services”.  And it was fantastic (did you have any doubts?)!  I bugged him personally to make sure he got his “Is he right for you?” quiz up on his blog and without further ado here it is, the definitive way to know if you should go for the complicated but steady guy (SSAS), or the speedy, slick new kid on the block (PowerPivot):  They’re both so dreamy! [...]

    Erika's BI Blog » PASS 2010

    November 22, 2010 at 8:57 pm

  3. Is there any possiblity of posting the stack from the PASS presentation? I was not able to be there.

    Joe

    April 1, 2011 at 4:19 am

  4. [...] solution and when is SQL Server Analysis is the right solution. Thanks to Chris Webb who did a PowerPivot vs SSAS Quiz in November last year. Check the quiz out and see if your answers the questions help you lead in [...]

  5. The link is not working. Can you make the quiz available?

    iroche

    January 10, 2013 at 6:56 pm

  6. 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?

    David Laplante

    April 3, 2013 at 3:07 pm

    • 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

      Chris Webb

      April 5, 2013 at 9:53 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,144 other followers

%d bloggers like this: