Essential Guide

Guide to SQL business intelligence, analytics and data visualization

A comprehensive collection of articles, videos and more, hand-picked by our editors

Exploring Microsoft PowerPivot and collaborative business intelligence

Learn the background of Microsoft Power View and how PowerPivot works with Microsoft Excel 2010 in part one of this book excerpt.

This is the first part of a two-part excerpt from Chapter 1, "Getting Started," from the book Visualizing Data with Microsoft Power View by Brian Larson, Mark Davis, Dan English and Paul Purington (McGraw-Hill Professional; 2012) with permission from McGraw-Hill. Read the second part of this book excerpt here. Download a PDF of the full chapter. Get insights from the authors in this Q&A.

The Evolution of Microsoft Business Intelligence

Before we get into the details of Microsoft Power View, let's take a look at how Microsoft business intelligence has evolved over the years. This background should give you a better understanding of how Power View works. It can also provide insight into how Power View is intended to be used.

In the "old days" of data warehousing and business intelligence, data marts and data warehouses were created using a top-down approach. Business users had to work through the IT department by following a traditional application design methodology. The process started with design meetings that took place over a period of weeks, months, or even years depending on the size of the project.

These design meetings would be attended by IT employees and business users in order to accomplish the following:

  • Determine and document the reporting and analysis requirements of the users
  • Identify the sources of data that would be used for making business decisions
  • Design processes to import data from these data sources
  • Plan out the database structure for the data warehouse
  • Design the reports and/or applications that would be used to analyze the data
  • Estimate the effort and cost to build the solution

Once the design was complete and management had approved the budget and timeline for the project, the IT department would build the data warehouse according to the design. Unfortunately, most data warehouse projects did not (and many still do not) complete within the budgeted time or cost. In addition, the users -- for whom the project was originally started -- did not have the ability to actually analyze any data until months, or oftentimes years, after the project was started.

For more on Microsoft PowerPivot and BI

Learn how to map data from PowerPivot to Excel or PowerPoint

Uphold data quality in PowerPivot with sound policy

Read about using Excel in the cloud with PowerPivot

There are several consequences to approaching BI projects in this manner. First, the information needs of the users are likely to have changed from the start of the project to the time the first analysis is delivered. The data warehouse is already obsolete by the time it becomes available for use! Second, users often find that the reports and applications do not provide all of the necessary information to make business decisions. Reports designed on paper look very different once they are loaded with actual data. Many times these reports lead to further business questions and users discover that the reports were not designed to answer these new questions. Imagine management's perspective, spending thousands or millions of dollars on a project only to find out that core business decisions cannot be made using the tools created! I wouldn't want to be the person delivering that news!

Microsoft's approach to the BI methodology has evolved to be more user-centric. The users are the ones who know their data the best and know what they want to learn from that data. Instead of having the users sit down with the IT department to build a solution on paper, why not give the users access to the data along with easy-to-use analysis tools? That is the approach Microsoft has chosen to pursue, as evidenced by Power View and the Tabular BI Semantic Model (BISM).


Many business users are very comfortable working with data in Excel. The main problem users experience with Excel is the limited number of rows allowed in a workbook and the poor performance that results when working with large amounts of data. If users could effectively use the data analysis tools in Excel with a workbook containing millions of rows of data, they could fulfill many of their data analysis needs. This was the inspiration for PowerPivot.

PowerPivot is a data analysis add-on for Microsoft Excel 2010 that allows large amounts of data to be collected, aggregated, and analyzed in one workbook. PowerPivot workbooks use a powerful data engine to quickly query a large volume of data. Using PowerPivot, many business intelligence questions can be answered efficiently and responsively directly within Excel.

The data to be analyzed may come from a variety of sources. The user imports this data into the PowerPivot workbook. Once the data is loaded, the user can utilize tools such as PivotTables and PivotCharts to calculate totals and find trends in the data. Figure 1-1 shows a sample PowerPivot analysis. Very powerful!

PowerPivot allows business intelligence to be more of an evolutionary process than a discreet project -- providing a return on investment (ROI) almost immediately. Users can make business decisions based on results in PowerPivot in very short order. The need for a huge business intelligence project that does not provide deliverables for months is gone!

A sample PowerPivot analysis

In addition, Microsoft provides a way for analysis work done by an individual user in PowerPivot to be shared at various levels throughout an enterprise. PowerPivot has two editions -- PowerPivot for Excel, which works within Excel on a user's workstation, and PowerPivot for SharePoint. Using PowerPivot for SharePoint, analysis can be shared by a number of users in a work group or department.

For example, say Bill in accounting has analyzed the general ledger data and created some useful charts illustrating trends in gross profit. Others in his department have seen his PowerPivot workbook and would like to use those charts to create their own charts using the same data. To meet their needs, Bill publishes the PowerPivot workbook to a document library within the accounting department's Microsoft SharePoint site. The entire department can then use and enhance the workbook. The workbook has gone from being an individual tool to an important departmental resource. It has been escalated to the next level of use without the intervention of the IT department.

This was first published in October 2013



Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

Guide to SQL business intelligence, analytics and data visualization

1 comment


Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: