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.
In SQL Server 2005 SP2, the SharePoint integrated mode was introduced in SSRS to offer a tighter level of integration with SharePoint. The SharePoint integrated mode offered several potential benefits, including:
- The ability to store reports in a SharePoint document library. SharePoint document libraries support capabilities such as versioning, check-in/check-out, and additional columns to describe or categorize reports -- capabilities that SSRS's native mode lacks.
- The ability to configure user authentication and authorization in one place. In SharePoint integrated mode, SSRS doesn't have its own users and roles. Instead, SharePoint users and roles are used to govern report access and permissions.
- A new document library extension for report subscriptions. Report subscriptions let you deliver reports directly to a document library, which is handy if you need to keep a history of rendered reports in one location. Note that in SSRS 2005, SharePoint integrated mode supported only user-defined, and not data-driven, subscriptions. SSRS 2008 introduced support for data-driven subscriptions.
In SQL Server 2005 SP2, SharePoint integrated mode had a few potential drawbacks. Some SSRS native mode features weren't available, such as My Reports and Linked Reports. And from an administrative standpoint, SSRS still had to be configured and maintained as a separate service. For example, a scale-out deployment of SSRS couldn't take advantage of the automatic scale-out architecture available to the built-in SharePoint services.
The key drawback, however, was performance. In SharePoint integrated mode, SSRS was still a standalone service, which had two ramifications. First, the report server still had to provide data processing, rendering, and delivery capabilities. Second, there was additional communication required between the SharePoint object model and SSRS for each report request.
In late 2010, the SQL Server Customer Advisory Team (CAT) did a thorough test of both SSRS 2008 and SSRS 2008 R2. In the "Reporting Services Performance in SharePoint Integrated Mode in SQL Server 2008 R2 " technical note, it reported, "There is a measurable amount of additional overhead associated with SharePoint integrated mode that impacts report performance (between 250 milliseconds and 500 milliseconds of additional overhead to view the first page of any report)." Some customers noted an even higher overhead between native mode and SharePoint integrated mode.
Let's now fast forward to 2012. In SSRS 2012, the SharePoint integrated mode is re-architected to be implemented as a SharePoint 2010 shared service. You can think of a shared service as a component of SharePoint that does work, such as the Search and User Profile services. In terms of performance, this means that the additional communication overhead is gone, as is the performance hit. A SharePoint integrated mode deployment in SSRS 2012 is around 30 percent to 60 percent faster than that in SSRS 2008 R2 and at parity with a native mode deployment. There are also other benefits to the new shared service architecture:
- You can use the SharePoint Central Administration console to configure the SSRS SharePoint integrated mode.
- SSRS can use SharePoint's scale-out and load-balancing functionality.
- SSRS supports claims-based authentication.
- SSRS supports SharePoint backup and recovery and end-to-end SharePoint logging through the Unified Logging Service (ULS).
In addition, Microsoft decided to provide some new capabilities in SSRS 2012 that are available only through SharePoint integrated mode. One of those capabilities is data alerts. As Figure 3 shows, end users can create a schedule and set of rules for being notified when data in a report changes. The other capability is Power View, a new data visualization and exploration technology that I wrote about in "Introducing Microsoft Power View" (June 2012).
Figure 3: Creating a data alert
Data alerts and Power View are only available in the Business Intelligence edition or Enterprise edition of SQL Server 2012. To find out more information about them, see "What's New (Reporting Services)" in SQL Server 2012 Books Online (BOL).
PowerPivot for SharePoint
Following a long period of acquisitions and consolidation, the BI market has seen a resurgence of pure-play vendors in the past several years. Often, these solutions let business users bypass traditional IT tools and processes to gather, store, model, analyze, and share information. I know several IT professionals who have discovered these solutions in their organization only after they've been purchased by a business unit. For this reason, I recommend that you get well acquainted with what SQL Server, SharePoint, and Office can provide in terms of self-service BI. It's an opportunity for you to become a hero to your business users by leveraging products that they don't need to purchase and are already familiar with.
Specifically, Microsoft delivers what it calls "managed self-service" BI through its PowerPivot offerings for Office and SharePoint. The PowerPivot for Excel 2010 add-in lets business users gather, store, model, and analyze data from multiple sources. Behind the scenes, users are actually building an OLAP cube that runs in-memory inside of the Excel process. (This cube is saved as part of the Excel workbook.)
PowerPivot for SharePoint 2010, first introduced in SQL Server 2008 R2, is an instance of SSAS integrated into SharePoint. PowerPivot for SharePoint is installed and configured as a SharePoint shared service. When a user publishes an Excel workbook that contains a PowerPivot model, PowerPivot for SharePoint (along with Excel Services) makes it possible for other users to view and interact with the workbook through a browser. PowerPivot for SharePoint also lets other technologies (e.g., SSRS, Power View) connect to the workbook. Note that PowerPivot for SharePoint is only available in the Business Intelligence and Enterprise editions of SQL Server 2012 and in the Enterprise edition of SQL Server 2008 R2.
I only scratched the surface of the capabilities in PowerPivot for Excel 2010 and PowerPivot for SharePoint 2010. For more information about these tools, see the articles listed in the Learning Path.
BI Shared Service Deployment
Given the BI capabilities you can gain, I hope that you're considering setting up a SharePoint farm for BI. Even if you already have a SharePoint farm, I recommend that you set up a separate one dedicated to BI, which is referred to as a BI Shared Service deployment. This recommendation might seem counterintuitive. After all, if SharePoint provides BI capabilities (and SQL Server integrates itself into SharePoint), why not enable these capabilities directly within existing farms? Won't a separate farm negate the integration benefits?
At first glance, it seems that a separate farm increases complexity and provides no benefits. A new farm involves separate servers, which means additional monitoring, backups, user permissions to manage, and so on. Plus, the integration of BI content into existing site collections is relegated to links and frames because none of the BI capabilities are cross-farm services. (If you're unfamiliar with what a cross-farm service is, see the sidebar "SharePointTerminology 101.") With that said, I've spent many years helping customers evaluate and adopt Microsoft's BI platform. I've seen several BI initiatives stall due to concerns over:
- Edition mismatch. The existing SharePoint farms run on SharePoint 2007 or the SharePoint team plans to migrate from SharePoint 2007 to SharePoint 2010 "sometime next year."
- Workload conflict. The SharePoint team members are typically wary of placing an additional workload on the farm, especially a workload they don't understand.
- Non-SharePoint integration. The SharePoint team members are concerned that many of the BI services aren't built-in SharePoint capabilities but instead are SQL Server services.
Setting up a separate BI farm can alleviate these concerns and allow BI capabilities to be rolled out or upgraded independent of other SharePoint workloads. SQL Server and SharePoint administrators can work together to become familiar with these new BI services without jeopardizing the performance and availability of existing SharePoint functionality and services. The HP Business Decision Appliance is a great way to provide a smaller BI Shared Service deployment that uses a single server.
Tied at the Hip
Clearly, SharePoint is "tied at the hip" to SQL Server for the storage of its configuration, content, and service data. The same can now be said of SQL Server's reliance on SharePoint for exposing its BI capabilities. SQL Server and SharePoint administrators will be well served to learn more about these dependencies to successfully deliver BI in their organizations.
To learn more about Power View and PowerPivot:
"Introducing Microsoft Power View"
"Introducing PowerPivot for SharePoint"
