Home > Sample chapters

Using PowerPivot for SharePoint 2013

PowerPivot for SharePoint 2013 is the integration of the Microsoft SQL Server Analysis Services 2012 SP1 engine with SharePoint 2013. Learn how PowerPivot for SharePoint can help you securely share, manage, and refresh the workbooks, and IT can securely manage and learn about the workbook’s usage.

There are many tools that the business user, or the information worker, can reach to, but by far it is Microsoft Excel that is used most often. A great deal of today’s business decisions are based on an Excel workbook. However, there are some drawbacks inherent to this approach, and a few caveats must be issued:

  • It’s fairly common to share those workbooks by email or in a file share. This can potentially raise security issues.
  • It’s hard to assure that everybody working with a given workbook is using the same version, because access to that workbook is neither monitored nor controlled.
  • Refreshing the workbooks with new data can be a lot of work, and potentially, it’s a chore that needs to be done frequently.
  • Often, the data sources for those workbooks is used without the knowledge IT personnel.

Therefore, a natural yet inevitable mild tension exists between the users (who want to get things done quickly) and the IT department (which wants control). There is nothing wrong with the position of either side. It’s just how things are.

To address the needs from the business user’s perspective but at the same time not forget about the legitimate concerns of IT, Microsoft developed PowerPivot for Excel, PowerPivot for SharePoint, and Power View. Together, they are Microsoft’s core implementation of Self-Service Business Intelligence.

PowerPivot might not solve all the problems, but it is a paradigm-shifter that is bringing business intelligence (BI) to the business analyst. Here are just a few of the benefits that PowerPivot brings to the table:

  • A secure mechanism for sharing the reports You can publish the workbook to SharePoint, where it becomes an interactive web application through the Excel Services. Users have to download the workbook; they can open and interact with it in the browser. There is less risk to sensitive data, and when you publish a new version, everyone receives it the next time they visit the site.
  • Scheduled, automatic report refresh You can configure the workbooks to be automatically refreshed periodically without human intervention.
  • Transparency for IT After the workbook is published to a SharePoint location, everything happens within the realm of IT. IT is able to set the security of the workbooks through SharePoint, learn which data sources are being used by the PowerPivot workbooks, and learn what workbooks are actually being used and by whom. The list goes on and on.

In a few words, PowerPivot for SharePoint 2013 is the integration of the Microsoft SQL Server Analysis Services 2012 SP1 engine with SharePoint 2013. With PowerPivot for SharePoint, the user can securely share, manage, and refresh the workbooks, and IT can securely manage and learn about the workbook’s usage.

A brief history

The genesis of PowerPivot derived from two Microsoft internal papers. The first paper was about the concept of a BI sandbox, which would be a product that would make the creation of BI applications much easier and in a controlled environment that would include relational databases, multidimensional databases, and a reporting tool. As this first paper gradually shaped PowerPivot from the concept to the product, many of the original ideas changed (originally, Microsoft Access was the client application, not Excel), but many that remained are the soul of PowerPivot.

The second paper was about an in-memory BI engine. The business idea was to take advantage of the market trends in computer hardware (such as decreasing RAM prices, and the increased adoption of multicore processors) that would make an in-memory engine feasible. The in-memory engine described in the second paper would make some of the ideas in the first paper possible.

Both papers were accepted, and a small incubation team was created to explore the concepts further. This incubation team existed during the SQL Server 2008 R2 development wave, writing specifications, plans, code, and tests under the codename Gemini for what is now PowerPivot. PowerPivot for Excel 2010 and PowerPivot for SharePoint were released in May, 2010 as part of Microsoft SQL Server 2008 R2.

In the latest release, PowerPivot and Microsoft Office have been drawn into an even closer relationship. As discussed in Chapter 4, “Using PowerPivot in Excel 2013,” and Chapter 6, “Business intelligence with Excel Services 2013,” PowerPivot and Office are much more integrated. In PowerPivot for SharePoint 2013, there isn’t much new exposed functionality, but it was completely redesigned under the hood to make it more reliable and scalable.