SharePoint 2010 is today’s leading presentation and deployment system for Business Intelligence (BI) solutions based on Microsoft technologies. This book brings together all the information you need to successfully implement and use SharePoint 2010’s powerful BI capabilities. It reflects the unsurpassed real-world experience of five expert consultants at RDA, a Microsoft Gold Partner specializing in delivering advanced BI solutions with SharePoint.
You’ll first learn how to prepare SharePoint 2010 for the deployment of BI solutions. Next, the authors walk through integrating, configuring, and using Reporting Services, PerformancePoint Services, PowerPivot, Visio Services, and other key related technologies.
Building on what you’ve learned, you’ll walk step-by-step through constructing and integrating two end-to-end BI solutions. Finally, in a comprehensive troubleshooting section, the authors present today’s most common SharePoint BI issues, identify proven solutions, and teach effective problem-solving techniques.
- Get started fast, by using the Business Intelligence Center’s prebuilt site collection and template
- Build integrated, end-to-end SharePoint BI solutions
- Present business data to the enterprise through Excel Services
- Install, configure, and integrate Reporting Services and the Reporting Services Add-In for SharePoint
- Define document library content types and manage reports
- Use Report Viewer Web Parts to render reports on SharePoint web pages
- Build enterprise dashboards with PerformancePoint Services
- Secure dashboards via data source delegation, SharePoint permissions, and groups
- Analyze enormous datasets with PowerPivot for Excel and SharePoint
- Publish Visio 2010 data-driven web diagrams that integrate multiple data sources
- Utilize the fine-grained security available through Visio Graphics Service
- Troubleshoot problems with Reporting Services, PerformancePoint Services, PowerPivot, and Visio Services
Table of Contents:
Introduction 1
How This Book Is Organized 2
What This Book Does Not Cover 2
Expanding Your Knowledge 3
Part I Getting Started
1 Introduction to the Business Intelligence Center in SharePoint 2010 7
Planning for Your Business Intelligence Center 7
Server Prerequisites and Licensing Considerations 7
Choosing BI Tools 8
Creating and Configuring Your Business Intelligence Center 9
Creating a New Site Collection Using the Business
Intelligence Center Site Collection Template 9
Reviewing the Business Intelligence Center 9
Reviewing Features Enabled for BI 12
Creating a New Subsite Using the Business Intelligence Center
Site Template 13
Summary 15
Best Practices 15
2 Configuring and Using Excel Services in SharePoint 2010 17
Configuring Excel Services 17
Global Settings 18
Trusted File Locations 22
Trusted Data Providers 25
Trusted Data Connection Libraries 26
User-Defined Functions 27
Using Excel Services 28
Creating a Library for Excel Workbooks 28
Adding Excel Workbooks to the Library 29
Accessing Excel Workbooks from the Library 31
Using the Excel Web Access Web Part 31
Summary 33
Best Practices 33
Part II Reporting Services
3 Reporting Services Setup and Configuration 37
Installing Reporting Services 38
Installing the Reporting Services Add-In for SharePoint 39
Configuring the Reporting Services Add-In for SharePoint Integration 40
Adding Content Types to a Document Library 42
Summary 45
Best Practices 45
4 Report Management 47
Deploying Reports 48
Upload Report to Document Library 48
Deploy Report from BIDS 49
Save from Report Builder 51
Built-In SharePoint Functions 53
Shared Schedules 55
Processing Options 58
Data Sources 60
Shared Datasets 62
Parameters 64
Subscriptions 65
Summary 69
Best Practices 69
5 Using the Report Viewer Web Parts 71
Configuring the SharePoint Integrated Mode Web Part 71
SharePoint Integrated Mode Web Part Properties 75
SharePoint Integrated Mode Web Part Connections 77
Configuring the Native Mode Web Parts 81
Summary 85
Best Practices 86
Part III PerformancePoint Services
6 PerformancePoint Services Configuration 89
Adding PerformancePoint Services 89
Setting up the Secure Store Service 90
Applying PerformancePoint Service Application Settings 91
Enabling Non-Business Intelligence Center Sites with
PerformancePoint Services 93
Setting Trusted Data Source Locations and Trusted Content Locations 96
Summary 98
Best Practices 98
7 PerformancePoint Services Development 99
Using Dashboard Designer 99
Creating Data Connections 102
Analysis Services Data Connection 102
Excel Services Data Connection 104
SharePoint List Data Connection 107
SQL Server Table Data Connection 108
Building Key Performance Indicators 109
Blank KPI 110
Objective 112
Developing Scorecards 113
Creating Reports 117
Analytic Chart 118
Analytic Grid 119
Strategy Map 121
KPI Details 122
Reporting Services 123
Excel Services 124
ProClarity Analytics Server Page 125
Web Page 126
Filtering Data 126
Custom Table 127
MDX Query 127
Member Selection 128
Named Set 128
Time Intelligence 129
Time Intelligence Connection 129
Constructing Dashboards 130
Connecting Filters to Dashboard Content Objects 131
Linking KPI Details Report to a Scorecard 133
Creating Multiple Pages in a Dashboard 134
Deploying the Dashboard to SharePoint 135
User Interaction with the Decomposition Tree 137
Summary 139
Best Practices 139
8 PerformancePoint Services Security 141
Data Source Delegation 141
PerformancePoint Services and SharePoint Permissions 144
Summary 147
Best Practices 147
Part IV PowerPivot
9 PowerPivot for Excel 151
Overview of PowerPivot 151
Installing PowerPivot 152
Using PowerPivot 153
Getting the Data 154
Importing from a Database 154
Importing from a Flat File 157
Preparing the Data 158
Creating Relationships Between the Tables 159
Adding Calculated Fields 160
Hiding Unwanted Fields 161
Presenting the Data 162
Summary 165
Best Practices 165
10 PowerPivot for SharePoint 167
Overview of PowerPivot for SharePoint 167
Installing PowerPivot for SharePoint 168
Hardware and Software Requirements 168
New Single-Server Install 169
Multiserver Farm Install 171
Using PowerPivot for SharePoint 176
PowerPivot Gallery 176
Data Refresh and Snapshots 179
Publishing Workbooks 180
Controlling Data Exposure and Spreadmarts 181
Monitoring PowerPivot 182
Enabling Usage and Health Data Collection 182
Using the PowerPivot Management Dashboard 183
Summary 184
Best Practices 184
Part V Visio Services
11 Configuring Visio Graphics Service 187
Adding the Visio Graphics Service 187
Configuring Visio Graphics Service Global Settings 189
Configuring a Visio Graphics Service Global Settings
Parameters by Using Windows PowerShell 190
Visio Graphics Service Trusted Data Providers 191
Visio Process Repository Site Template 193
Summary 195
Best Practices 195
12 Visio Graphics Service Development 197
Prerequisites 197
Developing a Data-Driven Visio Web Drawing 198
Publish a Drawing to a SharePoint 2010 Document Library 212
Browsing a Visio Web Drawing Online 213
Refreshing Data in a Visio Web Drawing 214
Embedding a Drawing into a SharePoint Page Using the
Visio Web Access Web Part 214
Interaction with Other SharePoint 2010 Applications 218
Custom Data Providers 220
Summary 221
Best Practices 221
13 Visio Graphics Service Security 223
Internal Data Sources 223
External Data Sources 224
Adding a New Trusted Data Provider 224
Data Source Delegation 226
Publishing an ODC File to SharePoint 2010 with Secure Store
Service Security Model 229
Summary 231
Best Practices 231
Part VI End-to-End Solutions
14 Building a Management Dashboard Solution 235
Preparing the Management Console 235
Creating the Site Collection and Sites 235
Customizing the Navigation 237
Configuring Reporting Services 239
Configuring PerformancePoint Services 239
Deployed Solution 239
Creating a Reporting Services Report with Report Builder 3.0 240
Create a Data Source 241
Create a Dataset 242
Create a Report 245
Creating PerformancePoint Content 250
Creating the Analysis Services Data Connection 251
Creating the Scorecard and KPIs 252
Creating Reports 259
Creating the Filter 262
Building and Deploying the PerformancePoint Dashboard 263
Interacting with the BI Dashboard 266
Summary 269
Best Practices 269
15 Integrating Visio and Excel Services 271
Deployed Solution 271
Creating a Visio Web Drawing with Microsoft Visio 2010
Premium Edition 272
Summary 277
Best Practices 277
Part VII Troubleshooting
16 Reporting Services Issues 281
How Do I Create a Shared Dataset in Report Builder? 281
Report Builder Getting Started Dialog Not Displayed 282
Enable Report Builder to Create or Edit Reports 283
Is the Reporting Services Add-In Installed? 284
How Do I Activate the Report Server Integration Feature? 285
How Do I Create Report Artifacts in a SharePoint Library? 285
Checking Report Project Settings 286
Summary 286
Best Practices 286
17 PerformancePoint Services Issues 287
PerformancePoint Services Is Not Configured Correctly 287
Dashboard Connection Formula 289
Creating PerformancePoint Content Items in a Nontrusted Location 292
Insufficient Security for Dashboard Deployment 293
Cube Action Not Available on a PerformancePoint Services Report 294
Summary 295
Best Practices 296
18 PowerPivot Issues 297
Troubleshooting PowerPivot for Excel 297
Enabling Tracing from PowerPivot for Excel 298
Disable Tracing from PowerPivot for Excel 299
Analyze Traces from PowerPivot for Excel 300
Troubleshooting PowerPivot for SharePoint 300
Common Issues 301
Installing PowerPivot 302
Using PowerPivot 302
Summary 303
19 Visio Services Issues 305
Visio Graphics Service Data Refresh Failed (Event ID 8037, 8038, 8062, 8063) 305
Visio Configuration Database Not Found (Event ID 8040) 306
Visio Graphics Service Untrusted Data Provider Request (Event ID 8041) 307
Visio Graphics Service Failed to Generate Diagram (Event ID 8060, 8042, 8043) 307
Visio Proxy Initialization Failed (Event ID 8044) 307
Visio Application Proxy Has Invalid Endpoint (Event ID 8049) 308
Visio Graphics Service Data Provider Not Found (Event ID 8050) 310
Visio Graphics Service File Loading Error (Event ID 8051, 8061) 310
Summary 311
Best Practices 311
Index 313
About the Author :
Steve Mann was born and raised in Philadelphia, Pennsylvania, where he still resides today. He is an Enterprise Applications Engineer for Morgan Lewis and has more than 17 years of professional experience. Steve was previously a Principal Architect for RDA Corporation where he worked for over 13 years.
For the past 8 years, he has primarily focused on collaboration and business intelligence solutions using Microsoft technologies. Steve managed the internal BI Practice Group at RDA for several years. He was also heavily involved within RDA’s Collaboration/Search Practice Group.
He has authored and co-authored several books related to the subject of SharePoint Server 2010. Steve’s blog site can be found at www.SteveTheManMann.com.
Chuck Rivel is currently employed at RDA as a Principal Architect which involves scoping, designing, developing and delivering custom data warehouse projects for many different industries. He has over 14 years’ experience working with MS technologies and the last 8 years have been focused on DW\BI development. Currently, he is the BI Technical Lead for RDA, which is responsible for the technical direction of the BI team within RDA and for creating training programs for existing RDA employees to learn the BI toolset.
Ray Barley is a Principal Architect at RDA Corporation and has worked as a developer, analyst, project manager, architect, trainer and independent consultant. He has been focused on architecting and delivering Business Intelligence solutions since 2005. Ray helps to run the Baltimore SQL Server User group, is a frequent speaker at local user group meetings, and a frequent contributor to MSSQLTips.com.
Jim Pletscher is a Senior BI Consultant with RDA and has worked in the IT field for more than 15 years. Jim first began exploring OLAP technologies with SQL Server 2000, and has worked extensively with SQL Server 2005 and 2008, developing Reporting and Analysis solutions for numerous clients in a variety of industries over the past 8+ years. He is originally from Pennsylvania, and lives there now, but has also lived in Northern California as well.
Aneel Ismaily was born and raised in Karachi, Pakistan and then moved to the United States at the age of 18. Since then he has lived in Atlanta, GA. Aneel joined Georgia State University in 2001 and finished his BS degree from there in Computer Science. He is currently enrolled in a professional MBA program at Georgia State University and expecting to graduate in Dec, 2012. Aneel brings over eight years of experience to RDA in designing, developing, deploying, and supporting Business Intelligence Solutions. He currently holds Sr. Software Engineer Position with RDA. Prior to RDA, Aneel was employed at BCD Travel as a BI Solution Developer and previously as a Database Administrator. You can learn more about Aneel at http://www.linkedin.com/in/aismaily.
