Professional Microsoft PowerPivot for Excel and SharePoint

著者名：Harinath, Sivakumar/Pihlgren, Ron/Lee, Denny Guang-Yeu

Wrox（2010/05/25発売）

• 言語：ENG
• ISBN：9780470587379
• eISBN：9780470913444

Description

The next wave of business intelligence, Self-Service BI, seeks to meet the demands of powerful hardware and shorter decision time frames. This book introduces PowerPivot for Excel and SharePoint, showing you how to use this innovative software for self-service analytics and reporting. Authored by key members of the Microsoft team that built the product, this book will guide you through the use of in-memory BI server technology, data analytics eXpressions, and report gallery. The book also discusses how to deploy and manage sandbox servers, and a companion website provides sample reports and applications.

Table of Contents

Introduction xxi

Part I: Introduction

Chapter 1: Self-Service Business Intelligence and Microsoft PowerPivot 3

SQL Server 2008 R 2 4

Self-Service Business Intelligence 4

Power Pivot: Microsoft’s Implementation of Self-Service BI 6

PowerPivot Applications 6

PowerPivot for Excel 7

PowerPivot for SharePoint 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

Importing Data 27

Analyzing and Enriching Data 32

Creating a PivotTable/PivotChart 39

Sharing Your Data 43

PowerPivot for SharePoint 44

Single-Machine New Farm Install 44

Publishing Your Workbook 51

Viewing PowerPivot Workbooks 51

Viewing the PowerPivot Gallery 52

Summary 53

Part II: Creating Self-Service BI Applications Using PowerPivot

Chapter 3: Assembling Data 57

Importing Data 58

Relational Databases 58

Multi-Dimensional Data Sources 64

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

The Healthcare Audit Application 80

Assembling Data for the Healthcare Audit Application 80

Importing the Main Data Table 80

Importing the Related Tables 82

Adding Data from 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 Relationship Detection 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

Building Ad Hoc Reports 162

Adding Reporting to the SDR Healthcare Application 166

Summary 176

Part III: IT PROFESSIONAL

Chapter 7: Preparing for SharePoint 2010 179

SharePoint 2010 179

Why Not SharePoint “Lite” BI Edition? 180

Excel Services 181

Comparing Excel and Excel Services 182

Excel Services and PowerPivot 182

Key Servers in PowerPivot for SharePoint 184

SharePoint Web Front End (WFE) 184

SharePoint Application Servers (App Servers) 185

SharePoint Databases 186

Key Services in PowerPivot for SharePoint 186

The Analysis Services Service in PowerPivot 187

PowerPivot System Service 189

Services Architecture Workflow Scenarios 191

Excel Client Upload to SharePoint 191

Excel Services Rendering 192

Excel Services Server Action 193

Summary 194

Chapter 8: PowerPivot for SharePoint Setup and Configuration 195

Required Hardware and Software 196

Single-Server Hardware Requirements 196

Multi-Server Hardware Requirements 197

Software Requirements 197

Setup and Configuration 198

Multi-Server Farm Setup 199

Install SQL Server on the SharePoint Database Server 200

Install SharePoint 2010 on the SharePoint WFE 201

Configuring the SharePoint WFE 204

Initial Farm Configuration Wizard on the WFE 207

Install SharePoint 2010 on the SharePoint App Server 209

Configuring the SharePoint App Server 210

Confirm SharePoint Farm Setup 211

Install SQL Server 2008 R2 Analysis Services on the SharePoint App Server 212

Deploy, Configure, and Activate PowerPivot for SharePoint 213

Configuration of the PowerPivotUnattendedAccount 216

Enabling PowerPivot Management Dashboard Data Collection 219

Turn off Excel Calculation Services on the SharePoint WFE 220

Final Configuration Steps 220

Verify the PowerPivot for SharePoint Setup 222

Publishing Your Excel Workbooks 222

Viewing Workbooks in PowerPivot Gallery 224

Viewing Workbooks in Excel Services 224

Additional Verification Steps (Optional) 225

Optional Setup Steps 225

Configuring File Size Limits 225

Turning off the External Data Warning on Data Refresh 226

Integrating Reporting Services 227

Add More Servers to your PowerPivot for SharePoint Farm 230

Summary 232

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

Troubleshooting Tools 234

Tracing Tool in PowerPivot for Excel 234

SQL Server Profiler 235

ULS Logs 241

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

Site Access 270

Infrastructure Access 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 Take a Break 293

Diving into Windows 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

Scaling Out 323

Maintenance 324

Remote Blob Store 324

Upgrade and Patching Considerations 324

Upgrading from SharePoint 2007 to 2010 325

Upgrade and Patch Management 326

Upload Considerations 327

Save As Versus Upload 327

LargeChunkFileSize Configuration 330

SharePoint Upload Versus File Copy 330

Impact of Online Edit 332

Summary 335

Part IV: Appendix

Appendix A: Setting Up the SDR Healthcare Application 339

Setting Up the SQL Server Audit Database 339

Setting Up the Database Group Name SharePoint List 340

Setting Up the Client Address to State Report 342

Appendix B: DAX Reference online Only

Index 345