A common question that those using the Microsoft stack ask is “when should I create my report in Power View and when should I create it in Power Pivot”?
I can’t give you a definitive answer to that question as that would depend on your specific situation; however I can list some of the things you should consider before deciding on which tool to use.
Before jumping into the list; it’s worth pointing out that Power Pivot is primarily a tool we use for connecting to and modelling data (by creating a Power Pivot data model). We can add Pivot Charts and Pivot Tables to our Excel based reports and these may or may not be linked to a Power Pivot data model. So when we talk about Power Pivot here we’re actually referring to Pivot Charts and Pivot tables.
- The size of a Power View report is limited to the Power View workspace, which generally accommodates 4 individual charts / tables / maps etc.
- A Power Pivot report can be as tall and as wide as we want and is only limited by the Row and Column limits in Excel (approx 1 million rows and 65,000 columns).
- Each Power View sheet can be connected to one data connection only.
- A single Excel worksheet can contain many Pivot Charts and Pivot Tables, linked to one or multiple data connections.
- Power Pivot is more limited in terms of data visualisations; for example in Power View we have maps, animated bubble charts and cards available to us. In Power Pivot we’re limited to charts and tables (although there’s still plenty to choose from including many charts that aren’t available in Power View).
- Drilling is easier in Power View than it is in Power Pivot. In Power View we simply double click on a visualisation to drill down, whereas in Power Pivot we have to use the Explore feature (which is far from difficult but not quite as intuitive).
- Filtering in Power View can be achieved using the Filter Pane, applying a Slicer to the report or by single clicking one visualisation – which then updates and filters the other visualisations (object filtering). Filtering in Power Pivot is generally achieved using Slicers (which look much nice than those in Power View); however we don’t have the before mentioned object filtering in Power Pivot.
- In Power View we can use the Pop Out and Pop In feature to quickly and easily expand and focus in on one visualisation.
- There are no Pivot Tables in Power View; Pivot Tables are very useful for showing a large amount of detailed data and information at both a summary and detailed level.
- The report customisation options in Power View have been kept to a minimum to make Power View as quick and simple to use as possible. For example we can’t customise axis, axis labels, colours and chart titles in Power View, instead these are done automatically for us. In Power Pivot we have almost complete control over the content and formatting applied to our visualisations.
I’m sure there will be other points to consider as well, but these are the key points I’ve noted to date. If I uncover anymore I’ll be sure to add them to the list.