What every administrator needs to know
Since its inception, Microsoft SharePoint has used SQL Server to store its content, configuration, and application service data. SharePoint simply won't function without SQL Server. SQL Server also has a growing reliance on SharePoint, especially in the area of business intelligence (BI). Whether you're a SQL Server or SharePoint administrator, you need to be familiar with the important points regarding the products' integration and dependencies.
As Figure 1 shows, the components necessary for delivering enterprise and self-service BI include PerformancePoint Services, Excel Services, SQL Server Reporting Services (SSRS), Power View, and PowerPivot for SharePoint. Table 1 provides a quick rundown of them. I'll walk you through each of these components in more depth so that you have a full understanding of them.
Figure 1: Highlighting the components involved in enterprise and self-service BI
PerformancePoint Services and Excel Services
Many organizations model business processes (e.g., sales, forecasting) with SQL Server Analysis Services (SSAS) so that end users can easily explore the data with tools like Microsoft Excel PivotTables. In the traditional multidimensional mode, SSAS has no dependency on SharePoint. The same can be said for the new tabular mode introduced in SSAS 2012. With SSAS and an Excel front end, an organization can implement an effective BI solution. However, many organizations will likely want to use SharePoint to provide additional front ends to SSAS. Two technologies built directly into SharePoint 2010 Enterprise -- PerformancePoint Services and Excel Services -- can provide these additional front ends.
PerformancePoint Services. Because BI dashboards and scorecards have become very popular, all the major BI vendors have added dashboard and scorecard features to their products. A scorecard usually consists of multiple Key Performance Indicators (KPIs), often grouped into different objective areas. A typical dashboard is a collection of one or more scorecards and supporting details and reports, which are often linked together through predefined connections or common filters.
Microsoft initially provided dashboards and scorecards through a product called Microsoft Office Business Scorecard Manager (BSM) 2005. In 2006, Microsoft acquired ProClarity, which was the most popular partner front end for SSAS at the time. BSM and ProClarity were folded into a standalone product named PerformancePoint Server 2007. PPS 2007 offered three major capabilities: monitoring (dashboards and scorecards), analytics (interactive grids and charts), and planning (forecasting and budgeting). Although it was a standalone product, the PPS 2007 dashboards, scorecards, grids, and charts were almost always deployed to a SharePoint 2007 environment.
In SharePoint 2010 Enterprise, PPS is now the built-in PerformancePoint Services. A PerformancePoint Services scorecard can include KPIs that leverage values from multiple sources, but the PerformancePoint Services analytic grids and charts only work on top of an SSAS cube or PowerPivot workbook deployed to a SharePoint library. (Note that the planning capabilities in PPS 2007 are no longer available.)
Excel Services. Excel Services is a built-in service in the Enterprise edition of SharePoint 2010 and SharePoint 2007. It allows an entire Excel workbook or an Excel workbook component (e.g., chart, named range of cells) to be published to a SharePoint document library and then displayed in a browser. For example, in Figure 2, the right side of the dashboard is a named range from an Excel workbook. When a user clicks on a particular KPI in the scorecard on the left (or selects a different year in the Fiscal Year filter), the Excel workbook is refreshed to display the selected KPI's information. Note that Excel Services can display any workbook, regardless of whether it's connecting to an SSAS cube. What I really like about Excel Services is that it effectively turns Excel into a report authoring tool, decreasing the training time needed for end users to create and share their analyses with others.
Figure 2: Displaying a named range of cells from an Excel workbook in a dashboard
Scorecards and KPIs aren't limited to Excel Services and PerformancePoint Services. Multiple Microsoft products offer some form of support for them. For more information, see the sidebar "Scorecard and KPI Overload?"
SSRS and Power View
To understand the integration of SSRS and SharePoint, it's helpful to know a bit about SSRS's history. When SSRS was first released in 2004 as a SQL Server 2000 add-on, there was only one deployment mode (what's now called native mode) and no integration with SharePoint. The only option for displaying reports within SharePoint was the generic Page Viewer Web Part. This Web Part, which comes with any SharePoint installation, acts as an HTML frame to display web content from a specified URL.
In 2005, Microsoft provided the Report Explorer and Report Viewer SharePoint 2.0 Web Parts as part of SQL Server 2000 Reporting Services SP2. (Note that these Web Parts continue to be available in current releases and are still a viable option for customers with SSRS native mode deployments.) The Report Explorer Web Part is configured to display a list of folders and reports from a specified SSRS folder in a native mode deployment. It also passes the name of a selected report to the Report Viewer Web Part.
