This is a question I’ve been asked a few times before; “apart from handling larger data-sets, what do you get with cubes that you don’t get with pivot tables?”

This isn’t an exhaustive list but it covers what I think are the most important functionality differences.

#1 – Cubes are organised into dimensions / attributes.

With traditional Excel pivot tables, you are building on top of a flat data-set and as a result you will get your dimensions / attributes / measures in one big list.

You can take a flat data-set straight into PowerPivot as well (and there are definitely use cases for doing this) but if you build a dimensional model (as you are forced to do in SSAS MD) you will logically organise your reporting attributes by the correct dimension and make a much more intuitive model for the user.

#2 – Hierarchies.

Traditional pivot tables will show you the implicit relationships in your data but you will have to create a column for each attribute you wish to show.  You can then tidy this up by adding manual grouping but the whole process is very clunky.

With PowerPivot & Cubes, you explicitly define hierarchies and when exploring the data you can drag this single field in and have instant drill-up and drill-down, plus on large dimensions performance is going to be significantly better.  Displaying natural relationships this way is key in making the data easy to understand at a glance.

#3 – MDX / DAX.

In cubes and PowerPivot you can write your own MDX & DAX functions to build more complex calculations.  There really isn’t any direct comparison for MDX/DAX for traditional pivot tables although when creating a measure in a traditional pivot table there are some options available (as shown below) but nothing with the advanced analytics capability of MDX/ DAX.

Users also sometimes build calculations outside of the traditional excel pivot but this then means the pivot table has to remain static otherwise the calculations are overwritten.

#4 – Client Tools.

Traditional Pivot Tables are themselves the end-product.  PowerPivot provides an upgrade path to SSAS tabular and cubes have a whole host of cutting edge reporting tools that can use them as a source.  Personally I don’t like excel pivot tables for browsing cubes and have tried out some different reporting tools that can connect to SSAS.  My favourite experience was delivered by Pyramid Analytic’s BI Office solution, an example of which can be shown below.

Power View inside Excel is not bad but in my opinion it’s not the easiest thing to use and I can’t see it gaining wide adoption.

#5 – Other Considerations

There are plenty of other important considerations such as

security,
pre-aggregation,
partitioning,
formatting of dimensions/measures,
deployment possibilities

…and of course scalability but hopefully the above should demonstrate to people new to BI that SSAS offers key functionality benefits compared with pivot tables.