Introduction

Business intelligence (BI) has been around for many more years than you might think. Decision support systems (DSS) and executive information systems (EIS) from the 1970s and 1980s, along with artificial intelligence (AI) systems from the 1990s, can now all be considered as part of what BI comprises today.

In brief, BI refers to the aggregation of all relevant business information, put together such that correlations and metrics may be monitored and analyzed, and the making of decisions based on trends and the results of the analyzed information.

In the early 2000s, when economic conditions became rough, companies began focusing on their businesses more closely. This produced the essential need for BI solutions. Many organizations began building data warehouses using SQL Server 2000 and Analysis Services 2000. Microsoft recognized this need and approached the development of their SQL Server 2005 product with BI in mind.

The release of SQL Server 2005 brought BI to the forefront of Microsoft solutions. Although this took care of the data and reporting components, it did not address the core monitoring and planning aspects of a BI solution. To fill this gap, Microsoft began developing Office PerformancePoint Server 2007 (PPS) in conjunction with the acquisition of ProClarity. At the same time, Microsoft Office SharePoint Server 2007 (MOSS 2007) was being promoted as the main delivery mechanism for BI solutions using Microsoft technologies, and was at this point being referred to as the Microsoft BI stack. The Microsoft BI stack fulfilled the business needs, but the solution was made up of various separate components. Enter Office 2010.

With the releases of SharePoint Server 2010 and SQL Server 2008 R2, the convergence of the BI stack is more evident. Once-separated components (MOSS and PPS) are now combined within the same architecture, and tighter integration now exists between SharePoint and Reporting Services and Analysis Services.

The Office 2010 platform forces technological change within skillsets and roles. The BI specialists who may be considered SQL Server experts now need to understand SharePoint and its role within a BI solution. Conversely, SharePoint experts and developers need to be able to use SharePoint as a delivery mechanism for BI solutions.

Previously, if you needed to understand BI solutions using SQL Server, you referred to a SQL Server or data warehousing publication. If you needed to understand SharePoint, you referred to SharePoint-related material. With the tighter integration of the Microsoft BI Stack, we believe there is a need to combine the components of BI and SharePoint into one reference book for this integration. This book bridges the gap between the two worlds of database technology and portal solutions.

How This Book Is Organized

• Part I, “Getting Started,” explains how to prep SharePoint 2010 for the deployment of BI solutions. The Business Intelligence Center is described, and you learn about Excel Services.

• Part II, “Reporting Services,” focuses on the integration and use of Reporting Services within SharePoint, including uses of the Report Viewer web part.

• Part III, “PerformancePoint Services,” discusses the configuration and development of PPS (which is now part of SharePoint 2010 as an application service), along with security aspects of PPS.

• Part IV, “PowerPivot,” explains how Analysis Services and SharePoint 2010 have been integrated to produce a mechanism for business users to publish analytical information via Excel.

• Part V, “Visio Services,” explores how the visualization of BI data is becoming as important as the information it provides. Leveraging Visio Services within SharePoint adds an interesting component to BI solutions.

• Part VI, “End-to-End Solutions,” covers how all the components and services all fit together (in contrast to earlier chapters where these components and services are examined from discrete perspectives). This part provides two scenario-based end-to-end solutions that explain how to construct a BI solution.

• Part VII, “Troubleshooting,” outlines resolutions to common problems so that you do not have to search the Internet for guidance with regard to currently known issues and errors.

What This Book Does Not Cover

This book explains the integration and uses of SQL Server 2008 R2 components with SharePoint Server 2010. It does not discuss the processes of building a data warehouse, dimensional model, or Analysis Services cube. These are separate core topics that need to be explored on their own.

Some BI specialists consider Business Connectivity Services (BCS) an extension of business solutions. Although BCS does provide access to external data sources (which could include data warehouses or central repositories), this book focuses more on the commonality of SQL Server components in SharePoint 2010. To really understand BCS and the various capabilities, you should refer to publications that deal specifically with that subject; general SharePoint 2010 books just scratch the surface.

Another extension possible through the leveraging of BI data within SharePoint is InfoPath 2010. By creating forms with InfoPath 2010, you can leverage data from BCS, external data sources via Web Services, or from direct database connections. However, this subject area is beyond the scope of this book. To explore the possibilities of InfoPath 2010 within a SharePoint environment, refer to InfoPath with SharePoint 2010 How-To (Sams, 2010).

Expanding Your Knowledge

We hope that this book provides a solid foundation of understanding about potential BI solutions via SharePoint and SQL Server and that you find everything you need here. However, business requirements and changing needs usually require custom solutions that cannot all be possibly documented in one location.

So, to stay current and find additional answers if needed, consider the following resources a good investment of your time and attention:

• Microsoft BI site: http://www.microsoft.com/bi/

• Microsoft Office Developer Center: http://msdn.microsoft.com/en-us/office/default.aspx

• Microsoft SharePoint 2010 site: http://sharepoint.microsoft.com/

• RDA BI/SQL Server Practice Group Blog: http://bisqlserver.rdacorp.com/