Harinath,
Et al.

With PowerPivot, Microsoft brings the strength of Microsoft’s Business
Intelligence (BI) toolset to Excel and PowerPoint users. Authored
by members of the Microsoft team behind the creation of PowerPivot,
this book shows you how to use PowerPivot for Excel to create
compelling BI solutions, perform data analysis, and achieve
unique business insight. You’ll learn how to use PowerPivot for
SharePoint to share your BI solutions and collaborate with others.
And your organization will learn how to use SQL Server 2008 R2
management tools to acheive more efficient results.

• Addresses sharing and collaborating on user-generated BI solutions
in a SharePoint Server 2010 environment
• Shows you how to troubleshoot, monitor, and secure PowerPivot services
• Demonstrates how PowerPivot can meet the needs of Office, as
well as how IT professionals can deploy and manage the self-service
business intelligence system

Ron Pihlgren is a 15-year Microsoft veteran and member of the SQL Server
Analysis Services team as a senior test manager.
Denny Guang-Yeu Lee is a member of the SQL Customer Advisory team and a
coauthor of Professional Microsoft SQL Server Analysis Services 2008 with MDX.
Wrox Professional guides are planned and written by working programmers
to meet the real-world needs of programmers, developers, and IT professionals.
Focused and relevant, they address the issues technology professionals face every
day. They provide examples, practical solutions, and expert education in new
technologies, all designed to help programmers do a better job.

Code Downloads

Take advantage of free code
samples from this book, as
well as code samples from
hundreds of other books, all
ready to use.

Read More
Find articles, ebooks, sample
chapters, and tables of contents
for hundreds of books, and
more reference resources on
programming topics that matter
to you.

®

Sivakumar Harinath is a Senior Test Manager on the SQL Server Analysis Services
team and a coauthor of Professional Microsoft SQL Server Analysis Services 2008
with MDX.

Join our Programmer to
Programmer forums to ask
and answer programming
questions about this book,
join discussions on the
hottest topics in the industry,
and connect with fellow
programmers from around
the world.


đ

Microsoft Office 2010

$44.99 USA
$53.99 CAN

Wrox Programmer to Programmer

đ

ã Explores different ways to bring data into PowerPivot

Programmer
Forums

Join the discussion @ p2p.wrox.com

Microsoft

Professional Microsoft PowerPivot for Excel and SharePoint:
• Shows how to use PowerPivot for Excel to generate rich and
interactive analysis solutions

wrox.com

Professional PowerPivot for Excel and SharePoint

PowerPivot brings the power of Microsoft
Business Intelligence to Office 2010!


Professional
Microsoft

®

PowerPivot
for Excel and SharePoint
®

®

Sivakumar Harinath, Ron Pihlgren, Denny Guang-Yeu Lee


Related Wrox Books
Access 2010 Programmer’s Reference

Programmer to Programmer

™

Get more out of
wrox.com

ISBN: 978-0-470-59166-6
Access 2010 brings better integration with SQL Server and enhanced XML support and this guide shows you how to take advantage
of these and other improvements. With in-depth coverage of VBA, macros, and other programming methods for building Access
applications, this book also provides real-world code examples to demonstrate each topic.


Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports
ISBN: 978-0-470-56311-3
Packed with proven design practices, this book serves as a collection of recipes for solving design problems so that you don’t
have to reinvent the wheel with each challenge you face. Organized by specific types of reports, the book covers grouped reports,
charts, composite reports, dashboards, forms and labels, interactive reports, and more. Step-by-step instructions allow you to
implement these best practices immediately so that you can solve your own design hurdles quickly.

Microsoft SQL Server 2008 Integration Services: Problem - Design - Solution
ISBN: 978-0-470-52576-0
SQL Server Integration Services (SSIS) is the most widely adopted Business Intelligence (BI) component of SQL Server and the
leading extraction, transformation, and loading (ETL) tool in the data warehouse industry. Written by a team of Microsoft MVPs
for SQL Server, this book presents you with an array of common problems that SSIS administrators and developers face on a daily
basis, and walks you through the steps necessary to solve these challenges.

Knight’s 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services

Interact

Join the Community

Take an active role online by participating in our
P2P forums @ p2p.wrox.com

Sign up for our free monthly newsletter at
newsletter.wrox.com

