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 Power Pivot for Excel and Share Point, 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 e Xpressions, and report gallery. The book also discusses how to deploy and manage sandbox servers, and a companion website provides sample reports and applications.
Innehållsförteckning
Introduction xxi
Part I: Introduction
Chapter 1: Self-Service Business Intelligence and Microsoft Power Pivot 3
SQL Server 2008 R 2 4
Self-Service Business Intelligence 4
Power Pivot: Microsoft’s Implementation of Self-Service BI 6
Power Pivot Applications 6
Power Pivot for Excel 7
Power Pivot for Share Point 13
The Analysis Services Engine in Verti Paq Mode 18
Summary 21
Chapter 2: A First Look at Power Pivot 23
Power Pivot for Excel 24
Setting the Stage 24
Setup and Installation 25
Importing Data 27
Analyzing and Enriching Data 32
Creating a Pivot Table/Pivot Chart 39
Sharing Your Data 43
Power Pivot for Share Point 44
Single-Machine New Farm Install 44
Publishing Your Workbook 51
Viewing Power Pivot Workbooks 51
Viewing the Power Pivot Gallery 52
Summary 53
Part II: Creating Self-Service BI Applications Using Power Pivot
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 Power Pivot 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 Power Pivot 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
Pivot Tables and Pivot Charts 113
Single Pivot Table 115
Single Pivot Chart 116
Flattened Pivot Table 117
The Power Pivot Field List 118
The New Power Pivot Data Model 119
Automatic Relationship Detection 120
Metadata Refresh 122
Slicers 122
Excel Slicers 123
Power Pivot-Enhanced Slicers 125
DAX Measures 127
Looking at Some Examples 129
Power Pivot and Other Excel Features 131
Cube Formulas 132
Named Sets 132
Analysis in the Healthcare Audit Application 132
The Server Group Pivot Table Report 132
The Dashboard Page 137
The “Top 5” Report 144
Summary 146
Chapter 6: Self-Service Reporting 147
Publishing Power Pivot Workbooks 147
Power Pivot for Share Point 150
Power Pivot Gallery 151
Architecture of Power Pivot for Share Point 156
Power Pivot 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 Share Point 2010 179
Share Point 2010 179
Why Not Share Point “Lite” BI Edition? 180
Excel Services 181
Comparing Excel and Excel Services 182
Excel Services and Power Pivot 182
Key Servers in Power Pivot for Share Point 184
Share Point Web Front End (WFE) 184
Share Point Application Servers (App Servers) 185
Share Point Databases 186
Key Services in Power Pivot for Share Point 186
The Analysis Services Service in Power Pivot 187
Power Pivot System Service 189
Services Architecture Workflow Scenarios 191
Excel Client Upload to Share Point 191
Excel Services Rendering 192
Excel Services Server Action 193
Summary 194
Chapter 8: Power Pivot for Share Point 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 Share Point Database Server 200
Install Share Point 2010 on the Share Point WFE 201
Configuring the Share Point WFE 204
Initial Farm Configuration Wizard on the WFE 207
Install Share Point 2010 on the Share Point App Server 209
Configuring the Share Point App Server 210
Confirm Share Point Farm Setup 211
Install SQL Server 2008 R2 Analysis Services on the Share Point App Server 212
Deploy, Configure, and Activate Power Pivot for Share Point 213
Configuration of the Power Pivot Unattended Account 216
Enabling Power Pivot Management Dashboard Data Collection 219
Turn off Excel Calculation Services on the Share Point WFE 220
Final Configuration Steps 220
Verify the Power Pivot for Share Point Setup 222
Publishing Your Excel Workbooks 222
Viewing Workbooks in Power Pivot 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 Power Pivot for Share Point Farm 230
Summary 232
Chapter 9: Troubleshooting, Monitoring, and Securing Power Pivot Services 233
Troubleshooting Tools 234
Tracing Tool in Power Pivot for Excel 234
SQL Server Profiler 235
ULS Logs 241
Troubleshooting Issues 245
Installation 246
Usage 248
Connectivity 252
Configuration Issues 255
Monitoring Power Pivot 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 Power Pivot Architecture 277
Power Pivot for Excel Architecture 278
Power Pivot for Share Point Architecture 282
Power Pivot for Share Point Services Architecture 282
Diving into Excel Services 284
Diving into Power Pivot 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
Share Point Topologies 308
Share Point WFEs 313
Share Point App Servers 314
Excel Calculation Services 315
Power Pivot System Service 316
SSAS Engine Service 322
Share Point Databases 323
Sizing 323
Scaling Out 323
Maintenance 324
Remote Blob Store 324
Upgrade and Patching Considerations 324
Upgrading from Share Point 2007 to 2010 325
Upgrade and Patch Management 326
Upload Considerations 327
Save As Versus Upload 327
Large Chunk File Size Configuration 330
Share Point 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 Share Point List 340
Setting Up the Client Address to State Report 342
Appendix B: DAX Reference online Only
Index 345
Om författaren
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.
Fler e-böcker från samma författare (r) / Redaktör
16 501 E-böcker i denna kategori
