2004 NEI Submission via XML and CDX

1
2004 NEI Submission via XML and CDX

• Oklahomas Experience

2
This Presentation

• The CDX Network Node and NEI plug-in
• Rene Roy
• Preparation and QC of NIF Data tables
• Mark Gibbs
• NEI XML Preparation and Submittal
• Keith Duncan

3
This Presentation
Set-up NEI Plug-in
Set-Up CDX Node
1.
Basic Format Content Checker
Prepare Data
2.
Convert to XML
3.
Edit XML
Validation
CDX
4
Overview of Part 1

• The Node
• Prerequisites
• Our Choices
• Observed Pitfalls
• The NEI Plug-in
• Prerequisites
• Picking the place to begin
• Coding
• Testing
• The Future

5
The Node

• Prerequisites
• Oklahoma DEQ Network
• Network infrastructure is based on Microsoft
  Active Directory
• Most services are also structured around MS
  products
• The primary RDBMS utilizes the Oracle platform

6
The Node

• Prerequisites
• 2. Existing Expertise
• In-house DBA provide data access and management
  functions
• In-house subject matter experts provide the data
  mapping
• In-house programmers provide development and
  support

7
The Node

• Prerequisites
• 3. Project funding
• Exchange Network grant provided the basis for the
  infrastructure and contractor support

8
The Node

• Our Choices
• Contractor or In-house?
• http//exchangenetwork.net/node/dnc/index.htm
• 2. Windsor Node platform
• Established platform
• Included both server and client software
• Low cost (free)

9
The Node

• Pitfalls
• Reliance on external providers
• Availability
• Cost
• Flexibility

10
The Node

• Pitfalls
• 2. Schema and design changes
• Certain Node flows undergo frequent changes
• NEI v3.0 is current, but is scheduled to change
  in 2008?
• 3. Node and HTTP proxies
• Microsoft ISA Node bad!

11
The NEI Plug-in

• Prerequisites
• NEI Flow Configuration Document
• Outlines required(and optional) services
• Details recommended/required steps to
  constructing and validating NEI data.

12
The NEI Plug-in

• Prerequisites
• 2. Windsor Node Architecture
• Relies on plug-ins to provide data connectivity
• Allows for automation of data collection and
  submission
• Utilizes the Microsoft .NET framework

13
The NEI Plug-in

• Picking the place to begin
• Review of the FCD
• Selected Point Source Data Exchange
• Due to the nature of the data, little automation
  has been implemented

14
The NEI Plug-in

• Coding
• NEI plug-in written entirely in C
• Exposes both Query and Solicit methods
• Provided the basis for future upgrades

15
The NEI Plug-in

• Testing
• CDX Helpdesk provides a testing environment
• Schematron located most format errors in the data
• CDX performs scheduled solicits to collect the
  data

16
The NEI Plug-in

• The Future
• Implement the other services outlined in the FCD
• Perform automatic data transformation
• Perform automatic validation

17
Part 2
Set-up NEI Plug-in
Set-Up CDX Node
1.
Basic Format Content Checker
Prepare Data
2.
Convert to XML
3.
Edit XML
Validation
CDX
18
Preparation of NIF Data Tables

• Production of NIF Access Tables
• QC using EPAs Basic Format Content Checker
• Final status of data before conversion to XML

19
Preparation of NIF Data Tables

• 1. Production of NIF Access Tables
• Main Air Quality Division database (TEAM) is
  currently Access-based.
• Emissions data in hierarchical format
• Facility / Point / Process / Emission

20
Preparation of NIF Data Tables

• 1. Production of NIF Access Tables
• NIF 3.0 tables extracted on daily to weekly basis
  during NEI preparation period.
• 2004 NEI Submission
• 61 Facilities
• 2102 Points
• 2795 Processes
• 9885 Emission Records

21
Preparation of NIF Data Tables

• 2. QC Using EPAs BFCC
• Excellent tool
• Version 3.01 used during most of NEI preparation
  period
• Version 3.1 used briefly after its release in
  early May 2006

22
Preparation of NIF Data Tables