ISBN: 978-0-470-49692-3
This unique lesson-based book walks you through the essential parts of SSIS. Each lesson is streamlined to teach a specific
component of SSIS, helping you to focus on just what you need in order to succeed. As many readers learn better by getting their
hands on the product, this book provides you with a step-by-step lab for each lesson with a video to accompany it.


Professional Microsoft SharePoint 2007 Reporting with SQL Server 2008 Reporting Services

Wrox Online Library

Browse

Hundreds of our books are available online
through Books24x7.com

Ready for more Wrox? We have books and
e-books available on .NET, SQL Server, Java,
XML, Visual Basic, C#/ C++, and much more!

Wrox Blox
Download short informational pieces and code
to keep you up to date and out of trouble!

ISBN: 978-0-470-48189-9
This book walks you through the various challenges of combining SharePoint 2007 with SSRS 2008, and provides you with tips
and tricks for handling the obstacles that you may face when attempting to develop reports in your SharePoint environment. You’ll
discover the new features of SSRS 2008 while also learning helpful techniques for creating quick and engaging reports when using it.

Professional Microsoft SQL Server Analysis Services 2008 with MDX
ISBN: 978-0-470-24798-3
This book explains how to best use these enhancements for your business needs. The authors provide you with valuable insight
on how to use Analysis Services 2008 effectively to build, process, and deploy top-of-the-line business intelligence applications.
You’ll explore everything that Analysis Services 2008 has to offer with the help of step-by-step instructions on building
multi-dimensional databases.


Professional Microsoft SQL Server 2008 Reporting Services
ISBN: 978-0-470-24201-8
This hands-on guide will show you how to harness the full power of Reporting Services to create reporting and business intelligence
solutions that meet your company’s needs. It walks you step-by-step through the fundamentals of designing the most effective
reports by following careful planning considerations. The authors progress from beginning to advanced report design and filtering
techniques, showing you the conditions where reports could be more efficient. They also explore holistic business intelligence
solutions, comprehensive OLAP/Analysis Services reporting, and complete production-deployment scenarios.

Contact Us.
We always like to get feedback from our readers. Have a book idea?
Need community support? Let us know by e-mailing


Professional
Microsoft® PowerPivot
for Excel® And SharePoint®
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi

⊲⊲ Part I

Introduction

Chapter 1

Self-Service Business Intelligence and Microsoft PowerPivot. . . . . . . . . . 3

Chapter 2

A First Look at PowerPivot. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23


⊲⊲ Part IICreating Self-Service BI Applications
Using PowerPivot
Chapter 3

Assembling Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

Chapter 4

Enriching Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87

Chapter 5

Self-Service Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

Chapter 6

Self-Service Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147

⊲⊲ Part III IT PROFESSIONAL
Chapter 7

Preparing for SharePoint 2010. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179

Chapter 8

PowerPivot for SharePoint Setup and Configuration. . . . . . . . . . . . . . . 195

Chapter 9

Troubleshooting, Monitoring, and Securing PowerPivot Services. . . 233


Chapter 10 Diving into the PowerPivot Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . 277
Chapter 11

Enterprise Considerations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301

⊲⊲ Part IVAppendix
Appendix A: Setting Up the SDR Healthcare Application. . . . . . . . . . . . . . . . . . . . . . 339
Appendix B: DAX Reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Online Only
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345

587379ffirs.indd 1

5/11/10 12:45:53 PM


587379ffirs.indd 2

5/11/10 12:45:53 PM


Professional

Microsoft® PowerPivot
for Excel® and Sharepoint®

587379ffirs.indd 3

5/11/10 12:45:53 PM



587379ffirs.indd 4

5/11/10 12:45:53 PM


Professional

Microsoft® PowerPivot
for Excel® and SharePoint®

Sivakumar Harinath
Ron Pihlgren
Denny Guang-Yeu Lee

587379ffirs.indd 5

5/11/10 12:45:53 PM


Professional Microsoft® PowerPivot for Excel® and SharePoint®
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256

www.wiley.com
Copyright © 2010 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-58737-9

Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,
electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108
of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization
through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA,
01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to
the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax
(201) 748-6008, or online at />Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with
respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including
without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or
promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is
sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the
publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred
to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work
was written and when it is read.
For general information on our other products and services please contact our Customer Care Department within the
United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available
in electronic books.
Library of Congress Control Number: 2010928461
Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress
are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other
countries, and may not be used without written permission. Microsoft is a registered trademark of Microsoft Corporation
in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley
Publishing, Inc. is not associated with any product or vendor mentioned in this book.

