SharePoint 2010 Business Intelligence Unleashed

Description

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

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