Overview of Loading Tool Architecture

Figure 1 presents a flow chart for the Loading Tool and the Water Pollution Search and Custom Search (previously referred to as EZ, TRI, and Advanced search options). The Loading Tool pulls Discharge Monitoring Report (DMR) data from EPA's Integrated Compliance Information System for the National Pollutant Discharge Elimination System (ICIS-NPDES). The tool also pulls data from the Toxics Release Inventory (TRI). The data are extracted and processed through several calculation programs to calculate pollutant loads. The tool stores the calculated loads in an Oracle database, which users can access using the tool's online search options. The following is a description of the Loading Tool components:

• DMR Data: The Loading Tool pulls data from approximately 20 ICIS database tables and stores the data in the Loading Tool database. The Tool uses an Informatica workflow to monitor changes to those 20 ICIS tables twice daily. On a weekly basis, the tool refreshes the facility, permit, and DMR data that have changed and recalculates the pollutant loadings.
   
• TRI Data: EPA obtains TRI water release data from EPA's TRI website once annually and stores the data in the Loading Tool database. The Loading Tool uses the following TRI data files:
  • TRI Basic Plus Data Files: 1: Facility, Chemical, Releases and Other Waste Management Summary Information and 3b: Details of Transfers to Publicly Owned Treatment Works (POTWs)
  • TRI National Analysis Dioxin and Dioxin-like Compounds and TEQ Data Files: Water Congener file
     
• Load Calculation Programs and Database Tables:
  • Loading Tool Extraction Program: The Extraction Program reviews the ICIS-NPDES database changes identified by the Informatica workflow, which Informatica populates in change data capture (CDC) target tables in the Loading Tool database. The program reads the target tables and generates a list of unique NPDES IDs and Reporting Years represented in the list of changes. The program then pulls the most recent facility, permit, and DMR data for those NPDES IDs and years from ICIS-NPDES through a database link and stores the updated data in the Loading Tool extraction tables.
  • Loading Tool Convert Program: The Convert Program converts the ICIS-NPDES DMR data into standard units of milligrams per liter, kilograms per day, and millions of gallons per day. The converted DMR data are stored in the DMR_LOADINGS_CONVERT_DMR table in the Loading Tool Database.
  • Loading Tool Load Calculator Program: The Load Calculator Program pulls pollutant measurements and wastewater flows from the DMR_LOADINGS_CONVERT_DMR table, and calculates pollutant loads and aggregated nitrogen and aggregated phosphorus loads. The Load Calculator Program creates the following tables:
    • RPT_MONPER: This database table stores the pollutant loadings per monitoring period (e.g., January 2007) and the aggregated total nitrogen and total phosphorus loadings calculated by the Load Calculator Program.
    • RPT_ANNUAL: This database table stores the annual pollutant loads and the aggregated total nitrogen and total phosphorus loadings calculated by the Load Calculator Program.
  • Loading Tool EZ Search Load Program: To generate the loadings for the EZ Search, the EZ Search Load Program performs the following calculations:
    • Sums the pollutant loads in the DMR_LOADINGS_ANNUAL table by NPDES permit and pollutant;
    • Calculates toxic-weighted pollutant loads;
    • Estimates loads for discharges reported as non-detect
    • Estimates loads for monitoring periods with no reported discharges; and
    • Groups pollutant parameters that represent the same chemical.
    • The program stores the loads in the RPT_EZ_SRCH table.
  • Loading Tool TRI Search Load Program: To generate TRI releases for the TRI Search, the TRI Search Load Program converts all TRI water releases into standard units of pounds per year and calculates toxic-weighted releases. The program stores the TRI releases in the DMR_LOADINGS_TRI_RELEASES table.
     
• Online Search Options:
  • Custom Search: The Custom Search (previously Advanced Search) allows users to conduct a customized query and alter the loading calculation methodology. The Advanced Search provides the results to the user as a downloadable comma-separated value (CSV) file.
  • Water Pollution Search (DMR): The Water Pollution Search (DMR) (previously EZ search) provides a simple interface to query pollutant loads by location, watershed, industry, and pollutant/pollutant category. Based on the user-entered search criteria, the search filters the loads in RPT_EZ_SRCH and displays the results in an HTML view. Users can also download results as a comma-separated value (CSV) file.
  • Water Pollution Search (TRI): The Water Pollution Search (TRI) (TRI Search) provides a simple interface to query TRI releases by location, watershed, industry, and pollutant. Based on the user-entered search criteria, the search filters the loads in DMR_LOADINGS_TRI_RELEASES and displays the results in an HTML view. Users can also download results as a comma-separated value (CSV) file.