587379ffirs.indd 6

5/11/10 12:45:53 PM



To Shreepriya Sundaravathanen, Praveen Sivakumar,
and Divya Sivakumar
 — ​Sivakumar H arinath
To my wife, Nujsaran. Your support is why I can do
what I do.
 — ​Ron Pihlgren
To Isabella and Hua-Ping. I can climb because you are
there when I fall.
 — ​Denny Lee

587379ffirs.indd 7

5/11/10 12:45:54 PM


587379ffirs.indd 8

5/11/10 12:45:54 PM


About the Authors

Sivakumar Harinath  was born in Chennai, India. He has a Ph.D. in Computer Science from the
University of Illinois at Chicago. His thesis title was “Data Management Support for Distributed
Data Mining of Large Datasets over High-Speed Wide Area Networks.” Harinath has worked
for Newgen Software Technologies (P) Ltd; IBM Toronto Labs, Canada; National Center for
Data Mining; and the University of Illinois at Chicago. He started as a Software Design Engineer
in Test (SDET) at Microsoft in 2002 for the Analysis Services Performance Team, and is currently a Senior Test Manager in the SQL Server Analysis Services team. Harinath has co-authored

Professional Microsoft SQL Server Analysis Services 2005 with MDX (Indianapolis: Wiley,
2006), MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase
(Indianapolis: Wiley, 2006), and Professional Microsoft SQL Server Analysis Services 2008 with
MDX (Indianapolis: Wiley, 2009). His other interests include high-performance computing, distributed systems, and high-speed networking. He is married to Shreepriya and has twins, Praveen
and Divya. His personal interests include travel, games/sports (in particular, carrom, chess, racquetball and board games). You can reach him at
Ronald Pihlgren  is a native of Chicago, Illinois. He has a Bachelor of Science degree in Computer

Science from DePaul University. A 15-year veteran at Microsoft, he is currently a Senior Test Manager
on the SQL Server Analysis Services team, and has been a part of the project that became PowerPivot
since it was in incubation. He was one of the principal technical reviewers for the book Professional
Microsoft SQL Server Analysis Services 2008 with MDX (Indianapolis: Wiley, 2009). He has a blog
at This is his first book as an author.
Denny Lee  is a Senior Program Manager with Microsoft based out of Redmond, Washington, in

the SQL Customer Advisory Team (SQLCAT) DW/BI Group. He has more than 13 years of experience as a developer and consultant implementing software solutions to complex online transaction
processing (OLTP) and data warehousing problems. His industry experience includes accounting,
human resources, automotive, retail, Web analytics, telecommunications, and healthcare. He helped
create the first online analytical processing (OLAP) services reporting application in production at
Microsoft. He co-authored Professional Microsoft SQL Server Analysis Services 2008 with MDX
(Indianapolis: Wiley, 2009), Professional Microsoft SQL Server 2000 Data Warehousing with
Analysis Services (Indianapolis: Wiley, 2001), and Transforming Healthcare through Information
(New York: Springer, 2004). In addition to contributing to the SQLCAT Blog, SQL Server Best
Practices, and SQLCAT.com, you can also review Lee’s blog at . Lee specializes in developing solutions for Enterprise Data Warehousing, Analysis Services, and Data Mining.
He also has focuses in the areas of Privacy and Healthcare.

587379ffirs.indd 9

5/11/10 12:45:54 PM



About the Technical Editor

John Sirmon  is a Senior Escalation Engineer with the SQL Server Analysis Services Support team

at Microsoft, based in the Microsoft Regional Support Center in Charlotte, North Carolina. He
has worked for Microsoft since March, 2001, and began working with Microsoft SQL Server more
than 10 years ago when he began his professional career as a consultant at PricewaterhouseCoopers.
He has extensive development experience with Microsoft Visual Studio and all the components of
the Microsoft BI Stack. His specialties include Analysis Services performance tuning, Reporting
Services, SharePoint integration, and troubleshooting Kerberos authentication. Sirmon has presented on topics ranging from Reporting Services SharePoint Integration to Analysis Services at SQL
Server PASS Summits and Microsoft TechReady conferences. He holds a Bachelor of Science degree
in Business Administration from the Citadel. Sirmon holds Microsoft Certified Solution Developer
(MCSD) and Microsoft Certified Database Administrator (MCDBA) certifications.

