Office of Enforcement and Compliance Assurance
Integrated Compliance Information System
Impaired Waters Interface
Technical Specification Document
Version 1.0
June 1, 2011
Office of Enforcement and Compliance Assurance
Document Change History
Version Number / Date / Description0.1 / May 9, 2011 / Draft Baseline Release
1.0 / June 1, 2011 / Incorporated Comments provided by EPA
Table of Contents
1.Introduction
1.1 Purpose
1.2 About the Impaired Waters Transfer
1.3 Assumptions
1.4 Expectations for ICIS Contractor Staff
1.5 Expectations For EPA Staff
2. Usage in IIRS
2.1 BusinessObjects Universe Update
2.1.1 Add the Impaired Waters table
2.1.2 Add the Water Quality Assessment Category object
2.2 Updates to the IIRS Database
2.2.1 External Table Used to Load Impaired Waters
2.2.2 Water Quality Load Process
2.2.3 Water Quality Assessment Table
2.2.4 Water Quality Assessment Reference Data Table
2.2.5 External Log Tables
3. Business Rules
3.1 Business Rule for Validating Water Quality Assessment Value
3.2 Business Rule for Validating Program System Acronym
3.3 Business Rule for Validating Program System ID
3.4 Business Rule for Existing Records
3.5 Process Impaired Waters Processing Flow
4. Impaired Waters Processing Logistics
Table of Tables
Table 21: Water Quality External Raw Data Description
Table 22: Water Quality Stage Description
Table 23: Water Quality Assessment Table
Table 24: Water Quality Assessment Reference Data Description
Table 31: Impaired Waters Processing Flow Supporting Table
Table of Figures
Figure 21: ICIS-NPDES Ad Hoc Universe Screen Shot
Figure 31: Impaired Waters Processing Flow
ICIS Impaired Waters1
Office of Enforcement and Compliance Assurance
1.Introduction
The integration of Impaired Waters data withthe Integrated Compliance Information System (ICIS) Interim Reporting Solution(IIRS)facility information allows the Environmental Protection Agency (EPA) to monitor facilities discharging to Impaired Waters. EPA will periodically provide the ICIS team a text file containing water quality assessment categories for facilities. The Impaired Waters interface will process this file and allow reports to be generated in the ICIS- National Pollutant Discharge Elimination System (ICIS-NPDES) Universe containing pertinent facility and Impaired Waters data. Throughout this technical specification, water quality assessment will refer to the set of data describing Impaired Waters.
1.1 Purpose
The purpose of this document is to provide a comprehensive, detailed description of the Impaired Watersreporting process. The document outlinesthe database interface specification and the data transfer from EPA to theIIRS database. In addition, thisdocument describesthe business rules associated with processing updates to the Impaired Waters data and the BusinessObjects interface for the new object. The logistics for coordinating the data transfer are also included in this document.The supporting database scripts are maintained as separate, living documents.
1.2 About the Impaired Waters Transfer
The Enforcement Targeting and Data Division(ETDD) of EPAis developing ICIS-NPDESreports to retrieve the actual number of inspections and percentages for the various Compliance Monitoring Strategy (CMS) metrics. These reports are necessary so managers can evaluate how the states and regions are using the flexibility in the CMS to provide inspection coverage for NPDES and wet weather facilities. ICIS must have the ability to associate inspection coverage and enforcement actions for NPDES and wet weather facilities against impaired water bodies. To accomplish this, a comma delimited text file, containing theProgram System Acronym (NPDES or ICIS), the Program System ID and the “Water Quality Assessment Category” will be provided to ICIS by the Reports and Analysis Branch. This text file will then be integrated within the ICIS reporting solution. Periodic updates will be provided to these data.
1.3 Assumptions
•The ICIS application will not contain any Impaired Waters information.
•ICIS v4.1.0 will contain all necessary functionality for the initial load of Impaired Waters data; a subsequent release will contain all necessary functionality for processing updates to the Impaired Waters data.
•The content and format of the staging area may change as the result of modernization or enhancements of the IIRS table structures if agreed upon by EPA and the ICIS team.
•Since these data are not stored in the operational database, the ICIS-FRS transfer will not be impacted by this functionality; if at a future time the data are stored in ICIS, this assumption will be revisited.
1.4 Expectations for ICIS Contractor Staff
ICISwill support Impaired Waters through processes implemented in the IIRS. These processes will ensure that only valid data (according to the business rules) are loaded into the reporting solution.
1.5 Expectations For EPA Staff
•EPA will provide an updated Impaired Waters file on a quarterly basis.
•EPA or National Computer Center (NCC) will set up a directory on the EPA Test and Productionservers to store the Impaired Waters file.These directories are documented separately.
•EPA will send the quarterly Impaired Waters file to NCC Database Administrators (DBAs) and NCC DBAs will upload the file directly to the server. NCC will rename the previous quarter’s file using the following naming convention: YYYYMMDD_ICIS_IMPAIRED_WATERS.txt. The most current version of the file should be named ICIS_IMPAIRED_WATERS.txt. NCC and EPA will notify the contractor by email after the file is uploaded.
•The uploaded file will conform to the format and business rules detailed below in Section 3.
2. Usage in Reporting
Impaired Waters functionality will be available for reporting through the use of BusinessObjects in the ICIS-NPDES Ad Hoc universe. The data underlying the ICIS-NPDES Ad Hoc universe are provided by the ICIS Interim Reporting Solution (IIRS) reporting database, which is populated by a separate Extract, Transform, and Load (ETL) process from the ICIS operational database.
ICIS will be updated to support Impaired Waters reportingin two ways:
•Update the BusinessObjects ICIS-NPDES Ad Hoc Universe
•Update the IIRS database.
2.1 BusinessObjects Universe Update
The BusinessObjects Universe will need to be updated in two ways:
•Update the universe to include the new Impaired Waters table
•Update the universe to add the new Water Quality Assessment Category object.
2.1.1 Add the Impaired Waters table
Update the ICIS-NPDES Ad Hoc Universe to include the Water Quality Assessment table (WATER_QUALITY_ASSESSMENT). This table will be joined as an outer join with the facility dimension (facility_dim) on the following two columns:
facility_dim.pgm_sys_acrnm_fi = water_quality_assessment.pgm_sys_acrnm (+)
facility_dim.pgm_sys_id_fi = water_quality_assessment.pgm_sys_id (+)
The join is an outer join because not all facilities will have Impaired Water categorizations.
2.1.2 Add the Water Quality Assessment Category object
The new Water Quality Assessment object will be added to the “Fac Identifying & Descriptive Info” folder, a sub-folder to “Facilities”, in the ICIS-NPDES Ad Hoc Universe (see Figure 2-1: ICIS-NPDES Ad Hoc Universe Screen Shot). The object will operate in the following manner:
•If the object is used as a query filter, the user will select the water quality assessment value from the list of values, and the object will return data that match the criteria selected.
•If the object is used in the report results, the water quality assessment value associated withthe facility interest will be displayed.
Figure 21: ICIS-NPDES Ad Hoc Universe Screen Shot
ICIS Impaired Waters1
Office of Enforcement and Compliance Assurance
2.2 Updates to the IIRS Database
Several tables will be added to the IIRS database to support the Impaired Waters functionality. An external Oracle table, WATER_QUALITY_EXT_RAW_DATA, will store the most recently submitted file and will be overwritten for each load. A staging table, WATER_QUALITY_STAGE, will store all the data recently submitted in addition to a history of all previously submitted Impaired Water data. The staging table will be used for processing the data into the target table, Water Quality Assessment. The target table will be the actual table used for reporting and will be updated with valid data from the staging table that meets the business rules defined in Section 3.
2.2.1 External Table Used to Load Impaired Waters
An external table is used to read flat files as though they were ordinary Oracle tables. The use of an external table is convenient because it can reference a text file residing on a server, such as the Impaired Waters text file on the EPA server. The external file allows read-only access as if the data were in a table in the database however the table is not actually in the database. As long as the external file is in an Oracle format, such as comma-delimited text, and resides on the server, an external table can easily read the data with a simple select statement. The use of the external table eliminates the need to load text files to intermediate tables for processing—saving both time and storage space. The text file can change with minimal maintenance involved tore-load the new data each quarter – as long as the file name is the same and exists in the correct server directory, the select operation will allow immediate access to the changed dataset. The external tablewill load all rows where at least one data element is present. Data validation on the text file occurs using PL/SQL scripts.Rows where all fields are blank in the text file will berejected at the external table level and willnot be processed by the PL/SQL scripts.Table 2-1 provides a description of the Water Quality External Raw Data table (WATER_QUALITY_EXT_RAW_DATA).
Table 21:Water Quality External Raw Data Description
Column Name / Data Type / Length / Null / Default / CommentPGM_SYS_ACRNM / VARCHAR2 / 20 / Yes / Null / The abbreviated name that represents the name of an information management system for an environmental program.
PGM_SYS_ID / VARCHAR2 / 30 / Yes / Null / The unique identifier used by the environmental programs when identifying the facility interest.
WATER_QUALITY_ASSESSMENT_TEXT / VARCHAR2 / 30 / Yes / Null / The description that defines the water quality assessment, also known as the impairment category.
The format of the comma delimited text file to be supplied by EPA will be:
PROGRAM SYSTEM ACRONYM,PROGRAM SYSTEM ID,IMPAIRMENT CATEGORY
The valid values for the Program System Acronym will be:
•ICIS
•NPDES.
The valid values for Impairment Category will be:
•Yes (TMDL)
•Yes (303D)
•Yes (Upstream)
•No NPDES Mapping
•No Spatial Overlap.
2.2.2 Water Quality Load Process
A database script will load the Water Quality ExternalRaw data into the Water Quality Staging table. A separate Oracle PL/SQL package will read the data from the staging area and insert the data into the Water Quality Assessment table. Impaired Water data will be appended to the staging table with each data load received for interfacing, maintaining all historical data sent.
The Latest_Load_Flagfield will be set to “Y” for the most recently loaded rows of data. When new data are loaded into the staging table, all the existing rows in the staging table will have the Latest_Load_Flag set to “N.” The procedures used to move data from the staging area to the water quality assessment table will process only the rows with the Latest_Load_Flag = “Y.” This process will keep a history of all updates provided to these data over time.
Table 2-2: Water Quality Stage Description provides a description of the Water Quality Stage table (WATER_QUALITY_STAGE).
Table 22:Water Quality Stage Description
Column Name / Data Type / Length / Null / Default / DED CommentWATER_QUALITY_STAGE_ID (PK) / NUMBER / No / The system generated unique identifier for a facility with impaired water.
PGM_SYS_ACRNM / VARCHAR2 / 20 / No / The abbreviated name that represents the name of an information management system for an environmental program.
PGM_SYS_ID / VARCHAR2 / 30 / No / The unique identifier used by the environmental programs when identifying the facility interest.
WATER_QUALITY_ASSESSMENT_TEXT / VARCHAR2 / 30 / Yes / The description that defines the water quality assessment category.
LATEST_LOAD_FLAG / VARCHAR2 / 1 / No / N / The flag indicating the most recently loaded rows of data. A value of ‘Y’ indicates the most recently loaded rows of data. A value of ‘N’ indicates previously loaded data.
LOAD_DATE / DATE / No / A system-generated value that represents the most recent calendar date and time the corresponding information was loaded to the stage table.
2.2.3 Water Quality Assessment Table
The Water Quality Assessment table will contain the validated Impaired Waters data for the facilities. The latest Impaired Water data will be loaded to the Water Quality Assessment table from the staging table according to the following condition:
•If a record already exists in the Water Quality Assessment table for the combination of Program System Acronym and ProgramSystem Identifier, the PL/SQL script will check to see if the Water_Quality_Assessment_text differs and will update the data according to the business rules (see Section 3).
The following table provides a description of the Water Quality Assessment Table (WATER_QUALITY_ASSESSMENT).
Table 23:Water Quality Assessment Table
Column Name / Data Type / Length / Null / Default / DED CommentWATER_QUALITY_STAGE_ID (PK) / NUMBER / No / The system generated unique identifier for a facility with impaired water.
PGM_SYS_ID / VARCHAR2 / 30 / No / The unique ID used by the environmental programs when identifying the facility interest.
PGM_SYS_ACRNM / VARCHAR2 / 20 / No / The abbreviated name that represents the name of an information management system for an environmental program.
WATER_QUALITY_ASSESSMENT_TEXT / VARCHAR2 / 30 / Yes / Null / The description that defines the water quality assessmentcategory.
CREATED_DATE / DATE / No / A system-generated value that represents the most recent calendar date and time the corresponding information was posted to the database.
CREATED_BY / VARCHAR2 / 30 / No / IMPWATER / The user ID of the person who entered the data into the system. This will always be set to “IMPWATER”.
UPDATED_DATE / DATE / No / A system-generated value that represents the most recent calendar date and time the corresponding information was updated in the database.
UPDATED_BY / VARCHAR2 / 30 / No / IMPWATER / The user ID of the person who updated the data in the system. This will always be set to “IMPWATER”.
2.2.4 WaterQuality Assessment Reference Data Table
The Water Quality Assessment Reference Data table contains the valid Water Quality Assessment Text values. The data in the other tables will be checked against this table for valid data during processing. The following table provides a description of the Water Quality Assessment Reference Data table (REF_WATER_QUALITY_ASSESSMENT).
Table 24:Water Quality Assessment Reference Data Description
Column Name / Data Type / Length / Null / Default / DED CommentWATER_QUALITY_ASSESSMENT_TEXT / VARCHAR2 / 30 / No / The description that defines the water quality assessment category.
STATUS_FLAG / VARCHAR2 / 1 / No / The flag indicating whether the record is active or inactive.
2.2.5 External LogTables
An External Run Log will be created. Records that were processed will be tracked in the Run Log table. An External Exception Log table will also be created and will contain key data for records that were not processed during the transfer load. The Exception Log table will include a message for each rejected record, indicating the violated business rule.
ICIS Impaired Waters1
Office of Enforcement and Compliance Assurance
3. Business Rules
This section summarizes the business rules for processing Impaired Waters data.
3.1 Business Rule for ValidatingWater Quality Assessment Value
IIRSvalidates the field Water_Quality_Assessment_Text from WATER_QUALITY_STAGE against the reference table REF_WATER_QUALITY_ASSESSMENT. If any row of data in the staging table is invalid, IIRS will not populate the row in the target tableWATER_QUALITY_ASSESSMENT, and a record in the log table will be generated.
3.2 Business Rule for Validating ProgramSystem Acronym
Valid values for ProgramSystem Acronym are NPDES and ICIS. The PL/SQL script will check for valid values in the staging table; if any other value was sent, the procedure will will not populate the row in the target table, WATER_QUALITY_ASSESSMENT,and a record in the log table will be generated.
3.3 Business Rule for Validating ProgramSystem ID
The Program System ID must not be a blank value. If the Program System ID is null, the PL/SQL procedure will will not populate the row in the target table, WATER_QUALITY_ASSESSMENT, and a record in the log table will be generated.
3.4 Business Rule for Existing Records
If a record already exists in the Water Quality Assessment table with the same Program System Acronym and Program System ID, the PL/SQL procedure will check to see if the Water Quality Assessment Text has changed. If the Water Quality Assessment Text has changed, the record will be updated, with one exception to this rule:
•If the existing record’s Water Quality Assessment Text from the WATER_QUALITY_ASSESSMENT tableis either “No Spatial Overlap”, “Yes (TMDL)”,“Yes (Upstream)”, or “Yes (303D)” and the latest load’sWater Quality Assessment Text from the WATER_QUALITY_STAGE tableis “No NPDES Mapping”, the existing value will not be overwritten in the WQA table.Process Impaired Waters Processing Flow
Figure 3-1: Impaired Waters Processing Flow is a diagram depicting the processing of a new set of Impaired Waters data. Also included in this section is a table detailing each step in the flow.
Figure 31: Impaired Waters Processing Flow
Table 3-1: Impaired Waters Processing Flow Supporting Table contains a description of the items from theImpaired Waters Processing Flow. The Item Number column refers to the Processing step being referenced. The Item Description column gives a more in-depth explanation of each step of the process.