Monitoring SQL Server and SharePoint BI Components

In "Delivering BI through SQL Server and SharePoint" (September 2012), I discussed the latest business intelligence (BI) components that Microsoft delivers through SQL Server 2012 and SharePoint 2010. Those BI components are highlighted in yellow in Figure 1.

 
Figure 1: BI Components Available Through SQL Server 2012 and SharePoint 2010

Now I'll discuss the logging capabilities that are available to monitor the BI components' performance and usage. Specifically, I'll discuss how to monitor:

Monitoring SSRS and Power View Usage

Administrators often want to know which SSRS reports are the most popular, which are the slowest, whether new reports are being viewed, and so forth. There's an easy way to get answers to these and similar questions. SSRS uses a database, referred to as the Report Server database, to store the following contents:

The report execution log data contains detailed information about every report request. This information is stored in a table and is made accessible through the ExecutionLog view. Note that depending on the SSRS version, there might be up to two additional versions of the view (ExecutionLog2 and ExecutionLog3) that reflect later product capabilities or fields with friendlier names.

Related: SharePoint Business Intelligence Resources: A modest aggregation

By default, log entries are kept for 60 days. Although these retention settings can be increased for historical reporting, I recommend that you periodically extract the log entries into a separate database. This approach will reduce query activity against the Report Server database and make it easier to consolidate execution information from multiple SSRS instances.

Several free solutions to extract and consolidate execution log data are available, including a Microsoft SQL Server Community Project, a CodePlex solution named SCRUBS, and a solution created by Rodney Landrum.

Microsoft SQL Server Community Project. This solution, which I wrote about in "SQL Server Reporting Services Questions Answered" (October 2009), includes database schema, a set of SSRS reports, and a SQL Server Integration Services (SSIS) package (RSReportExecutionLog_Update.dtsx) to extract data from the execution log and a few other tables. This project is based on SSRS 2005, but it has never been updated for later versions of SQL Server. It'll work for SSRS 2008, but it doesn't work against an SSRS 2008 R2 or SSRS 2012 instance. Although I've come across a few blog entries about how to update the original package and reports (e.g., Execution Log Sample Reports SSRS 2008 R2), you should consider using SCRUBS if you're using SSRS 2012 or SSRS 2008 R2.

SCRUBS. This solution is based on the original Microsoft sample projects and uses the 2008 Report Definition Language (RDL) format in its sample reports. Although SCRUBS is written to work with SSRS 2008 and SSRS 2008 R2, it also works on SSRS 2012 with a few minor changes.

SCRUBS works with SSRS running in either native mode or SharePoint integrated mode. I included copies of the SCRUBS reports, the database, and the SSIS package (including the fixes needed for SSRS 2012) in the downloadable code for this article.

To view the samples in SQL Server 2012, open the BIMonitoringProjects.sln solution in SQL Server Data Tools. Before viewing the sample reports, which start with Scrub_, you need to:

1. Attach the included SummitCloud_SCRUBS database to an existing SQL Server 2012 instance. The database files are located in the SummitCloud_SCRUBS_DBFiles.zip file.

2. Run the SCRUBS_SSRS_Log_Feed.dtsx SSIS package to populate the database.

3. Edit the SummitCloud_SCRUBS.rds connection settings in the ExecutionLog project.

As Figure 2 shows, the Scrub_Top Reports.rdl report shows a summary of the top 10 reports in terms of usage. Clicking any of the report names will open the detailed Scrub_Report Summary Generation Statistics.rdl report. Similarly, the Scrub_Report Execution Log.rdl report provides a summary of overall report activity for a specified period of time, along with a detailed list of report executions. Clicking a report name (or hourglass icon) will open a detailed report. Note that since Power View reports are a special type of SSRS report (.rdlx), Power View usage will also show up in these reports.

Figure 2: Sample Report Showing the Top 10 SSRS Reports by Usage 

For situations in which real-time monitoring is desired, I included three sample reports that will work directly against a Report Server database. These reports start with the prefix RealTime_.

Landrum solution. In "Consolidate Data on Executed SSRS Reports for Easy Querying" (January 2013), Rodney Landrum presents his own custom approach to extracting data from the execution logs. Like the other two solutions, Rodney uses an SSIS package to extract data into a custom schema.

Monitoring PowerPivot for SharePoint Usage

Just as SSRS reports can be used to monitor SSRS usage, PowerPivot reports can be used to monitor PowerPivot usage. Introduced in SQL Server 2008 R2, PowerPivot for SharePoint lets an end user publish and share Excel 2010 workbooks that contain PowerPivot models. Installed as a shared service application, PowerPivot for SharePoint takes advantages of the logging capabilities offered by SharePoint. SharePoint's Unified Logging Service allows the collection of usage data, and PowerPivot for SharePoint leverages this feature to have usage data loaded into its service application database. An internal PowerPivot workbook named PowerPivot Management Data.xlsx is built on top of this database to serve as a data model. If you want to peek behind the scenes, the PowerPivot Management Data.xlsx workbook is located in a document library named PowerPivot Management in the Central Administration site collection.

In order for the collection of usage data to occur, this functionality must be enabled for the SharePoint farm. When you install PowerPivot for SharePoint using the New Server option, this functionality is enabled automatically. Otherwise, you need to enable it manually. (For instructions on how to do so, see the sidebar "How to Manually Enable the Collection of SharePoint Usage Data.") By default, PowerPivot usage data is stored for 365 days. You can change this setting from the PowerPivot Service Application Page.