587379ffirs.indd 10

5/11/10 12:45:54 PM


Credits
Executive Editor

Robert Elliott

Vice President and Executive Group
Publisher

Richard Swadley
Project Editor


Kevin Shafer

Vice President and Executive Publisher

Barry Pruett
Technical Editor

John Sirmon

Associate Publisher

Jim Minatel
Production Editor

Kathleen Wisor

Project Coordinator, Cover

Lynsey Stanford
Copy Editor

Christopher Jones

Compositor

Jeffrey Lytle, Happenstance Type-O-Rama
Editorial Director

Robyn B. Siesky


ProofreaderS

Editorial Manager

Jen Larsen, Word One
Michael Shaw, Word One

Mary Beth Wakefield
Indexer
Marketing Manager

Johnna VanHoose Dinse

Ashley Zurcher
Cover Designer
Production Manager

Michael E. Trent

Tim Tate
Cover Image

© Ben Blankenburg/istockphoto

587379ffirs.indd 11

5/11/10 12:45:54 PM


587379ffirs.indd 12


5/11/10 12:45:54 PM


Acknowledgments

The authors thank  SQLCAT, the SQL Server Analysis Services team, Excel team, and Excel

Services team for their help and contribution to this book. The authors would like to specially thank
Rob Collie, Howie Dickerman, Deva Kaladipet Muthukumarasamy, Amir Netz, Sergey Volegov,
Dave Wickert, Lee Graber, John Hancock, Marius Dumitru, Jeffrey Wang, Karen Aleksanyan,
Ashvini Sharma, Kathy MacDonald, Marcelo Blinder, Bogdan Crivat, Leon Cyril, and Thierry
D’Hers for their key contributions and reviews for the book. The authors thank John Sirmon for
technical review of the book, as well as Kevin Shafer for helping with all the logistics of technical editing and publishing, and keeping us on track for all the timelines. The authors finally thank
Robert Elliott for all his support of this book, from initial proposal to final completion.

587379ffirs.indd 13

5/11/10 12:45:54 PM


587379ffirs.indd 14

5/11/10 12:45:54 PM


Contents

Introduction


xxi

Part I: Introduction
Chapter 1: Self-Service Business Intelligence
and Microsoft PowerPivot

SQL Server 2008 R2
Self-Service Business Intelligence
Power Pivot: Microsoft’s Implementation of Self-Service BI
PowerPivot Applications
PowerPivot for Excel
PowerPivot for SharePoint
The Analysis Services Engine in VertiPaq Mode

Summary
Chapter 2: A First Look at PowerPivot

PowerPivot for Excel
Setting the Stage
Setup and Installation
Importing Data
Analyzing and Enriching Data
Creating a PivotTable/PivotChart
Sharing Your Data

PowerPivot for SharePoint
Single-Machine New Farm Install
Publishing Your Workbook
Viewing PowerPivot Workbooks
Viewing the PowerPivot Gallery


Summary

3

4
4
6
6
7
13
18

21
23

24
24
25
27
32
39
43

44
44
51
51
52


53

Part II: Creating Self-Service BI Applications Using PowerPivot
Chapter 3: Assembling Data

Importing Data
Relational Databases
Multi-Dimensional Data Sources

587379ftoc.indd 15

57

58
58
64

5/11/10 12:46:07 PM


CONTENTS

Data Feeds
Text Files
Importing the data

Other Ways to Bring Data into PowerPivot
Pasting From the Clipboard
Linked Excel Tables


The Healthcare Audit Application
Assembling Data for the Healthcare Audit Application
Importing the Main Data Table
Importing the Related Tables
Adding Data from Other Sources

Summary
Chapter 4: Enriching Data

68
70
74

77
77
78

80
80
80
82
83

86
87

Exploring the PowerPivot Window

87


Data Refresh
Formatting Data
Column Operations
Relationships
Data Analysis Expressions (DAX)
Managing Connections
Update Import Definition

88
89
91
95
97
106
107

