for Excel and SharePoint

Sivakumar

Harinath

Ron

_Pihlgren

Denny Guang-Yeu

Lee

WILEY

INTRODUCTION xxi

fBPPVStlKI^KT'Y^^MfSCSt"'- " : '.

CHAPTER 1: SELF-SERVICE BUSINESS

INTELLIGENCE

AND MICROSOFT POWERPIVOT 3

SQL Server 2008 R2 4

Self-Service Business

_Intelligence

4

Power Pivot: Microsoft's

_{Implementation}

of Self-Service Bl 6

PowerPivot

_Applications

6

PowerPivot for Excel 7

PowerPivot forSharePoint 13

The

_Analysis

Services

_Engine

in

_VertiPaq

Mode 18

Summary

21

CHAPTER 2: A FIRST LOOK AT POWERPIVOT 23

PowerPivot

for Excel 24

Setting

the

_Stage

24

Setup

and Installation 25

ImportingData 27

Analyzing

and

_Enriching

Data 32

Creating

aPivotTable/PivotChart 39

Sharing

YourData 43

PowerPivot for SharePoint 44

Single-Machine New Farm Install 44

Publishing

YourWorkbook 51

Viewing

PowerPivot Workbooks 51

Viewing

the PowerPivot

Gallery

52

Summary

53

CHAPTER 3:ASSEMBLING DATA 57

Importing

Data 58

Relational Databases 58

Data Feeds 68

Text Files 70

Importing

the data 74

Other

_Ways

to

_Bring

Data into PowerPivot 77

Pasting

From the

_Clipboard

77

Linked Excel Tables 78

TheHealthcareAudit

_Application

80

Assembling

Data forthe Healthcare

Audit

Application

80

Importing

the Main DataTable 80

Importingthe Related Tables 82

Adding

Datafrom Other Sources 83

Summary

86

CHAPTER 4: ENRICHING DATA 87

Exploring

the PowerPivot Window 87

Data Refresh 88

Formatting

Data 89

Column

_Operations

91

Relationships

95

Data

_{Analysis Expressions (DAX)}

97

Managing

Connections 106

Update Import

Definition 107

Enriching

Data for the Healthcare Audit

_Application

107

Establishing Relationships

108

Defining

DAX Calculations 110

Summary

111

CHAPTER 5: SELF-SERVICE ANALYSIS 113

PivotTables and PivotCharts 113

Single

PivotTable 115

Single

PivotChart 116

Flattened PivotTable 117

The PowerPivot Field List 118

The New PowerPivot Data Model 119

Automatic _RelationshipDetection 120

Metadata Refresh 122

Slicers 122

Excel Slicers 123

PowerPivot-Enhanced Slicers 125

DAX Measures 127

Looking

at Some

_Examples

129

PowerPivot and Other Excel Features 131

Cube Formulas 132

Named Sets 132

Analysis

in the Healthcare Audit

Application

132

The Server

_Group

PivotTable

_Report

132

The Dashboard

_Page

137

The

_"Top

5"

_Report

144

Summary

146

CHAPTER 6: SELF-SERVICE REPORTING 147

Publishing

PowerPivot

Workbooks 147

PowerPivot for SharePoint 150

PowerPivot

Gallery

151

Architecture of PowerPivot for SharePoint 156

PowerPivot Data Refresh 157

BuildingAd HocReports 162

Adding Reporting

tothe SDR Healthcare

Application

166

Summary

176

CHAPTER 7: PREPARING FOR SHAREPOINT 2010 179

SharePoint 2010 179

Why

Not SharePoint "Lite" Bl Edition? 180

Excel Services 181

Comparing

Excel and Excel Services 182

Excel Services andPowerPivot 182

Key

Servers inPowerPivot for SharePoint 184

SharePointWeb Front End

_(WFE)

184

SharePoint

_Application

Servers

_{(App Servers)}

185

SharePoint Databases 186

Key

Services in PowerPivotforSharePoint 186

The

Analysis

ServicesServicein PowerPivot 187

PowerPivotSystem Service 189

Services Architecture Workflow Scenarios 191

Excel Client

_Upload

to SharePoint 191

Excel Services

Rendering

192

