PowerPivot vs. Power View: What’s the Difference?

 
I’ve been getting this question a lot lately:  How does Power View relate to PowerPivot?  Is PV a replacement for PP?   (And why does PV have a space in it while PP does not?)

(For more information on the various “Power *” offerings from Microsoft, see also the newer posts What is Power BI? and The Three Big Lies of Data).

First:  Understand that PowerPivot is Kinda Two Things

Let’s rewind all the way back to Office 2010, a world in which PV does not exist.  (For most of you, we call this time Today.  And for those stuck on 2007 or 2003, you may refer to this as Tomorrow.  Or maybe even the Day After Tomorrow).

In that world, which is where this blog largely lives, it’s helpful to reflect that PowerPivot has two parts:  the PowerPivot window, and the Excel window.  They have the following relationship:

PowerPivot's Relationship to Excel

 

Yes, in 2010 we like to write our measures in Excel, using the field list.  So we’re writing part of the model while looking at the visualization layer (Excel), rather than the PowerPivot window.  The lines blur a bit there, yes, but essentially we still have the Model (data, tables, relationships, calc columns, measures, etc.) and we have the reports/visualizations (pivots, charts, etc.) in Excel.

Power View is Purely an Alternative to the Excel Visualization Layer

PowerPivot is the Source of Data and Underlying Formulas/Relationships (aka the Model) that Can Be Exposed via Excel or Power View

Example:  The User Group Maps

You know those maps of PowerPivot/Excel user group interest I’ve been posting the past couple of days?  Well those are Power View visualizations in Excel 2013:

A Power View Visualization in Excel 2013.  Note that Power View Gets its Own New Sheet Type.

A Power View Visualization in Excel 2013.
Note that Power View Gets its Own New Sheet Type.

Now…  could I have used a PivotTable for this?  Maybe. 

But here’s the trick:  locations don’t always match in the data even though they match in the real world.

Example:

image

Two Problems:  “GA” is the Same as “Georgia,” and Alpharetta is Part of Metropolitan Atlanta
They Should All Be One User Group – I Need All of Those to Be Considered the Same Place!

So in some sense you can think of this Map visualization in PV as a smart “grouping” feature, since it uses the Bing Maps engine to plop all of those dots in the same place:

Power View Displays Geographically Close Locations as a Cluster

Power View Geographically Charts All of Those Rows as a Cluster On the Map, Just Like I Need!

Compare that to a pivot, which treats them as 3 separate locations:

Geographic Locations in a Pivot - If They are "Close," the Pivot Doesn't Know

A Pivot Will Treat Those Locations Just as Distinctly as it Does Athens Greece Smile

In Both Cases Though…

Both the pivot and the PV map connect to the same PowerPivot model and use the same measures (like Response Count).  It’s just that in 2013 we get to choose different visualizations for different purposes.

In other words, ideally we would all view Power View as just a new type of Excel Chart, whereas today it admittedly feels like an alien transplanted into the Excel box.  (I’m providing a lot of feedback to MS along those lines and so far it has been warmly received, which always feels nice).

We also now have GeoFlow, which honestly I’ve yet to experiment with, but will soon.  In true MS fashion we now have two competing map-charts hosted in the same product!  Woo hoo!  Let’s hope they unify those two, and THEN unify both of those with the rest of Excel Smile

Power View Offers More than Maps

There’s also a “Card View” (pictured below) and everyone’s favorite, the animated bubble chart.

More on these later.  It is not my intent to taunt people with things they can’t use, and honestly I don’t enjoy working with 2013 that much due to a number of cosmetic issues.  So I’m keeping the blog firmly rooted in 2010 for the forseeable future.

Power View's Card View

Power View’s Card View

14 Responses to PowerPivot vs. Power View: What’s the Difference?

  1. Henson says:

    Almost spit out my tea when I saw the title of that Card View graph. Nice work!

  2. Eric Hutton says:

    “I don’t enjoy working with 2013 that much due to a number of cosmetic issues”

    I hear you on that! I had 2013 installed on my office computer for a short while, and, my God it is ugly! There is no contrast between anything, even on the “dark grey” theme setting.

    We Had to revert back to 2010 for technical reasons*, and I wasn’t too upset.

    * PowerPivot in 2013 does not work on our machines for some reason, giving a similar error that the early releases of the SQL 2012 based versions of PowerPivot gave – which had something to do with local policy rights.

  3. Franky Leeuwerck says:

    Nice simple overview of these three components and their relations. Useful for introducing new users.

  4. Dave says:

    Excel 2013 lost some Powerpivot functionality:

    1) The SWITCH() and IF() functions now treat numbers and dates as different data types and won’t allow both in the same formula

    2) Lost the ability to query the Powerpivot metadata (e.g., SELECT DISTINCT [Table], [Object_Type], [Object], [Expression] FROM $system.discover_calc_dependency where Object_Type = ‘MEASURE’ or Object_Type = ‘CALC_COLUMN’)

    http://www.powerpivotpro.com/2012/10/other-better-ways-to-get-all-measures-as-text/

  5. Ifeanyi says:

    I love Excel 2013 and ‘Model.Refresh’ is the ultimate reason

  6. Helen says:

    fresh perspective on powerview being just another excel chart. Was muddled about when to say powerview and powerpivot. – using them interchangeably until now. thanks! I would be interested if you have anything comparing SSRS with PowerView. One of my favorite features of Powerview is that you can elegantly offer users detailed view of any part of a dataset represented a chart- side by side. In excel you do this by double clicking into a field in the Pivot table resulting in another tab opening up listing details of records behind the data. In PowerView both chart and details can be viewed at same time. Is PowerView superior to SSRS in this functionality? Would love to know.

  7. olbu says:

    PowerView can’t export Chart’s data to Excel. I want to copy the data to a new worksheet, but I can’t.

  8. Aparna says:

    So i never thought of this question until i stumbled at this post…Why does PV have a space in it while PP does not? so do we have any answer for that. Sounds silly though :)

  9. Akira Kondo says:

    Someone in my office is looking to do what’s expressed in a world map with bubbles as in power view in Excel 2010. Is this possible at all only with Excel 2010 with Power Pivot added. Is it right that Power View is only available with Excel 2013. A quick answer will be appreciated. Thanks.

    Very Distressed

  10. cyrilbrd says:

    Well I don’t know… Bought your books “Dax formulas”, “Alchemy” as well as Kasper’s Dashboarding, Worked on 2010 and now working on 2013; maybe because I lack your experience, I have no problem using 2013. I find Power View quite cool although sharing with non Microsoft user isn’t really clear to me as of this moment. This said all end users are quite thrilled by the map system (as we rely on data emanating from several production sites geographical scattered in Asia). Great blog by the way, discovered PP here.

  11. Craig says:

    Is mapping features part of Power Pivot in 2010, or do I need to upgrade to Power View and Excel 2013 to get the mapping features? Can you tell me more about the differences between Excel 2010 and Excel 2013 BI features and capabilities? I’m new to Power Pivot as our company just upgraded from 2003 and 2007 Excel…

  12. Jon says:

    Is there a Power View Add-in for Excel 2010 to complement the Power Pivot Add-in?

Leave a Comment or Question