• 2. QC using EPAs BFCC
• BFCC reports used to focus staff efforts
• All corrections made in main TEAM database
• New tables then extracted from TEAM
• Process repeated until final extraction
  immediately before submission

23
Pre-XML Data Status

• BFCC version 3.01

83 stack issues 70 emission amounts gt Range
Max No referential integrity issues
24
Pre-XML Data Status

• BFCC version 3.1

Most errors in tables EP PE due to Oklahoma
collecting and reporting actual annual hours per
year.
922 instances of fugitive dimension problems for
stacks only
25
Part 3
Set-up NEI Plug-in
Set-Up CDX Node
1.
Basic Format Content Checker
Prepare Data
2.
Convert to XML
3.
Edit XML
Validation
CDX
26
NEI XMLPreparation and Submittal

• Step 1. Generate XML file
• Step 2. Edit XML file
• Step 3. Validation
• Step 4. Submit to CDX Test environment
• Step 5. Submit to CDX NEI Production

27
1. Generate XML File

• Create target MSAccess database
• Populate target database
• Create/fill SystemRecordCountValues table
• Use Access ExportXML method to create xml file

28
1. Generate XML file

• Create target MSAccess database
• NEI Flow Configuration Document
• XML Schema
• EN_NEI_Point_v3_0.xsd EN_NEI_Common_v3_0.xsd
• http//www.exchangenetwork.net/exchanges/air/nei.
  htm
• NEI XML NIF Element
• Xreference09-APR-04_v1.xls
• http//oaspub.epa.gov/emg/xmlsearch.startup

29
1. Generate XML file

• Create target MSAccess database
• Target database tables are named to match
  Submission Group names
• Table field names match NEI XML 3.0 Schema
  Element names

30
1. Generate XML File

• Create target MSAccess database
• Populate target database
• Create/fill SystemRecordCountValues table
• Use Access ExportXML method to create xml file

31
1. Generate XML File

• Create target MSAccess database
• Populate target database
• Create/fill SystemRecordCountValues table
• Use Access ExportXML method to create xml file

32
1. Generate XML file

• Use Access ExportXML method to create xml file

33
2. Edit XML File

• Delete Access-generated ltdatarootgt tags
• Add enclosing Document, Payload, and
  PointSourceSubmissionGroup tags
• Add schemaVersion to all opening SubmissionGroup
  tags
• Insert document header
• Find and delete all empty tags

34
2. Edit XML file

• Example XML document nei_sample_instance.xml
• Delete Access-generated ltdatarootgt tags
• ?xml version"1.0" encoding"UTF-8"?gt
• ltdataroot xmlnsod"urnschemas-microsoft-comoffi
  cedata" generated"2006-09-12T154932"gt
• ltSystemRecordCountValuesgt
• ltSystemRecordCountTransmittalValuegt33lt/SystemRecor
  dCountTransmittalValuegt
• ltSystemRecordCountSiteValuegt61lt/SystemRecordCountS
  iteValuegt
• ltSystemRecordCountEmissionUnitValuegt2102lt/SystemRe
  cordCountEmissionUnitValuegt
• ltSystemRecordCountEmissionReleasePointValuegt2102lt/
  SystemRecordCountEmissionReleasePointValuegt
• ltSystemRecordCountEmissionProcessValuegt2795lt/Syste
  mRecordCountEmissionProcessValuegt
• ltSystemRecordCountEmissionPeriodValuegt2795lt/System
  RecordCountEmissionPeriodValuegt
• ltSystemRecordCountEmissionValuegt9885lt/SystemRecord
  CountEmissionValuegt
• ltSystemRecordCountControlEquipmentValuegt9885lt/Syst
  emRecordCountControlEquipmentValuegt
• lt/SystemRecordCountValuesgt
• ltTransmittalSubmissionGroupgt

35
2. Edit XML file

• Add Tags
• ?xml version"1.0" encoding"UTF-8"?gt
• lthdrDocument xmlnshdr"http//www.exchangenetwor
  k.net/schema/v1.0/ExchangeNetworkDocument.xsd"
  xmlnsxsi"http//www.w3.org/2001/XMLSchema-instan
  ce" Id"ID02"gt