Excel Services Server Action 193

CHAPTER 8: _{POWERPIVOT FOR SHAREPOINT SETUP}

AND CONFIGURATION ₁₉₅

Required

Hardware and Software 196

Single-Server

Hardware

_Requirements

196

Multi-Server Hardware

_Requirements

197

Software

Requirements

197

Setup

and

_{Configuration}

198

Multi-ServerFarm

_Setup

199

Install SQL Server onthe SharePoint DatabaseServer 200

Install SharePoint2010ontheSharePoint WFE ₂₀₁

Configuring

the SharePoint WFE 204

Initial Farm

_{Configuration}

Wizardonthe WFE 207

Install SharePoint 2010 onthe SharePoint

_App

Server 209

Configuring

the SharePoint

_App

Server 210

Confirm SharePointFarm

Setup

211

Install SQL Server 2008 R2

Analysis

Services onthe

SharePoint

_App

Server ₂₁₂

Deploy, Configure,

and ActivatePowerPivot for SharePoint 213

Configuration

ofthePowerPivotUnattendedAccount 216

Enabling

PowerPivot

Management

Dashboard Data Collection 219

Turn off ExcelCalculation Services onthe SharePointWFE 220

Final

_{Configuration Steps}

₂₂₀

Verify

the

PowerPivot

for SharePoint

_Setup

222

Publishing

YourExcel Workbooks 222

Viewing

Workbooks in PowerPivot

_Gallery

224

Viewing

Workbooks in Excel Services 224

Additional Verification

_{Steps (Optional)}

₂₂₅

Optional

Setup Steps

225

Configuring

File Size Limits ₂₂₅

Turning

off the External Data

_Warning

on Data Refresh 226

Integrating

Reporting

Services ₂₂₇

Add MoreServersto_{your PowerPivot for SharePoint Farm 230}

Summary

232

CHAPTER 9:

_{TROUBLESHOOTING,}

_MONITORING,

AND

SECURING POWERPIVOT SERVICES 233

Troubleshooting

Tools ₂₃₄

Tracing

Tool inPowerPivot for Excel 234

SQL ServerProfiler 235

ULS

_Logs

₂₄₁

Troubleshooting

Issues 245

Installation 246

Usage

248

Connectivity

252

Configuration

Issues 255

Monitoring

PowerPivot Services 261

Monitoring

Infrastructure

(Server

Health)

262

Workbook

_Activity

264 Data Refresh 265

Reports

267 Dashboard

_Settings

269

Security

269

Security

Services 269 SiteAccess 270 InfrastructureAccess 273

Summary

275

CHAPTER 10: DIVING INTO THE POWERPIVOT

ARCHITECTURE

277

PowerPivot for Excel Architecture 278

PowerPivot for SharePoint Architecture 282

PowerPivot for SharePoint Services Architecture 282

Diving

into Excel Services 284

Diving

into PowerPivot Services 286

"Time" to TakeaBreak 293

Diving

intoWindows

Identity

Foundation 294

Summary

299

CHAPTER 11: ENTERPRISE CONSIDERATIONS 301

Capacity Planning

302

Resources 302

Recommended Hardware

Requirements

303

Provisioning

306

SharePoint

_Topologies

308

SharePoint WFEs 313

SharePoint

_App

Servers 314

Excel Calculation Services 315

PowerPivot

_System

Service 316

SSAS

_Engine

Service 322

SharePoint Databases

323

Sizing

323

Maintenance 324

Remote Blob Store 324

Upgrade

and

_Patching

Considerations 324

Upgrading

from SharePoint 2007 to 2010 325

Upgrade

andPatch

_Management

326

Upload

Considerations 327

Save As Versus

Upload

327

LargeChunkFileSize

Configuration

330

SharePointUploadVersusFile

_Copy

330

Impact

of Online Edit 332

Summary

335

APPENDIX A: SETTING UP THE SDR HEALTHCARE APPLICATION 339

Setting

Up

the SQL Server Audit Database 339

Setting

Up

the Database

_Group

NameSharePoint List 340

Setting Up

the Client Addressto State

Report

342

APPENDIX B: DAX REFERENCE ONLINE ONLY

INDEX 345