Enriching Data for the Healthcare Audit Application
Establishing Relationships
Defining DAX Calculations

Summary
Chapter 5: Self-Service Analysis

PivotTables and PivotCharts
Single PivotTable
Single PivotChart
Flattened PivotTable

The PowerPivot Field List
The New PowerPivot Data Model

Automatic Relationship Detection
Metadata Refresh

Slicers
Excel Slicers
PowerPivot-Enhanced Slicers

DAX Measures
Looking at Some Examples

107
108
110

111
113

113
115
116
117

118
119
120
122

122
123
125


127
129

xvi

587379ftoc.indd 16

5/11/10 12:46:07 PM


CONTENTS

PowerPivot and Other Excel Features
Cube Formulas
Named Sets

Analysis in the Healthcare Audit Application
The Server Group PivotTable Report
The Dashboard Page
The “Top 5” Report

Summary
Chapter 6: Self-Service Reporting

Publishing PowerPivot Workbooks
PowerPivot for SharePoint
PowerPivot Gallery
Architecture of PowerPivot for SharePoint
PowerPivot Data Refresh

Building Ad Hoc Reports

Adding Reporting to the SDR Healthcare Application
Summary

131
132
132

132
132
137
144

146
147

147
150
151
156
157
162

166
176

Part III: IT PROFESSIONAL
Chapter 7: Preparing for SharePoint 2010


179

SharePoint 2010
Why Not SharePoint “Lite” BI Edition?
Excel Services

179
180
181

Comparing Excel and Excel Services
Excel Services and PowerPivot

182
182

Key Servers in PowerPivot for SharePoint

184

SharePoint Web Front End (WFE)
SharePoint Application Servers (App Servers)
SharePoint Databases

184
185
186

Key Services in PowerPivot for SharePoint


186

The Analysis Services Service in PowerPivot
PowerPivot System Service

187
189

Services Architecture Workflow Scenarios

191

Excel Client Upload to SharePoint
Excel Services Rendering
Excel Services Server Action

Summary

191
192
193

194

xvii

587379ftoc.indd 17

5/11/10 12:46:07 PM



CONTENTS

Chapter 8: PowerPivot for SharePoint Setup
and Configuration

Required Hardware and Software
Single-Server Hardware Requirements
Multi-Server Hardware Requirements
Software Requirements

Setup and Configuration
Multi-Server Farm Setup
Install SQL Server on the SharePoint Database Server
Install SharePoint 2010 on the SharePoint WFE
Configuring the SharePoint WFE
Initial Farm Configuration Wizard on the WFE
Install SharePoint 2010 on the SharePoint App Server
Configuring the SharePoint App Server
Confirm SharePoint Farm Setup
Install SQL Server 2008 R2 Analysis Services on the
SharePoint App Server
Deploy, Configure, and Activate PowerPivot for SharePoint
Configuration of the PowerPivotUnattendedAccount
Enabling PowerPivot Management Dashboard Data Collection
Turn off Excel Calculation Services on the SharePoint WFE
Final Configuration Steps

195


196
196
197
197

198
199
200
201
204
207
209
210
211
212
213
216
219
220
220

Verify the PowerPivot for SharePoint Setup

222

Publishing Your Excel Workbooks
Viewing Workbooks in PowerPivot Gallery
Viewing Workbooks in Excel Services
Additional Verification Steps (Optional)


222
224
224
225

Optional Setup Steps
Configuring File Size Limits
Turning off the External Data Warning on Data Refresh
Integrating Reporting Services
Add More Servers to your PowerPivot for SharePoint Farm

Summary
Chapter 9: Troubleshooting, Monitoring, and
Securing PowerPivot Services

Troubleshooting Tools
Tracing Tool in PowerPivot for Excel
SQL Server Profiler
ULS Logs

225
225
226
227
230

232
233

234

234
235
241

xviii

587379ftoc.indd 18

5/11/10 12:46:08 PM


CONTENTS

Troubleshooting Issues

245

Installation
Usage
Connectivity
Configuration Issues

246
248
252
255

Monitoring PowerPivot Services
Monitoring Infrastructure (Server Health)
Workbook Activity

Data Refresh
Reports
Dashboard Settings

Security
Security Services
Site Access
Infrastructure Access

Summary
Chapter 10: Diving into the PowerPivot Architecture

