9780672335518

The only comprehensive, practical guide to delivering SharePoint 2010 Business Intelligence solutions. Thoroughly covers both BI and SharePoint 2010: a one-stop guide covering every key task. Reaches a fast-growing market: SharePoint is emerging as the preferred presentation and deployment mechanism for Microsoft-based BI solutions. By a world-class team of SharePoint BI consultants. Ideal for both SQL Server and SharePoint technical professionals. SharePoint 2010 is rapidly emerging as the preferred presentation and deployment system for Business Intelligence (BI) solutions that use the Microsoft technology stack. This is a complete guide to both SharePoint 2010 and BI. This book reflects the unsurpassed real-world experience of five expert consultants at RDA, a Microsoft Gold Partner specializing in delivering advanced BI solutions with SharePoint. The authors offer practical, how-to guidance for every key task involved in building, configuring, and deploying BI solutions within a SharePoint environment, delivering crucial information that can't be found in any other book. They bridge the gap between SharePoint 2010 and SQL Server 2008, carefully guiding technical professionals on utilizing BI data stored in SQL Server databases. Both a hands-on tutorial and a thorough reference, SharePoint 2010 BI Unleashed will be an indispensable resource for every BI developer, implementer, manager, or integrator working with Microsoft technologies.

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.

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

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Amazon no longer offers textbook rentals. We do!

Microsoft SharePoint 2010 Business Intelligence Unleashed

Microsoft SharePoint 2010 Business Intelligence Unleashed

0672335514

Summary

Author Biography

Table of Contents

Supplemental Materials

Write a Review