The Safety and Environmental Division (SED) of the California Public Utilities Commission (CPUC) was using multiple Microsoft Access (MS Access) database applications to track mission critical Utility Safety and Reliability (S&R) data for the entities that the CPUC oversees.
Initially, in the early 1990s, the Electric and Gas branches were one integral organization, and a single database application was used for tracking utility incidents, annual audits of utilities, and consumer-reported complaints. When the Electric and Gas organizations were separated into two branches, the database application for incidents, audits, and complaints was separated into two applications: one for the Electric branch, and one for the Gas branch. The split databases were split again and again, and then each individual application was customized further to meet the needs of a particular business unit in a particular geographic location. The resulting seven MS Access database applications each were developed by different business analysts, and subsequently, different code bases and individually unique interfaces exist.
Given the importance of the Utility Safety & Reliability Database System (USRDS) data, the SED management team requested that the seven MS Access database applications be conformed, consolidated, migrated and modernized back into a single solution.
The following diagrams document the transformation from the current state to the final application by our MS Access developers, data and technical solution architectures.
Original Solution State – Logical
Symptoms: Poor application performance, high IT costs of supporting seven different application code bases, data inconsistent across database applications, data corruption, lack of data integrity, lack of ability to support public access, inability to backup data, application crashes, lack of regulatory compliance, lack of user confidence, and a lack of standardized user interfaces and support for single-sign-on.
Future Solution State – Conceptual
The diagrams for the future state have been moved to the bottom of the document to aid in the readability and continuity of the case study. Use the below link or page down. click here..
Two Migration Approaches
The smaller the increment, the smaller the risk and the smaller the gain. Our MS Access developers will be taking an iterative, low-risk approach.