PowerPivot for Excel Architecture
PowerPivot for SharePoint Architecture
PowerPivot for SharePoint Services Architecture
Diving into Excel Services
Diving into PowerPivot Services
“Time” to Take a Break
Diving into Windows Identity Foundation

Summary
Chapter 11: Enterprise Considerations

Capacity Planning
Resources
Recommended Hardware Requirements
Provisioning
SharePoint Topologies

SharePoint WFEs

SharePoint App Servers
Excel Calculation Services
PowerPivot System Service
SSAS Engine Service

SharePoint Databases
Sizing
Scaling Out

261
262
264
265
267
269

269
269
270
273

275
27 7

278
282
282
284
286
293

294

299
30 1

302
302
303
306
308

313
314
315
316
322

323
323
323
xix

587379ftoc.indd 19

5/11/10 12:46:08 PM


CONTENTS

Maintenance

Remote Blob Store

Upgrade and Patching Considerations
Upgrading from SharePoint 2007 to 2010
Upgrade and Patch Management

Upload Considerations
Save As Versus Upload
LargeChunkFileSize Configuration
SharePoint Upload Versus File Copy
Impact of Online Edit

Summary

324
324

324
325
326

327
327
330
330
332

335

Part IV: Appendix

Appendix A: Setting Up the SDR Healthcare Application

Setting Up the SQL Server Audit Database
Setting Up the Database Group Name SharePoint List
Setting Up the Client Address to State Report

339

339
340
342

Appendix B: DAX ReferenceOnline Only
Index

345

xx

587379ftoc.indd 20

5/11/10 12:46:08 PM


Introduction

Self-service business intelligence (BI)  is hot! Companies are scrambling to provide easier-to-

use tools to bring the benefits of BI to analysts and business decision makers at all levels of organizations. PowerPivot is Microsoft’s entry into this fast-growing market.
Built on top of Microsoft’s popular Office suite, PowerPivot extends Excel and SharePoint to create

a self-service BI system that allows creation of applications inside Excel 2010, a server-side component that enhances SharePoint 2010 with the capability to share those applications across the organization, update them with the latest data, and monitor how people are using them.
This book describes all aspects of PowerPivot and shows you how to use each of its major features.
It also provides insight into the design and development of this innovative product. By the time you
are finished with this book, you will be well on your way to becoming a PowerPivot expert.

Who This Book Is For
This book is for people who want to learn about PowerPivot end to end. You should have some rudimentary knowledge of databases and data analysis. Familiarity with Microsoft Excel and Microsoft
SharePoint is helpful, since PowerPivot builds on those two products.
Part I of the book is for those who want an introduction to PowerPivot. It provides background on
self-service BI and how PowerPivot fits into the picture. It also includes a quick end-to-end walkthrough of the major features in PowerPivot for those who want to get their feet wet.
Part II is for those who want to understand the client half of PowerPivot — ​PowerPivot for Excel.
This includes Excel power users who work with PivotTables day in and day out, and are curious
about the additional capabilities PowerPivot can provide to them, as well as BI professionals who
want to understand the details of what PowerPivot is and what you can do with it. If you are a business analyst, this section will be particularly relevant for you.
Part III of the book is for those who want to learn about the server side of PowerPivot. This includes
IT professionals who want to learn about how to plan for, deploy, and maintain PowerPivot’s server
infrastructure. Since PowerPivot builds on SharePoint, SharePoint administrators who are responsible for adding PowerPivot to their SharePoint farm will find a wealth of information in this section
of the book.

What This Book Covers
This book covers the first version of PowerPivot, which ships with SQL Server 2008 R2 and
enhances Microsoft Office 2010. It provides an overview of PowerPivot and a detailed look at its
two components: PowerPivot for Excel and PowerPivot for SharePoint. It explains the technologies that make up these two components, and gives some insight into why these components were

587379flast.indd 21

5/11/10 12:46:20 PM


introduction


implemented the way they were. Through an extended example, it shows how to build a PowerPivot
application from end to end.

