ECI Architects Award Winning Business Critical Solution

The Facility Data Mart Project was a recipient of a "Best Solution Award" at the 2007 GTC West conference and was a featured solution during the conference.

Background

OSHPD function is the collection, validation and dissemination of health care facility data as mandated by the California Legislature. To continue to meet their enterprise reporting requirements for licensed healthcare facilities in California, and their desire to extend the cross subject area reporting of facility information to support the secured health information needs of internal and external information consumers, OSHPD required the development of a new Facilities data mart development.

OSHPD contracted with Estrada Consulting (ECI) to assess, design and develop a new Facility data mart that served as the facility dimension for their two main data marts, the IP (In-Patient) and EDAS (Emergency dept./Ambulatory Surgery) data marts, and for future data marts and enterprise reporting. 

The development of the Facility Data Mart also required upgrading OSHPD's Business Objects reporting system to correctly handle changes data warehouse environment, and to upgrade the software to version XI.  Existing Informatica Extraction/Transformation/Loading (ETL) processes for the facility dimension in the EDAS data mart was re-worked for use with the new Facility data mart, and formerly stand-alone Informatica Workflows were consolidated into a single, Enterprise ETL process.

Additional components of the Facility Data Mart implementation included:

• Automated facility updates between the new data mart and the Enterprise Geographic Information System (EGIS) vector database.
• Development of a web-services based address validation and Geo-coding solution for the OSHPD ALIRTS Licensing and Facility Information System that uses the ESRI web-services interface.
• Re-architected a uniform facility fact and dimension.
• Provided expanded facility reporting capability for the IP and EDAS subject areas (data marts).

ECI solution improved enterprise reporting by facilitating the sharing of data across diverse subject areas while improving data consistency through the use of a shared facility data mart.   In addition to improving the isolated data problem, ECI also accomplished the following:

• Enabled enterprise management and dissemination of facility data to support cross subject area reporting for OSHPD
• Automated facility updates between the new data mart and the Enterprise Geographic Information System (EGIS) vector database
• Created a uniform facility fact and dimension facilitating improved and consistent reporting throughout the enterprise
• Provided expanded facility reporting capability for the IP and EDAS subject areas (data marts)
• Significantly reduced Reporting time cycles
• Automated the generation and publishing (to the OSHPD website) of the EDAS 15-Day Reports as part of the Informatica Enterprise ETL process.

 ECI also provided Data Warehouse maintenance and support services throughout the project that included database, ETL, and reporting maintenance and support services for the IP, EDAS, and Facility data marts including modifications to data mart tables, ETL processes, Business Objects universes and Business Objects reporting.

Additional support services included:

• Maintenance and support services to augment current technical staff to assist in the planning, prioritization, development, testing, and implementation of specified data base, ETL, and reporting modifications.
• Met with technical and program staff to determine work priorities
• Assisted in scoping and estimating work efforts
• Created technical Specifications for maintenance and support services work planned,
• Developed specifications for maintenance and support services work accomplished with format and content approved by the Project Manager.
• Worked with technical staff to coordinate modifications using configuration management practices
• Developed testing plans and coordination of User Acceptance Testing.
• Provided training/knowledge transfer for OSHPD Data Warehouse support staff on modifications to databases, ETL scripts, Business Objects Universes, and maintenance processes.
• Revised Documentation for the maintenance and support services.
• Assisted technical and program staff to identify and debug performance and reporting issues and defects.

• Oracle 10 G
• Informatica 8
• Business Objects XI R2
• ERWIN
• TOAD for Oracle