Data Warehousing for Compliance with Environmental Laws
The mission of the Environmental Protection Agency is to protect human health and the environment. Compliance and enforcement are an integral part of EPA's mission. Compliance with the nation's environmental laws is the ultimate objective, but enforcement is a vital part of encouraging governments, businesses, and other regulated organizations to meet their environmental obligations.
EPA's Office of Enforcement and Compliance Assurance (OECA) pursues enforcement and provides compliance assistance in areas that yield the most environmental benefit or reduce risk to human health.
OECA needed a high-performance data warehouse to assist EPA's enforcement officials in identifying potential violators for further review. A major challenge was not having direct access to all of the source program databases. This hampered OECA's ability to import the data in a usable format from the Oracle data sources and then to "massage" the data to fit EPA's Integrated Data for Enforcement Analysis (IDEA) framework and requirements.
InfoPro designed highly specialized conversion programs to extract the relational source data and re-work it into a "flat-file" format that was usable within the IDEA framework. In addition, InfoPro developed and maintains the IDEA database—a high-performance data warehouse that integrates critical data from multiple external sources, such as Oracle databases, while facilitating complex queries to help spot patterns and other information of special interest to OECA.
The primary languages involved in the IDEA initiative include PL1 and Assembly within an IBM mainframe environment. As part of the solution, large Oracle databases are partitioned to break data structures into multiple pieces for more efficient management, and then reconnected as part of the extraction process.
EPA managers routinely query this system using various keywords. They also conduct multiple types of queries using different combinations of keywords to identify companies that should be targeted for a more detailed review of compliance-related actions. IDEA supports querying and reporting primarily through a core program called REFRESH. This tool extracts data from the individual source databases and then translates it into a format that allows linking by facility and/or permit number to all of the permit programs. This enables the derivation of new values and descriptors that may have more significance for OECA than just the reported data by itself.
Typical queries performed by OECA include the number of days since the most recent compliance inspection, whether or not an organization is a significant violator, and how many times over the last several quarters a company may have violated regulations. The system also reports companies' recidivism instances and rates with regard to both the degree of non-compliance and the amount of time taken to come back into compliance with the standards.
InfoPro's work on the IDEA initiative demonstrates the high-level analytical and programming skills of InfoPro data warehouse and data mining experts. We have particular expertise in heterogeneous database environments and full capability to support an agency's evolution toward a single enterprise architecture. We also go the "extra mile" in adding value; in this case, for example, we developed and incorporated both valuable "paper trails" covering the history of the source data and a complex algorithm that tracks enforcement actions and how they have been processed.
InfoPro's data warehouse and mining expertise can be valuable whenever an agency or organization needs to integrate a model, a statistical process, or a complex algorithm into a data system. It could serve as a means of determining data to be stored, or for production of an "output" report. Any agency dealing with enforcement of laws, regulations, and standards should talk to us.
For more information on how InfoPro can help your organization, contact us or call (703) 226-2520.