4.4 Jonah Experience Prev

Health Care Data Warehouse

Blue Care Network (BCN) of Michigan had a large data extraction and reporting system that was built using non-standard techniques and tools. Much of the ETL code was developed in SQL and data was duplicated and modified across many different tables. This made it difficult to maintain the system and balance reports across different subject areas. The system performed poorly and often missed its batch processing windows. Some parts of the warehouse were months out of date. The system implemented few control totals or balances so the accuracy of the data was not verified automatically. The analytic data model was in normal form, which made ad-hoc queries inefficient.

The Challenge

Jonah Group was engaged to design and build a major data warehouse for BCN.

Our Responsibilities

  • Project planning and cost estimating
  • Management of a team of 14 developers
  • Collection and management of requirements for the entire system
  • Data Model Design for the analytic data store (ADS)
  • Architecture Design of the end-to-end solution
  • Technical design of the ETL programs and ADS
  • Development of the ADS and ETL programs
  • Development of changed data capture programs
  • Business Objects universe development
  • Development of automated build and deployment scripts
  • Development of automated unit tests
  • Performance Tuning
  • System Integration Testing
  • Creation of a Data Model User Guide

The Solution

We analyzed the existing system and extracted detailed requirements for the new data warehouse, and delivered a full-featured solution with the following features, components and technologies:

Features

  • Replication is used to copy data from the remote operational system to the local Operational Data Store (ODS).
  • The system uses triggers on the tables in the operational data store to efficiently capture data changes throughout the day. This minimizes the amount of data that needs to be processed each night during the batch processing window.
  • The Analytic Data Store (ADS) covers the following subject areas:
    • Claims (Medical and Pharmacy)
    • Medicare
    • Billing and Finance
    • Coverage Plans
    • Membership and Groups
    • Provider Financial Analysis
    • Capitation Financial Analysis
    • Self Insured Contracts
    • Attachments
  • The ADS integrates over 300 tables from several different source systems for a total of 8000 data elements. The ADS is built on a dimensional model to make ad-hoc queries easy to compose and efficient to run.
  • TimeFinder creates point-in-time copies of the ODS and ADS so replication and ad-hoc queries can run in parallel with the ETL programs. The copies reduce the daily down time of the ADS to a few minutes.
  • The system provides control totals and auditing for all critical data elements.
  • The Business Objects Universe provides easy access to all ADS data elements
  • ETL batch jobs are controlled and monitored by the Tivoli scheduling system.

Components

  • The Operational Data Store (ODS) contains copies of tables from source systems. Replication is used to collect information from the remote operational system.
  • History information is accumulated through triggers on the target tables in the ODS. The history tables identify the changes that took place in the source systems during each business day. Changes are accumulated throughout the day.
  • ETL programs extract data from the ODS, apply business rules, integrate data from different sources into staging tables and load data into the Analytic Data Store (ADS). All ETL programs are developed using Informatica.
  • Control total programs are written in Java. These programs compare the data in the source systems to the data in the ADS. Both record counts and summed dollar amounts are compared.
  • Tivoli runs the daily ETL batch schedule. The daily schedule includes 278 ETL batch jobs and all control total programs.
  • The ADS supports the following subject areas in the dimensional data model. The model uses conformed dimensions to ensure consistent reporting across subject areas. The analytic data store was built on an Oracle database
  • The business intelligence layer is implemented in Business Objects. We created an integrated universe that provides easy access to all of the analytic information.

Technologies

  • Informatica Power Center
  • Oracle (Target Database)
  • Sybase (Source Database)
  • Sybase Replication Server
  • Business Objects XI
  • System Architect (Data Modeling Tool)
  • TimeFinder
  • Tivoli Batch Scheduler
  • Java, Ant, PVCS, J-Unit

The Results

The data warehouse is the first step to replacing BCN's legacy system, which is expensive to maintain and operate. Once the reporting pieces are i n place BCN will retire their legacy system and significantly reduce their operating costs.

The system enables BCN to balance their financial statements back to their operational data thereby meeting financial audit requirements.

We designed an integrated data model using conformed dimensions and common fact tables to reduce data duplication and ensure consistent reporting.

We designed and built a control total framework that externally reconciles record counts and dollar amounts back to the operational systems. The framework records results in a central table so discrepancies can be reviewed and corrected.

BCN needed data refreshed by 7:00 am each weekday. We developed a data capture system that quickly identified changes to reduce daily run times and meet our batch processing window.

BCN depends on their reporting system to run their business. We applied patches to the existing reporting system that allowed BCN to continue to operate while the new system was developed.

During the project BCN's operational system went through a major upgrade that forced significant changes to the scope of work. We worked with BCN to identify and control these changes for a successful delivery.

 
 

© 2018 The Jonah Group, Ltd. All rights reserved.

461 King Street West, Third Floor, Toronto, Ontario

(888) 594-6260

(416) 304-0860