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.
Autorentext
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.
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.
Klappentext
PowerPivot brings the power of Microsoft Business Intelligence to Office 2010!
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.
Professional Microsoft PowerPivot for Excel and SharePoint:
- Shows how to use PowerPivot for Excel to generate rich and interactive analysis solutions
- Explores different ways to bring data into PowerPivot
- 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
Inhalt
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
Publ…