Sivakumar Harinath, Ron Pihlgren, Denny Guang-Yeu Lee
Professional Microsoft PowerPivot for Excel and SharePoint (eBook, ePUB)
34,99 €
inkl. MwSt.
Sofort per Download lieferbar
Sivakumar Harinath, Ron Pihlgren, Denny Guang-Yeu Lee
Professional Microsoft PowerPivot for Excel and SharePoint (eBook, ePUB)
- Format: ePub
- Merkliste
- Auf die Merkliste
- Bewerten Bewerten
- Teilen
- Produkt teilen
- Produkterinnerung
- Produkterinnerung
Bitte loggen Sie sich zunächst in Ihr Kundenkonto ein oder registrieren Sie sich bei
bücher.de, um das eBook-Abo tolino select nutzen zu können.
Hier können Sie sich einloggen
Hier können Sie sich einloggen
Sie sind bereits eingeloggt. Klicken Sie auf 2. tolino select Abo, um fortzufahren.
Bitte loggen Sie sich zunächst in Ihr Kundenkonto ein oder registrieren Sie sich bei bücher.de, um das eBook-Abo tolino select nutzen zu können.
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.…mehr
- Geräte: eReader
- ohne Kopierschutz
- eBook Hilfe
- Größe: 16.31MB
- Upload möglich
Andere Kunden interessierten sich auch für
- Laura ReevesA Manager's Guide to Data Warehousing (eBook, ePUB)46,99 €
- Martin KleppmannDatenintensive Anwendungen designen (eBook, ePUB)44,90 €
- Robert VieiraProfessional Microsoft SQL Server 2008 Programming (eBook, ePUB)32,99 €
- Jörn KohlhammerVisual Business Analytics (eBook, ePUB)69,90 €
- Peter GluchowskiData Governance (eBook, ePUB)59,90 €
- Florian HopfElasticsearch (eBook, ePUB)2,99 €
- Brian KnightKnight's Microsoft Business Intelligence 24-Hour Trainer (eBook, ePUB)30,99 €
-
-
-
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.
Produktdetails
- Produktdetails
- Verlag: John Wiley & Sons
- Erscheinungstermin: 25. Mai 2010
- Englisch
- ISBN-13: 9780470913444
- Artikelnr.: 37300561
- Verlag: John Wiley & Sons
- Erscheinungstermin: 25. Mai 2010
- Englisch
- ISBN-13: 9780470913444
- Artikelnr.: 37300561
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.
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.
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
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
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
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