Architecture Review and Remediation Strategy
The Problem
Blue Care Network (BCN) of Michigan had migrated their healthcare information to a new online transaction system and had created a reporting sub-system to extract information that fed over 600 reports. The online system was performing adequately but their reporting system was struggling. Data extraction was slow and often overran the available nightly batch window. Reports performed poorly and were often inaccurate and inconsistent. Users were complaining about the reports and about the difficulty of doing ad-hoc analysis of the data.
BCN realized that they needed an experienced development team come in and assess the architecture and design of the reporting system. They turned to the Jonah Group for assistance. The Jonah Group was asked to review the problems being encountered by the users, assess the inefficiencies in the system, and to devise solutions that would improve the situation both in the short term and for the anticipated lifespan of the system.
Our Responsibilities
- Conduct interviews with system sponsors, users, designers and developers
- Catalogue the components of the system and identify the items contributing to processing inefficiencies
- Review samples of different report types for performance, accuracy, consistency
- Review the underlying data model and its suitability for reporting and ad-hoc analysis
- Review the batch processing steps and programs
- Create a detailed report of the results of the architecture review
- Devise recommendations to improve system performance and reliability in the short term
- Design a migration strategy for improving the system architecture over the lifetime of the system
- Present the review findings to development staff, business users, and senior executives in a series of short seminars
- Perform the remediation
The Solution
The Jonah Group allocated a senior data warehousing expert and two senior reporting specialists to the project to perform the review. The team began by collecting system architecture and design documentation and devising a list of questions covering the areas of: Performance, Capacity, Scalability, Reliability, Availability, Robustness, Accuracy, Maintainability, Extensibility, Reusability, Manageability, Usability and Security. These questions were distributed to executives, users, managers, architects, developers and testers and over several weeks a series of interviews were conducted to ascertain the details of the system.
The team also collected statistics on reports and batch jobs and participated in compiling a list of the poorest performing components of the system. From this list a number of reports and batch jobs were selected for in depth analysis.
As a result of the interviews and analysis, The Jonah Group was able to deliver an extensive report that detailed the existing system architecture, design and implementation, along with more than 40 recommendations organized into areas of data model design, report design and implementation, batch framework design and processing, development and promotion process, operations management and system documentation. The cost, effort, and complexity of each recommendation was estimated, and an impact and benefit analysis was supplied. Recommendations were classified as either short term operational benefits or strategic long term initiatives.
The Jonah Group delivered the report along with several findings presentations, each customized to specific audiences within the company.
Technologies
- Sybase 12.5
- BusinessObjects Data Integrator 6.5
- BusinessObjects Designer 6.5
- Business Objects Full Client / ZABO / WEBI 6.5
- Chilli Source DDS-Pro 2.21 Data Modelling Tool
- Brio / SQR 6.2.1
Unique Challenges, Solved.
- The Jonah Group helped the client to recognize that their reporting subsystem design had lacked an overall architecture strategy. The data model had been designed by several disparate groups and the underlying tables were often targeted at specific reports with no conformity as to the purposes of the tables or the business meaning of the table attributes.
- The Jonah Group demonstrated that many of the report performance issues were a result of attempting to run reporting queries off of a data model that was designed more for OLTP rather than for reporting.
- Jonah showed how duplication of data among many tables in many different subject areas and lack of a strategy for identifying changed data led to an overloaded batch schedule.
- Jonah showed how migrating to a proper data warehouse design could help consolidate common information, reduce inconsistency, improve batch efficiency through changed data capture and improve report performance through report-oriented data model design (star schemas, aggregation tables, widespread indexing).
- Identified and implemented some specific short term report fixes to improve the performance of the slowest reports in the system.
- Implemented some key changes to the reporting application layer to improve the applicability of the system to ad-hoc analysis.
- Designed a subsystem of control totals and discrepancy reports to improve the verifiability of the data in the system.
- Implemented fixes to crucial financial reports to correct inaccuracies in the reports.
- Proposed a long term strategy for moving segments of the system to a standard data warehouse design and delivered a detailed data model, component design and project plan for migrating a crucial segment covering the billing and finance subject areas of the system.
Feedback
"Blue Care Network of Michigan created an Enhanced Reporting Architecture that reduced the number of corporate reports from 7,000 to 600. The Jonah Group's data warehousing and reporting experts helped us create a road map for moving the system to a dimensional model using Business Objects, Brio, and Sybase.""The Jonah Group acted as a trusted advisor throughout the engagement and worked with other vendors to develop a process for controlling and promoting code components. They reduced the run-time of our largest reports by 50% by redesigning the database queries. We've been very happy with the service we've received from The Jonah Group, and would recommend them to companies seeking help with their technology challenges."
Janet P. Macqueen
Vice President and Chief Information Officer, Blue Care Network of Michigan
- Questions? Ready to move your project forward? We'd be happy to discuss a solution for your needs. Contact Us to find out more.
