- Overview
- By Industry
- Banking
- Business and Professional Markets
- Capital Markets
- Commercial Real Estate
- Federal/Public Sector
- Healthcare and Pharmaceuticals
- Independent Software Vendors
- Insurance
- Lending
- Manufacturing
- Media/Entertainment
- Transportation/Distribution/Logistics
- Utilities/Telco
- By Area of Expertise
- Business Intelligence
- Custom .NET Application Development
- Enterprise Application Integration
- Workflow and Collaboration
Case Studies
Dashboard Solution Improves Reporting Capabilities for Benefits Processor
Project Summary and Benefits |
|---|
RDA helped this client establish an information system in which key users within the organization can view, analyze, and report from data across multiple systems in the enterprise. The solution provides executives with the right data to enhance business management through the use of identified key performance indicators, metrics, and reporting in the form of a balanced executive scorecard. |
About Our Client
RDA's client is a system and services company dedicated exclusively to health benefits processing for BlueCross and BlueShield Plans nationwide.
Background
Over the years, our client had engaged in several projects to develop data reporting solutions on various metrics across the organization, from "Rate of Employee Retention" to "Cost per Claim Processed." The solutions were all developed separately and existed in separate applications. They were primarily developed in traditional ASP code.
The company realized that they could have better visibility into their business if they unified these solutions. Further, they wanted to have the ability to analyze the data further and drill into the details if necessary. Their end goal was to provide executives with the right data to enhance business management through the use of identified Key Performance Indicators (KPIs), metrics, and reporting in the form of a balanced executive scorecard.
Solution Detail
Our client had previously standardized their intranet on Microsoft Office SharePoint Server (MOSS) 2007, thus Microsoft SQL Server and PerformancePoint 2007 were a natural fit to meet the business objectives for this project. However, they also had several specific requirements that needed to be addressed:
- The data needed for the KPIs was currently located in several different systems. This included separate online databases, Excel files, CSV files, flat files uploaded to FTP sites, and data straight out of SharePoint.
- The client was concerned that their users would not be comfortable with PerformancePoint's Dashboard Designer and wanted to allow users to manage KPI targets and thresholds from within SharePoint instead.
- Data had to be secured at the row level. For example, senior executives would be able to see all data, but lower-level employees might only be able to see a colored indicator (red, yellow, or green), and not the actual data.
- They wanted to eventually open up the balanced scorecard to key customers.
RDA implemented a Balanced Scorecard utilizing a combination of MOSS 2007, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), and PerformancePoint 2007. Enhancements were developed for SharePoint to allow for management functionality. SSIS was used to merge additional external data into a reporting datamart. PerformancePoint 2007 was used to create the reports and dashboard, and surface them into the SharePoint environment.
Benefits
With the resulting solution, our client has more visibility into their business data than ever before and is already using it to drive business decisions and better support their customers. Further, the solution allowed several legacy applications to be retired, lowering their overall administrative and operational overhead.
The solution gives the business and its customers and partners a new level of visibility into their data, and a greater ability to see and respond to business problems early. The client previously employed a manual process to gather and compile the metric data, which was time consuming, costly and could only be completed quarterly. With the new solution, they see the monthly metrics almost as soon as they become available. The increased visibility into the business is also helping to set a new level of trust with customers and partners.
Technically Speaking
The solution incorporates the following features, organized here by technology type:
SharePoint Server
- A custom "KPI Administration" site definition was developed for SharePoint. This serves as a location for editing metric data and KPI targets and thresholds for a set of KPIs. Usually, one site per business unit was created for this purpose.
- A custom SharePoint field type was created to allow KPI targets and thresholds as well as an effective date range to be entered via a SharePoint list. This also allows multiple KPIs to be maintained and sent through an approval process within a single SharePoint list item.
- Custom SharePoint list definitions were created that allow items to be related to a specific month/year or just a year. These are used when entering metric data from SharePoint, as all data has to be tied to a time dimension.
- Custom event list receivers and workflows were developed to carry the metric and KPI data to the reporting datamart following an optional approval process. Workflows were developed in Visual Studio and utilized InfoPath Forms Services forms for editing association data and tasks.
SQL Server and PerformancePoint Server
- SSIS packages were created for each KPI to handle ETL of the data into the reporting datamart from the varying sources.
- Code was implemented to allow Active Directory security to be polled during calls to the database in order to filter data at the row level.
- A custom master page was created to remove the Quick Launch on PerformancePoint dashboard pages.
- Several dozen reports were created to visualize the metrics and present the scorecard data.
- Dashboard pages and scorecards were tied back to "Strategy Maps" via icons embedded in the scorecards.