The PowerPivot Management Dashboard, which is accessible from the root level of the SharePoint Central Administration site, contains several Web Parts that display performance and usage information based on the PowerPivot Management Data.xlsx workbook. For example, performance metrics on query time, memory utilization, CPU utilization, and activity levels are available through the Infrastructure – Server Health Web Part, as shown in Figure 3. Similarly, a list of end-user workbooks that have been published (along with a count of users who have viewed the workbook, the latest workbook size, and cumulative query counts) is available through the Workbook Activity – List Web Part.

Figure 3: PowerPivot for SharePoint Performance Metrics in the PowerPivot Management Dashboard 

If necessary, you can customize the PowerPivot Management Dashboard to provide additional views. For example, I recently had a customer who was very concerned about the data sources that end users leveraged in their workbooks. Data source information is collected as part of the usage data, so I built an Excel pivot table using PowerPivot Management Data.xlsx as the data source. This pivot table lists the data sources grouped by data provider. I then uploaded this workbook to the Central Administration site, opened the dashboard page using the SharePoint page editor, and added a Web Part to the dashboard. You can see this Web Part, which is named Data Sources Used, in Figure 3. For information on how to customize the PowerPivot Management Dashboard, see the resource listed in the Learning Path.

There's an important point to understand when making sense of the usage data. In terms of query activity, these numbers don't get calculated based on workbook views but rather on connection requests to the PowerPivot data in the workbook. As I discussed in "Introducing PowerPivot for SharePoint" (July 2011), when a user opens a PowerPivot workbook in a SharePoint document library, PowerPivot for SharePoint typically isn't involved. It isn't until the user clicks a slicer, selects a filter, or refreshes the workbook connection that PowerPivot for SharePoint is invoked. But because these types of interactions can trigger multiple connection events, the query numbers in the PowerPivot Management Dashboard will likely be much higher than the number of times a workbook was viewed. And if a workbook is being used as a data source for other technologies (e.g., Power View), the query numbers will be even higher.

Monitoring PPS and Excel Services Usage

To monitor both PPS and Excel Services, you need to enable SharePoint's collection of usage data (see the sidebar "How to Manually Enable the Collection of SharePoint Usage Data"). However, reporting PPS performance and usage is a bit tricky. PPS objects (e.g., scorecards, reports, dashboards) are stored in a SharePoint list, but the usage and performance metrics for individual list items aren't tracked and uploaded into the SharePoint logging database in a meaningful way. With this in mind, the best way to understand PPS usage is to monitor the usage of SharePoint pages that include PPS content. In your environment, you might choose to manually identify those SharePoint web pages with PPS content, then query the logging database for these specific pages.

To automate the page identification, you can use the SharePoint APIs to iterate through all the pages in a site collection and look for PPS Web Part references. In my sample WSS_LoggingReports code, I include a report named PPSUsageSummary2.rdl that queries the RequestUsage view in the logging database. This query identifies SharePoint web pages that also show up in the query string of PPS-specific service calls. Based on my testing, this approach seems fairly accurate in terms of identifying and aggregating PPS usage. Figure 4 shows a sample report.

Figure 4: Sample Report Showing PPS Performance and Usage 

Like PPS, Excel Services is a bit tricky to monitor. And like PPS, I decided to monitor Excel Services at the page level. When a user opens a workbook in a SharePoint site where Excel Services is enabled, the workbook is opened in the browser. However, a common page is used to open the workbook, with the workbook name passed in as a parameter (e.g., http://MySite//_layouts/xlviewer.aspx?id=/MySite/Reports/VolumeAnalysis.xlsx&Source=http%3A%2F%MySite%2FReports%2FForms%2Fcurrent%2Easpx&DefaultItemOpen=1). So, when querying the logging database, you can search for rows in the RequestUsage view where the xlviewer.aspx page is in the document path, then parse the QueryString column to determine the actual workbook that was viewed. In the article's sample code, I included a sample report (ExcelServicesUsage.rdl) that performs this query.

Note that this approach doesn't identify web pages that use the Excel Web Access Web Part to embed Excel workbook content. If you're using this Web Part, you need to identify and explicitly monitor these types of pages.

A Practical Approach to Monitoring

I hope you have a better understanding of how to monitor the performance and usage of the BI components that are delivered through SQL Server 2012 and SharePoint 2010. For some of these components, you need to take a pragmatic approach to monitoring. My long-term goal is to build a single repository (and set of reports) to provide a unified view of performance and usage across all of these components.

Learning Path

SQL Server Pro Resources

To learn more about the BI components available through SQL Server 2012 and SharePoint 2010:

Business Intelligence landing page

"Delivering BI through SQL Server and SharePoint"

"Introducing Microsoft Power View"

"Introducing PowerPivot for SharePoint"

"What's New in Microsoft SQL Server 2012 PowerPivot"

Microsoft Resources

To learn more about monitoring the BI components:

"Chapter 3: Monitoring SharePoint 2010 (Real World SharePoint 2010)"

"Customizing the PowerPivot Management Dashboard"

"PowerPivot Usage Data Collection"

SCRUBS - Reporting Services Execution Log Scrubber