Maximizing the benefits of Excel add-ins: PowerPivot and Power View
A comprehensive collection of articles, videos and more, hand-picked by our editors
In an email discussion with Brian Larson, Mark Davis, Dan English and Paul Purington -- the authors of Visualizing Data with Microsoft Power View --SearchSQLServer Managing Editor Melanie Luna asks the writers to explain more about the benefits of Power View. Click here to read an excerpt from Chapter 1, "Getting Started," (McGraw-Hill Professional; 2012) with permission from McGraw-Hill. Download a PDF of the full chapter here.
What industries might benefit most from Microsoft Power View? Can you give some examples of how it's working in specific instances?
Paul: All industries can benefit. Every industry has decision makers that need to be able to analyze business and market data to make educated and strategic decisions.
Mark: Power View is especially suited to environments with decision makers who want the ability to explore their data before they're able to articulate their requirements. It seems that every locale has users who want ready access to information with speed-of-thought analysis tools.
Brian: I am currently working with an organization using Power View to empower business users to create their own reports and do their own data exploration. This has allowed the organization to relieve a six-month reporting backlog and free IT resources to handle other pressing issues. In another case, I worked with an organization using Power View to quickly provide an analytical interface on top of complex franchisee marketing information.
Dan: Currently I have customers in billing, healthcare, insurance, third-party logistics and more that are using PowerPivot and Power View. All of them are definitely seeing the benefits and providing a faster turnaround on their reporting solutions to get a quick ROI.
You explain that Microsoft has opted for a business intelligence approach that gives users access to data and user-friendly analysis tools. With this user-centric approach, how does the role of the IT department change?
Mark: This user empowerment has several implications with respect to the role an IT department plays in information delivery. One of these relates to the data itself. Will an end user really wish to navigate through unfiltered rows and columns of data designed for use by complex software systems? No matter how friendly the analysis tools are, data must be modeled in a manner that reflects the user's understanding of their business.
Brian: In some cases, power users may develop their own models. However, in many cases, the level of expertise and the familiarity with the data necessary to create data models will continue to reside in the IT department.
Mark: One of the roles of IT is to analyze their business models, manage data lineage and construct data models accordingly.
Brian: In situations where IT does not want to be solely responsible for model creation, IT may be involved in providing training and support for business analysts and power users as they learn the data modeling process.
Paul: The IT department also still has a role to provide the infrastructure for the users to access the data models and use the data analysis tools. They will also ensure that the data is secured properly.
How is the user-centric approach advantageous from the perspective of IT?
Paul: One big advantage of this user-centric approach for IT is that they do not need to be actively involved in the development of reports and metrics. Users can create these entities and can validate the data themselves.
Dan: IT resources are rather limited and the world of IT is changing so rapidly that this shift and empowerment of the business is vital for companies to continue to grow, stay competitive and focus on their customers' needs.
Paul: User-centric BI solutions (i.e., PowerPivot workbooks) often get escalated to enterprise solutions as other departments find value in the solutions developed by one individual or one team.
Mark: This is a game changer. IT now has the added role of being a gatekeeper or distributor of business intelligence outputs. End-user report designers can submit their work for sharing, but within a defined scope, IT controls the publication of their work for consumption by the enterprise.
Dan: Once users start to share their data models and reports within SharePoint, Microsoft has provided tools to help track report usage over time. This was not available previously, so this is a big plus for IT as they begin down this path of self-service business intelligence within their organizations.
Brian: What it comes down to is allowing each group to concentrate on what they are good at -- IT manages data and infrastructure, business users explore the data to gain insight.
You mention that there are two editions of PowerPivot -- one for Excel and one for SharePoint. Are these meant to be completely separate editions, or do they work in concert with each other? Can users choose to use whichever best suits their purposes?
Dan: The PowerPivot that comes with Excel can be used on an individual level. Once you want to share the report and information, you will want to upload that Excel workbook into SharePoint. (This requires the Enterprise Edition of SharePoint.) Then at the SharePoint level, you can add a certain level of security and enable data refreshes. It is even possible to allow users without Excel or PowerPivot to utilize the reports. You also have the resources of the server, so the solution can scale better and IT can then monitor and track the usage.
In order to get started with PowerPivot, you have to start with the Excel version and then you can upload the workbook and data model to SharePoint. Once the PowerPivot file is in SharePoint you can then create additional reports from that, including Microsoft Power View.
Brian: Power View is also available on several platforms. It was originally made available through SharePoint but has now been made available outside SharePoint.
Paul: My perspective is that Power View for Excel supports data analysis for an individual. Power View for SharePoint supports data analysis for departments or an enterprise. It does not mean that Power View for Excel cannot be used by teams but I think each Power View platform has a specific niche audience that it serves.