PowerPivot for Excel and SharePoint

After completing this chapter, you will be able to

• Understand PowerPivot for Excel and PowerPivot for SharePoint.

• Understand how PowerPivot extends Excel functionality with Data Analysis Expressions.

• Create PowerPivot workbooks in Excel 2010.

• Publish PowerPivot workbooks to SharePoint 2010.

• Set up a periodic data refresh to a PowerPivot-enabled workbook in SharePoint 2010.

Introduction

As explained in Chapter 1, “Business Intelligence in SharePoint,” you can understand business intelligence (BI) as activities and practices that use fact-based support systems to improve the business decision-making process. Traditionally, those fact-based support systems have often been data-driven analytics and reporting tools developed by database administrators (DBAs) and developers, such as standard reports, scorecards, and applications.

Clearly, the DBAs and developers from an IT department cannot handle all that demand. An IT department has only so many resources and must focus on the most important projects. It’s likely that IT has been able to satisfy only a small fraction of the BI demand.

So, what happens with all that demand that does not make it onto IT’s plate? Usually, those projects get done without involving IT at all. The users cobble up solutions themselves—and they can do a good job if they use the right tools. In other words, they are already performing self-service BI. Self-service BI is a way for business users to easily access the data that they need to create the necessary reports and analysis themselves, without involving IT personnel at their company.

While many tools exist that business or information workers can reach for to help meet their BI needs, it’s Microsoft Excel that they use most often. A large number of today’s business decisions are based on information stored in Excel workbooks. Unfortunately, this popular approach has a few caveats:

• 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 ensure that everybody working with a given workbook is using the same version, because access to such files is rarely monitored or controlled.

• Refreshing existing workbooks with new data can be a lot of work—and potentially, it’s work that should be done often.

• Many times the data sources for those workbooks can be used without IT’s knowledge.

As a consequence, a natural—and inevitable—tension exists between users, who want to get things done quickly, and the IT department, which wants control. There is nothing wrong with either side’s approach; that’s just how things are.

To address the needs from the business users, while simultaneously keeping the needs of the IT department in mind, Microsoft developed PowerPivot for Excel and PowerPivot for SharePoint. Together, these applications represent Microsoft’s implementation of self-service Business Intelligence.

PowerPivot may not solve all the problems, but it is paradigm shift that gives powerful BI capabilities to business analysts that they can develop themselves. Here are a few such capabilities that PowerPivot brings to the table:

• Adding more powerful tools for the users in Excel Tasks that used to take hours now take only minutes (or even seconds), and some tasks that were simply impossible are now possible. For example, PowerPivot makes it possible to work with millions of rows in an Excel workbook.

• Scheduled, automatic report refresh You can configure the workbooks to refresh automatically and periodically without human intervention.

• Transparency for IT After a workbook has been published to a SharePoint location, everything happens within the realm of IT. That way, IT controls the security of the workbooks through SharePoint, can control which data sources are being used by the PowerPivot workbooks, and can learn which workbooks are actually being used and by whom, and so on.

PowerPivot for Excel is an add-in for Microsoft Excel 2010. It includes a modified version of the SQL Server Analysis Services engine. It plugs this powerful and fast data-crunching engine into Excel, greatly enhancing the capabilities of the most-used client available. Instead of dimensional modeling, users work with tables. Instead of Multidimensional Expressions (MDX) queries, users work with Data Analysis Expressions (DAX), a language that resembles Excel’s Formulas language.

Similarly, PowerPivot for SharePoint integrates the SQL Server Analysis Services 2008 R2 engine with SharePoint 2010. PowerPivot for SharePoint gives users the ability to securely share, manage, and refresh workbooks stored in SharePoint, while giving IT the control necessary for managing security and learning about workbook usage.