# Presentation: 512462

## Flowing Data from Texas to EPA: The WQX Experience 

- Nancy Ragland
- Texas Commission on Environmental Quality
- STORET Conference
- November 28, 2007

## The Data Systems

## TCEQ Regulated Activities and Compliance System (TRACS)

- Antiquated
- Hard to Maintain
- No longer meeting agency business needs

**Notes:**
Built in the late 80’s or early 90’s
No documentation on development process
Code a patchwork of ‘band aid’ fixes
Little or no documentation
Not flexible enough to update
Conclusion that resources needed to do a major overhaul would be better spent developing a new system

## Surface Water Quality Monitoring Information System (SWQMIS)

- Why not STORET
- Buy or Build?
- Enhancing the Experience

**Notes:**
Did not meet our agency business needs
Perception that we would need to change our business processes
Could not find a Customizable Off The Shelf (COTS) that met our business needs
Assessment Enhancement
Data Flow Enhancement

## SWQMIS – Assessment Enhancement

- Needed an integrated way to complete the 305(b) assessment
- Wanted to interface directly with SWQMIS
- Wanted to replace all the hand calculations that were being done
- Built an add-on to SWQMIS that uses SAS®  to compute statistics.

## SWQMIS – Data Flow Enhancement

- Had not reported data to EPA since 1998
- Needed a way to report data to EPA without radically changing business processes
- Participated in WQX pilot project

**Notes:**
We were fortunate enough to get the same contractor that built SWQMIS
Strongly advise that the contractor is familiar with your data system
Advise that agency staff are familiar with your data system

They knew our data system
We still had an active contract with them
No learning curve = Saved time = saved money
No learning curve = Saved time
Can better advise and oversee work of contractor

## NEIEN Grant

## National Environmental Information Exchange Network Grant

- Easy to apply
- Application process online
- www.grants.gov
- Freed up agency staff to continue agency business
- Still let us have control of the development of the data flow
- There are some commitments to be aware of
- Budget tracking
- Reports

## The Water Quality Exchange (WQX) – Getting Started

## http://www.epa.gov/storet/wqx.html

- THE place to start
- Includes step by step instructions
- Has many helpful downloads
- SRS characteristics in electronic format
- WQX database in MS Access
- XML training manual

## Slide 11

## Short Hand Steps

- Step 1 – Get 2 NAAS accounts (one for test, one for production)
- Step 2 – Get an Org ID
- Step 3 – Get a TPA in place
- Step 4 – Map data to the schema
- Step 5 – Generate XML
- Step 6 – Create test submission

**Notes:**
Network Authentication Authorization Service – domain admin – see exchange network website

## Helpful Hints

## Mapping to the Schema

- If you have not been using STORET, you may be calling the same piece of information something slightly (or totally) different than what EPA has been calling it
- Some of our data has pick lists where EPA had values and vice versa
- We did not always collect data in a way that meshed with the schema

**Notes:**
Chemical compounds that have synonyms (ex., 2,4-Dimethylphenol vs. 2,4-Xylenol)
Physical parameters that can be described different ways and still have the same meaning (ex., DO vs. Dissolved Oxygen, Conductance vs. Conductivity)
Constituents our agency monitors that are not in SRS (we have several we are not flowing)
We are not flowing these yet because this is a SWQMIS database change that was out of the scope of our data flow contract
We are committed to enhancing SWQMIS to flow this data at a later date
This represents less than 1% of our data
This required some hard coding of values
Required some data to be updated in SWQMIS

## SRS Characteristics

- This was the most challenging and time consuming part of the mapping
- Used MS Access to match as many as possible
- Matched the other 200+ by hand

**Notes:**
This was a completely new concept that came along after SWQMIS was built and after our data flow work was underway
Required many man-hours by data management staff to update constituents in SWQMIS
Had to understand the data
Used queries between SWQMIS and SRS
Had to have electronic version of SRS (provided by EPA)

Required many man-hours by data management staff to search constituent by constituent in SRS
Had to understand the data

## SRS Characteristics

- Not case sensitive
- Spaces have to match exactly
- All characters have to match exactly
- All punctuation has to match exactly

**Notes:**
Can be UPPER (ex., DISSOLVED OXYGEN)
Can be lower (ex., dissolved oxygen)
Can be Mixed (ex., Dissolved Oxygen)
Whatever the spacing in SRS, the same has to come across in your data flow (ex., DO vs. D O)
If there are too many or too few, flow will fail (ex., 1,2- Tetra vs. 1,2-Tetra)
Other than case, characters have to match exactly
All punctuation has to be present
All numbers have to be present

## Nodes

## What is a Node?

- A node is your presence on the exchange
- A node can talk to other nodes
- Can send and receive data packages
- Can send and receive error logs or other documents

## State Node

- Texas Integrated Data Exchange Node (TIDEN)
- A shell to build the flow in
- Sends submissions to CDX
- Retrieves error logs

## Slide 20

## EPA Node

- Central Data Exchange (CDX)
- Test
- Production
- Houses WQX schema and Schematron validation tool

## Slide 22

## The Submission

## The Tool

- We chose PL/SQL because our contractor was most proficient in this language and because they decided the most efficient and flexible way to go
- The PL/SQL code carries out the mapping and creates the XML submission
- Makes the error logs more human readable

**Notes:**
There are many other tools out there
You will want to pick the one that works best for your shop
Shop around
Talks to SWQMIS to get the data
Puts the data in the WQX XML schema format
Wraps the XML in the header and sends to TIDEN
Takes output (Oracle?) and puts it into something a layman can read
Gives a series of warnings and errors
Identifies records that may have caused the error

## The Data Package

- Contains the actual data
- Must be formatted according to the schema
- Must be zipped

**Notes:**
XML format
Header
Data from SWQMIS
Otherwise it will fail when it gets to CDX
Best if your underlying data system mirror the general heirarchy (result, activity, trip)
Size limitations of CDX
Size limitations of TIDEN
CDX is expecting a zipped file

## Delivering the Data

- Must go from state node to CDX
- Is transferred using Web Services
- Will result in a log file

**Notes:**
That is how communication happens on the exchange network
NAAS is set up for security
CDX can communicate back to state node
Submit
GetStatus
Download
Commonly called Error Log
Will show status of submittal
Will show error, if any, that occurred

## Conclusions

## Next Steps

- Biological Data
- Pilot participants
- Will add to our current flow
- Maintain the current flow
- We will continue to flow data according to our current Trading Partner Agreement (TPA)
- We plan to continue renewing our TPA

## Lessons Learned

- Map before you build
- Plan ahead
- Test, test, test!
- Work closely with EPA
- Work closely with your IT people

## Contact Info

## Nancy Raglandnragland@tceq.state.tx.us(512) 239-6546