ECI Helps Modernize Legacy Financial Reporting System

Background
The Annual Financial Reporting System (AFRS) is OSHPD’s legacy mainframe application (COBOL) and database (DB2).  It is used primarily used for collecting annual financial data from California Hospitals and Long Term Care Facilities, then managing the data through the editing and auditing process, and through to completion.  The final data is used to create reports and fulfill requests for data (from healthcare entities, researchers, and the general public) about Hospital and LTC financials.

As the legacy mainframe applications and databases have aged, they have become difficult to manage and maintain.  Because of this, application functionality has not been significantly updated over the past few years due to the difficulties of finding personnel with legacy skill sets.  The need to make changes forced OSHPD to strategically decide whether to continue sustaining the existing legacy application, or to migrate to a more modern technology framework.

Data within the legacy DB2 database was de-normalized in a Page / Line / Column metaphor, making it difficult to use modern reporting and analysis tools to generate reports, data extracts, and integration into other OSHP analysis tools (Business Objects XI, SAS Analytics, MS Analysis Services, Informatica).

The Annual Financial Data Model project was a preliminary phase of a larger, multi phase project to build a new .NET Annual Financial Reporting system and retire the legacy AFR application and database.  The goal of the Annual Financial Data Model project was to migrate the past five years’ Annual Financial Report data to a modernized and normalized Microsoft SQL Server 2005 database to support advanced reporting and analysis capabilities (via Microsoft SQL Server Reporting and Analysis Services), while simultaneously providing a database infrastructure needed to initiate future projects for building new AFRS .NET applications.

Solution

The ECI solution consisted of the following components:

• NEW Annual Financial Database (SQL Server 2005)
  ECI modeled and developed a normalized relational database schema for both Hospital and Long Term Care (LTC) financial data using Microsoft SQL Server 2005 technology.
• Data Migration and Automated Data Synchronization Process
  ECI developed, tested and implemented an automated Data Migration and Data Synchronization solution as an ETL process deployed using Microsoft SQL Server 2005 Integration Services (SSIS).
• Custom Reports
  ECI re-developed the existing paper-based, line printer style Facsimile Reports as form-based, PDF files that can easily be produced, distributed and published to the OSHPD website using Microsoft SQL Server 2005 Reporting Services.
• Data Availability for Analysis
  ECI worked with OSHPD’s data management staff to develop views and queries to be consumed by Microsoft SQL Server 2005 Analysis Services for the purpose of constructing OLAP cubes for ad-hoc analysis of OSHPD data.

OSHPD realized many expected and unexpected benefits from the development of a new Annual Financial Database including:

• Data Accessibility
  Migrating the Annual Financial data into a normalized relational database schema, and into a modern technology framework (SQL Server 2005), has allowed consumers from various units within OSHPD to more easily access the Annual Financial data.
• Streamlined Reporting and Report Publishing
  ECI implemented automated / scheduled PDF report generation that OSHPD can now utilize to automatically publish Annual Financial Facsimile Reports to the OSHPD website without human intervention (improved service delivery of reports to end user customers).
• Ad-Hoc Data Access
  By deploying the new Annual Financial database within the Microsoft SQL Server 2005 framework, OSHPD now has the added benefit of using SQL Server Reporting Services layer to create and manage pre-defined reports, as well as providing internal users with the ability to generate ad-hoc, on demand reports without the need for programming or consulting staff.
• Ad-Hoc Data Analysis and Reporting
  By deploying the new Annual Financial database within the Microsoft SQL Server 2005 framework, OSHPD now has the added benefit of using SQL Server Analysis Services layer to create and manage OLAP cubes that can be used for sophisticated analysis of Annual Financial data, inclusion of Annual Financial data into other enterprise analysis projects, and help share annual financial data with a variety of other internal OSHPD projects (GIS mapping, enterprise data sharing, etc).
• Automated Data Management and Synchronization
  By deploying the ETL solution within the Microsoft SQL Server 2005 framework (SSIS), Oshpd now has a fully automated data management and synchronization bridge that will continue to refresh the new database with data from the legacy DB2 database while subsequent phases of the project are initiated (redevelopment of the application / redevelopment of the activity system and database).  This effectively extends the life of the DB2 database, and AFRS application, until subsequent project phases are complete – making it possible to retire the current mainframe AFRS application and database.
• Foundation Database For The Next-Generation Annual Financial Application (.Net)
  ECI carefully modeled and developed the new Annual Financial database as a framework upon which the subsequent project phases (new application) can be built – thus leveraging the work done to both provide a technology bridge for OSHPD during new application development, and serving as the transactional database for the new application.

ECI was responsible for managing and implementing the entire project from start to finish including:

• Project Management – development of a Project Management Plan and Project Schedule
• Requirements – gathering, analysis and documentation of all business and technical requirements
• Database Modeling – creation of both a logical and physical database model, based on analysis of the legacy DB2 mainframe database
• Database Development – creation of the new SQL Server 2005 database
• Data Integration - development of the ETL processes for migrating and synchronizing data from the legacy system including:
• current year’s data
• four years of additional historical data
• ongoing data synchronization of all changes
• Knowledge Transfer - hands on and classroom knowledge transfer sessions and training
• ERD - production and printing of an Entity Relationship Diagram
• System Documentation - development of a 266 page Solution Summary and Solution Support Plan document for use by OSHPD staff in managing the deployed solution

• Microsoft SQL Server 2005 Database
• Microsoft SQL Server 2005 Integration Services (SSIS)
• Microsoft SQL Server 2005 Reporting Services