Tim is a Senior Consultant at ThreeWill. He has 15 years of consulting experience designing and developing browser-based solutions using Microsoft technologies. Experience over the last 8 years has focused on the design and implementation of SharePoint Intranets, Extranets and Public Sites.
SharePoint 2010 BI Primer Introduction
In any organization there are business users who need information to understand current and historical trends so they can make appropriate business decisions. In a Financial role, this might be tracking Actual expenses to Forecasted expenses. In a Sales role, this could be tracking Forecasted sales to Actual sales with a need to see this information broken down by Year, Region, State and Sales Person. In a Services Management role, this might include tracking Actual and Forecasted performance metrics to ensure that Service Level Agreements (SLAs) with external customers are being met (or not) so that appropriate adjustments can be made to avoid financial penalties or an unsatisfied customer. This last scenario, tracking of key performance metrics, is one that ThreeWill was recently engaged to develop leveraging features of both PerformancePoint (PP) and SQL Server Reporting Services (SSRS). The initial engagement consisted of creating a prototype that was demonstrated within a multi-million dollar Request For Proposal (RFP) meeting. After winning the RFP, the client initiated a second engagement with ThreeWill to create a Production version of the earlier prototype that expanded the functionality to include a greater number of Service offerings and was more scalable and reliable.
In this blog and in subsequent blogs, we would like to share the Business Intelligence functionality that we leveraged in the development of the prototype and Production instance as well as “lessons learned” as we moved from a prototype to a fully functional Production system. In this first blog, we would like to provide a brief overview of the functionality available in PerformancePoint and SQL Server Reporting Services that we leveraged. In the subsequent blogs, we will drill-down into more of the nitty-gritty technical details and discuss how we “beefed-up” some of the native PP and SSRS presentation by leveraging JQuery and Ajax. Also, we will discuss how we secured the application and data leveraging Active Directory, SharePoint Groups, Kerberos and SQL Server Analysis Server Roles (SSAS).
What is PerformancePoint?
PerformancePoint is a set of related objects that can be leveraged individually or together to provide meaningful views of data. These views of data can take the form of Key Performance Indicators (KPIs) where actual values are compared against a target or goal value with a corresponding visual indicator to give a quick indication of the performance. Or, these views of data can be more complex where drilling up and down in the data through dates (Year, Quarter, Month, Week) and other dimension hierarchies (Country, State, County, City) is possible using the Analytic controls in PerformancePoint. KPIs and analytic charts and grids are a few of the objects included in PerformancePoint. Other objects include dashboards, filters, scorecards as well as a very cool Silverlight control called the Decomposition Tree that integrates with the analytic controls to provide a “left to right” drill-down capability. To work with and organize these objects in a meaningful way, a client application called the PerformancePoint Dashboard Designer is used. PerformancePoint Designer reads and writes PerformancePoint objects to/from a document library that is hosted in a SharePoint site. Filters, scorecards, KPIs, analytic grids and charts are all available to be hosted as web parts on SharePoint web part pages so they can be mixed and matched as needed or on the special PerformancePoint dashboard pages that come with tab-like navigation. PerformancePoint objects can display data from a variety of data sources including Analysis Services, Excel Services, Excel workbooks, SharePoint list data and SQL Server tables and views. During the initial prototype development, we primarily leveraged a SharePoint list data source so we could quickly create a custom SharePoint list and add data using the datasheet view. However, we did eventually create an Analysis Services data source so that we could demonstrate some of the more visually compelling functionality available in the analytic chart and associated Decomposition Tree.
What is SQL Server Reporting Services?
SQL Server Reporting Services (SSRS) has been around for a while but the SSRS add-on to SharePoint surfaced for Windows SharePoint Services v3 and MOSS 2007. In a nutshell, the SSRS add-on provides, in SharePoint vernacular, custom content types that can be applied to SharePoint document libraries. These custom content types allow SSRS data sources, data models, and reports to be created and managed in SharePoint leveraging the security infrastructure that is included out of box. If a user has access to update the document library, they can add new or update existing SSRS reports from within SharePoint using the click-once ReportBuilder application that comes bundled with the SSRS add-on for SharePoint. By leveraging SSRS in SharePoint, reports can be easily previewed, printed and even exported to excel and other formats using the included Report Viewer.
While there is a lot of additional capabilities provided in SSRS, such as scheduling reports, the primary use of SSRS in our scenario was to provide access to detail data, allow exporting of the data, and to provide “ad hoc” report development by end-users using the ReportBuilder application.
PerformancePoint and SQL Server Reporting Services
As previously mentioned, we needed to build a solution that would provide Performance Metrics for the variety of services that our client provided to it’s customers. The target audience for our client solution included two sets of users. One set of users that we’ll call the Committee Users could see summary data for each of our services as well as the service data broken out by regions. However, the detail data that rolled up to create regional and overall totals was not accessible by the committee users. Our second set of users that we’ll call Regional Users could only see data for their particular region. They could see this data summed up for each service as well as all of the details.
To achieve this level of security, we were able to leverage SSAS roles. While I won’t go into detail about the creation, configuration and assignment of SSAS Roles in this blog, it is significant to point out that securing the data within SSAS allowed us to share KPIs and other objects among the two user groups knowing that the data security would only display the data appropriate for the currently logged-in SharePoint user. So users from all of the regions could log into the same region dashboard and would only see data for their region.
To provide a quick view of all of the Key Performance Indicators defined for each Service offering, PerformancePoint KPIs and Scorecards were the primary focus of our effort. KPIs were created for each individual performance metric and actual and target values were mapped for both current month and year to date. The KPIs were grouped under the appropriate Service category and displayed in a PerformancePoint scorecard. For each of the performance metrics, a green, yellow or red icon was displayed to indicate how well the actual performance values mapped to the targeted performance values that were agreed upon in the SLA.
In addition to seeing Summary data, the Regional users needed the ability to view and analyze the detail transactional data. To provide visual views of the data that could be used for analysis, we leveraged the Analytic Chart. Analytic charts provide the real “coolness” factor with the ability to drill-down into the data based upon the attributes or dimensions defined in the data. In our case (as in most cases), a date dimension was defined so we were able to drill-down into the details by Year, Quarter, Month and Day. Analytic charts are flexible in that they can be presented as a bar chart, stacked bar chart, 100% stacked bar chart, pie chart or line chart. Analytic charts are built/configured within the PerformancePoint Designer application where the measures, dimensions or named sets can be configured using drag and drop. By right-clicking on an analytic chart, a number of options are made available including but not limited to drill-down, drill-up, sort, filter, pivot and the Decomposition Tree. The decomposition tree always seems to be a crowd favorite as it provides a nice visual way to drill up and down the hierarchy. The decomposition tree is built using Silverlight so Silverlight must be installed on the client for this option to be available. Otherwise, it will not be displayed.
Besides being able to drill-down into the data and do visual analysis, there was also a requirement to see the raw detail data and be able to export this data into Excel for further analysis. SQL Server Reporting Services (SSRS) was a good fit for this requirement as we were able to define parameterized reports to show the detail data in a viewer that was searchable, printable and exportable into Excel and other formats.
Many organizations own SharePoint and take advantage of the document management features that have been available since the first version of the product. However, SharePoint has continued to mature as both a product and a development platform, adding capabilities such as PerformancePoint and the SSRS integration that can be leveraged to solve business problems for both internal and external customers. My goal for this post was to provide a high-level view of a real-world problem and how we leveraged PerformancePoint and SSRS to solve that problem. In subsequent posts, we will share more detail about these features and some of the lessons learned as we integrated these features to provide a client solution.