Data Management System Comes from MARS
By Thomas Foley, EcoAnalysis Inc.
Regulatory agencies and the organizations they regulate (permittees) often sit on opposite sides of the table. However, in the development of a data management system to administer monitoring programs and establish a big-picture approach to water quality management, the Los Angeles Regional Water Quality Control Board (LARWQCB) and several of its major permittees improved mutual understanding while creating the monitoring and reporting system (MARS).
The LARWQCB is responsible for the administration and application of regulatory requirements to protect the water quality in the Los Angeles and Ventura Counties. These regulations are set forth in the Federal Clean Water Act and State of California Water Quality Control Plans. As a result of these regulations, permittees are required to monitor the concentrations of pollutants discharged from their facilities. In addition, they often must develop and implement monitoring programs to detect the impacts of their discharges on surface and ground water environments. Guidelines for these monitoring programs are set forth in either the National Pollution Discharge Elimination System and Waste Discharge System permits.
To comply with these monitoring programs, permittees must conduct sampling, provide for laboratory analysis and report the results of these programs. This effort is accomplished by the permittees themselves or through outside consultants at a cost of millions of dollars each year. The final results and assessments of these programs are reported directly to the LARWQCB in formal reports, which range in size from several to hundreds of pages.
The size, complexity and sheer numbers of these reports have been extremely taxing on the staff, management and archiving facilities of the LARWQCB. In the past, the LARWQCB staff has not had time to evaluate all of this information. As a result, they have relied on the discharge agencies to review their own reports to ensure that permit limits were met and that receiving waters were not impacted. Additionally, the enormous size of the report archives has made it difficult to access relevant information needed to assess the entire watershed.
Acting on this problem, the LARWQCB began a novel approach-a cooperative effort with several major permittees willing to support the development of a data management system. By playing a role in the development process, these permittees gain insight and understanding of the objectives of the regulating body. In return, the LARWQCB gains perspective on the concerns and issues that face permittees when new programs are instituted.
One of the major rewards to the permittees is that they play a role in the design of a program to which they must ultimately comply. EcoAnalysis (Ojai, Calif.), an environmental information management and consulting firm, was asked to create a user-friendly data management system that would streamline the tracking and compliance tasks, act as a repository for monitoring data from throughout the region, and provide the LARWQCB with a means to address watershed management.
Development of the System
One of the most common mistakes made by software designers is to determine the needs of their clients in relative isolation. “In addition to the required technical expertise, a successful data management system greatly depends on an understanding of the people who will be using the system and the working culture surrounding them,” stated Rick Packard, EcoAnalysis president. The most successful systems require working with both management and the ultimate system users to set goals and objectives, while considering the working environment and the organizational structure. To accomplish this, EcoAnalysis conducted a series of intensive design meetings attended by management and representatives from each of the groups within the LARWQCB. With each meeting, more detail was uncovered regarding jobs, daily operations, the types of data and how these data were ultimately used.
One of the big challenges facing this system was how to get an extensive amount of information accurately into the system. To overcome this problem, electronic data interchange (EDI) was selected as the means to accomplish this task. EDI is a process where information is transferred electronically between parties via the Internet or disk exchange. The use of EDI will replace the production of many of the permittees paper reports and the shipping and storage issues related to them. EDI requires setting up the data format so that the permittees and the agencies are sending and receiving the data in the same language organized in the same manner.
Upon instituting EDI, permittees will simply put their monitoring data into the formats on a diskette and mail it to the LARWQCB. There, the data manager loads the data from these diskettes into the data management system, using a series of automated loading routines. To ensure the success of the EDI program, the cooperation continued as the LARWQCB held a series of meetings with the discharge agencies to provide EDI training and to gain feedback regarding the formats from these agencies.
Functionality of MARS
The primary objectives of the MARS data management system included:
a user-friendly environment,
a streamlining of the tracking and compliance efforts,
a repository for monitoring data from throughout the region, and
a means to evaluate the water quality of the entire watershed.
With these goals in mind and considering the large volume of information coming to the LARWQCB, ORACLE was chosen as the data management engine for the following reasons:
With up to 150 potential users, LARWQCB required client-server database technology.
ORACLE is currently in widespread use and is well supported.
It provides powerful data indexing capabilities so that even highly complex data designs can be efficiently handled, and processing times can be minimized.
SQLWindows was used to create Windows screens that are user-friendly. End-users do not require any experience using ORACLE. The Windows interface act as the go-between for the database and allows users to set up sophisticated data searches using click-and-drag Windows technology. The data are returned into views that can be manipulated and exported to other products.
Data Arrives via EDI
Data arriving at the LARWQCB from discharge agencies on diskette go through a three-step process. First, the data are automatically loaded into the data management and checked against permit information stored in the system for timeliness and completeness. Next, the data is rigorously quality assurance checked for data management problems such as duplicates and incorrect codes. It is imperative that data entering the system be of the highest quality possible. Finally, the data for each discharger are compared against permit limits for instantaneous maximum, weekly average and six-month medians, to name a few. An on-line report is then generated that includes permit excursions, timeliness and completeness information. This report is routed to the correct engineer on his or her workstation for review. Automation of this process has allowed the LARWQCB to take the lead for the first time regarding compliance monitoring.
Data Evaluation and Reporting
MARS includes sophisticated agency and facility tracking modules for both surface and ground water programs. All agencies reporting to LARWQCB are included in this system so that members can access information on the facilities for which they are responsible. Staff members can update information on these facilities and include information regarding violations, inspection dates and actions, and permit renewals. Other information includes facility locations, key contacts, flow volumes, watershed, SIC codes, etc. The systems automatically output electronic and paper documents that LARWQCB staff sends to other regulatory agencies at both the state and federal level.
Possibly the most powerful feature of MARS is the tools developed for data searching, analyzing and displaying data stored in the system. MARS provides access to all of the data and provides filters that allow users to break out the data and establish searches based on a data category such as water quality or industry type. They can then retrieve relevant information such as facility information, date spans, constituents, location and other criteria. In seconds, the desired data is located and displayed.
Users then have many options available to them for making assessments. The system contains graphics tools, so data can be quickly displayed in histograms, line graphs or pie charts. MARS also incorporates GIS mapping software, such as ArcView, allowing data to be plotted on maps in seconds. In addition, GIS software has been customized to allow the user to break down the data geographically after it is plotted and continue the analysis. All of this is accomplished in minutes using only a mouse.
After reviewing the information, users can export the data to more than 130 commercial software products (i.e., Microsoft Office, WordPerfect, SAS, etc..) for further assessments or quickly refine their search. This entire process uses icons and standard Windows format, so it takes only a few minutes. From here, information can be reported either electronically or printed with the click of a mouse. In addition, MARS can automate the production of dozens of standard documents which the LARWQCB produces on a regular basis. Before MARS, time considerations made this process impossible.
MARS shows the promise of incorporating the Internet to expand public awareness by easing the access to data and to provide an alternative method of EDI transfer. One thing is certain-efforts to develop the MARS program have demonstrated the benefits of a cooperative effort between regulators and permittees.
If you would like to see more articles on this topic, circle R.S. 105.
For more information on this article, circle R.S. 106.
The monitoring and report system queries specific data types for further analysis.
Information from the pivot table is graphically depicted in the pie chart, which shows concentrations of aluminum broken down by species.