• lthdrPayload Operation"PointReplace"gt
• ltPointSourceSubmissionGroup xmlnsxsi"http//www.
  w3.org/2001/XMLSchema-instance"
• xmlns"http//www.epa.gov/exchangenetwork"
  schemaVersion"3.0"gt
• ltSystemRecordCountValues schemaVersion"3.0gt
• ltSystemRecordCountTransmittalValuegt33lt/SystemRecor
  dCountTransmittalValuegt
• ltSystemRecordCountSiteValuegt61lt/SystemRecordCountS
  iteValuegt
• ltSystemRecordCountEmissionUnitValuegt2102lt/SystemRe
  cordCountEmissionUnitValuegt
• ltSystemRecordCountEmissionReleasePointValuegt2102lt/
  SystemRecordCountEmissionReleasePointValuegt
• ltSystemRecordCountEmissionProcessValuegt2795lt/Syste
  mRecordCountEmissionProcessValuegt
• ltSystemRecordCountEmissionPeriodValuegt2795lt/System
  RecordCountEmissionPeriodValuegt
• ltSystemRecordCountEmissionValuegt9885lt/SystemRecord
  CountEmissionValuegt
• ltSystemRecordCountControlEquipmentValuegt9885lt/Syst
  emRecordCountControlEquipmentValuegt
• lt/SystemRecordCountValuesgt
• ltTransmittalSubmissionGroup schemaVersion"3.0"gt

36
2. Edit XML file

• Insert Document Header
• ?xml version"1.0" encoding"UTF-8"?gt
• lthdrDocument xmlnshdr"http//www.exchangenetwor
  k.net/schema/v1.0/ExchangeNetworkDocument.xsd"
  xmlnsxsi"http//www.w3.org/2001/XMLSchema-instan
  ce" Id"ID02"gt
• lthdrHeadergt
• lthdrAuthorgtMark Gibbs, Keith
  Duncanlt/hdrAuthorgt
• lthdrOrganizationgtOklahoma Department of
  Environmental Qualitylt/hdrOrganizationgt
• lthdrTitlegtPointSourcelt/hdrTitlegt
• lthdrCreationTimegt2006-05-26T121212lt/hdrCreat
  ionTimegt
• lthdrCommentgtlt/hdrCommentgt
• lthdrDataServicegtGetNEIPointDataByYearlt/hdrData
  Servicegt
• lthdrContactInfogtPO Box 1677, Oklahoma City,
  OK, 73101-1677, 405-702-4140,
  mark.gibbs_at_deq.state.ok.uslt/hdrContactInfogt
• lthdrPropertygt
• lthdrnamegtGeographicCoveragelt/hdrnamegt
• lthdrvaluegt40lt/hdrvaluegt
• lt/hdrPropertygt
• lthdrPropertygt
• lthdrnamegtInventoryYearlt/hdrnamegt
• lthdrvaluegt2004lt/hdrvaluegt

37
2. Edit XML

• Delete Access-generated ltdatarootgt tags
• Add enclosing Document, Payload, and
  PointSourceSubmissionGroup tags
• Add schemaVersion to all opening SubmissionGroup
  tags
• Insert document header
• Find and delete all empty tags

38
3. Validate XML

• Use the Web based validation tools at
  http//tools.epacdxnode.net/
• A Guidance document is available at
• http//www.exchangenetwork.net/exchanges/air/nei
  _xml_val.pdf

39
(No Transcript)
40
NEI XML Submittal

• 4. CDX Test environment
• 5. CDX Production environment
• Naming conventions
• Zip your file

41
NEI Submission via XML CDX

• The Future
• Implement the other services outlined in the FCD
• Improved automation
• Data transformation
• XML editing
• Internal validation
• Better planning!

42
What is an Ideal Timeline?
Set-up NEI Plug-in
Set-Up CDX Node
1.
Basic Format Content Checker
Prepare Data
2.
Convert to XML
3.
Edit XML
Validation
CDX