How This Book Is Structured
After discussing self-service BI and the motivation for creating PowerPivot, we present a quick,
end-to-end tutorial showing how to create and publish a simple PowerPivot application. We
then drill into the features of PowerPivot for Excel in detail and, in the process, build a more
complex PowerPivot application based on a real-world case study. Finally, we discuss the server
side of PowerPivot (PowerPivot for SharePoint) and provide detailed information about its installation and maintenance.
Chapter 1, “Self-Service Business Intelligence and Microsoft PowerPivot,” begins Part I of the book.
This chapter describes self-service BI and introduces PowerPivot, Microsoft’s first self-service BI
tool. It provides a high-level look at the two components that make up PowerPivot — ​PowerPivot for
Excel and PowerPivot for SharePoint.
Chapter 2, “A First Look at PowerPivot,” walks you through a simple example of creating a
PowerPivot application from end to end. In the process, it shows how to set up the two components
of PowerPivot (PowerPivot for Excel and PowerPivot for SharePoint), and describes the normal
workflow of creating a simple PowerPivot application.
Chapter 3, “Assembling Data,” starts off Part II of the book, and explains how to bring data into
PowerPivot from various external data sources. It also introduces the extended example that you
will build in this and subsequent chapters.
Chapter 4, “Enriching Data,” shows how to enhance the data you brought into your application by creating relationships and using PowerPivot’s expression language, Data Analysis eXpressions (DAX).
Chapter 5, “Self-Service Analysis,” describes how to use your PowerPivot data with various Excel
features, such as PivotTables, PivotCharts, and slicers to do analysis. Chapter 5 also delves further
into DAX, showing how to create and use DAX measures.
Chapter 6, “Self-Service Reporting,” shows how to publish your PowerPivot workbook to the server
side of PowerPivot (PowerPivot for SharePoint), and make use of its features to view and update
PowerPivot reports. It also shows how to use the data in a PowerPivot workbook as a data source
for reports created in other tools such as Report Builder 3.0 and Excel.
Chapter 7, “Preparing for SharePoint 2010,” is the first chapter in Part III of the book. It describes

the components of SharePoint 2010 that are relevant for PowerPivot, and looks at how PowerPivot
for SharePoint interacts with those components.
Chapter 8, “PowerPivot for SharePoint Setup and Configuration,” provides instructions on how to
set up and configure a multi-machine SharePoint farm that contains PowerPivot for SharePoint.
Chapter 9, “Troubleshooting, Monitoring, and Securing PowerPivot Services,” gives tips on how
to troubleshoot PowerPivot for SharePoint issues. It also shows how to monitor the health of your
PowerPivot for SharePoint environment, and discusses relevant security issues.
xxii

587379flast.indd 22

5/11/10 12:46:20 PM


IntroductIon

Chapter 10, “Diving into the PowerPivot Architecture,” describes at a deeper level the architecture
of PowerPivot, both for client and server. It also explains the Windows Identity Foundation and discusses the use of Kerberos in the context of PowerPivot for SharePoint.
Chapter 11, “Enterprise Considerations,” talks about common PowerPivot for SharePoint enterprise
considerations: capacity planning, optimizing the environment, upgrade considerations, and uploading performance.
Appendix A provides instructions for setting up the data sources that are used to build the SDR
Healthcare extended example in Chapters 3 through 6.
Additionally, two “bonus” elements are available online at this book’s companion Web site (see the
later section, “Source Code”):
➤➤

Appendix B is a comprehensive DAX reference that describes all the DAX functions and
provides code snippets that show how to use them. The content published in Appendix B has
been provided by Microsoft.


What You need to uSe thIS book
To work through the examples in this book, you will need Microsoft Office Excel 2010 and
Microsoft Office SharePoint 2010. You will also need PowerPivot for Excel (which is available as
a free download) and PowerPivot for SharePoint (which is included in the Enterprise edition of
Microsoft SQL Server 2008 R2). The Contoso BI Demo Database, available from the Microsoft
Download Center, is needed for the Chapter 2 tutorial. Data that is needed for the extended BI
Healthcare example is available on this book’s www.wrox.com download site. Instructions for
installing the data needed for the example are included in Appendix A.

conventIonS
To help you get the most from the text and keep track of what’s happening, we’ve used a number of
conventions throughout the book.

Boxes with a warning icon like this one hold important, not-to-be-forgotten
information that is directly relevant to the surrounding text.

The pencil icon indicates notes, tips, hints, tricks, or asides to the current
discussion.

xxiii

587379flast.indd 23

5/11/10 12:46:20 PM