AQS Introductory Course for New Users Load AQS

AQS Introductory Course for New Users – Load AQS Conference, June 8, 2010 Colorado Springs, CO Standard Reports

AQS Overview Standard Reports

We Will Cover n n What AQS Is Where it Comes From The Types of Information it Contains How it Stores this Information AQS Conference, June 2010 3

What is AQS? n n EPA’s Oracle Database Application used to store Ambient Air Quality Data Centralizes the Location of Data ¨ Used to Determine if areas are Meeting Air Quality Standards ¨ Used by Universities and Institutes to Perform Health Studies AQS Conference, June 2010 4

Origins of Air Regulations & AQS • SAROAD (1970 – 1985) - - Storage And Retrieval Of Air Data - - Created in Response to the 1970 Clean Air Act • AIRS – AQS (1985 – 2000) - - Aerometric Information Retrieval System - - Stored Ambient Air Quality Data (Air Quality Subsystem) as well as Point Source Emission Data • AQS (2000 – Present) - - Air Quality System - - Contains Ambient Air Quality Data and Data System EPA Action Quality Assurance Information Clean Air Act CAA Amended 1970 1977 SAROAD PARS Update of NCC Hardware 1990 2000 AIRS AQS Conference, June 2010 AQS Moves off Mainframe to Client-Server Architecture 2002 AQS-Web 5

How Does AQS Fit in the Big Picture? Monitor the Air Regulate Acquire Data Analyze Store Handle Data Report (Load) Data AQS Conference, June 2010 6

What makes up what we call “AQS”? Oracle 11 g Database AQS • the AQS database • Holds all the Data • Holds all the Processes and Codes • Enforcement of the Rules • The User Interface • How we interact with the database CDX Oracle Forms & Reports • External to AQS • How Files are Sent To & From AQS Conference, June 2010 7

rs Da ta ito Mo n Sit e Types of “Core” Data in AQS Conference, June 2010 8

Information in AQS The AQS database can be considered to have four fundamental types of data: Sites Information about monitoring locations n Monitors Information about how measurements are taken n Detail Data Measurements, Summaries, and QA information n Reference Data Extra information about the data in AQS Codes used to identify States, Counties, Tribal Lands, Pollutants, etc. n AQS Conference, June 2010 9

AQS Data at a Glance Type of Data # of records Raw data (2005) Site Monitor Quarterly Summary (2005) Annual Summary (2005) Precision (2005) 81, 000 18, 000 227, 000 304, 000 115, 000 197, 000 Accuracy (2005) 18, 000 AQS Conference, June 2010 10

Why is the EUL Necessary? Annual Summaries Sites Monitors AQS Conference, June 2010 11

AQS Site and Monitor Data Model AQS Conference, June 2010 12

AQS Data Model – Sample Data AQS Conference, June 2010 13

AQS Data Model – Summary Data AQS Conference, June 2010 14

AQS Reference Data n Lots of “Extra” Information about the Data in AQS Codes used for Descriptions ¨ Standard Codes Used where Available ¨ n Codes are Used to Identify States, Counties, Tribal Lands ¨ Pollutants, Sample Lengths ¨ etc… ¨ AQS Conference, June 2010 15

Examples of Commonly Used Codes n Parameter Codes ¨ ¨ ¨ n n Ozone = 44201 NO 2 = 42602 NO 2 = 42401 CO = 42101 PM 2. 5 = 88101 Lead = 14128 ¨ ¨ ¨ n Units of Measure ¨ ¨ ¨ Collection Frequency Codes 001 = µg/m 3 007 = Parts per Million 008 = Parts per Billion 1 = Every Day 3 = Every 3 rd Day 6 = Every 6 th Day Duration Codes ¨ ¨ ¨ 7 = 24 Hours 1 = 1 Hour W = 8 -Hour Running Avg. * X = 24 -Hour Block Avg. * Y = 3 -Hour Block Avg. * * AQS Generated Durations AQS Conference, June 2010 16

Putting The Core & Reference Information Together n n An Ozone (44201) Monitor in Wake County(183), North Carolina (37) May be Represented as ¨ 37 -183 -0001 -44201 -1 A PM 10 (81102) Monitor for the St. Regis Band of Mohawk Indians of New York (007) May be Represented as ¨ TT-007 -1234 -81102 -1 AQS Conference, June 2010 17

AQS changes to meet regulatory needs n Exceptional Event Rule (EER) The Federal Regulations (40 CFR Part 50. 14) allow Ambient Air Quality Measurements that are Exceedances or Violations of the National Ambient Air Quality Standards (NAAQS) that are caused by an “Exceptional Event” to be excluded from Attainment demonstrations. ¨ Was implemented in AQS in March 2010 ¨ Refer to AQS Exceptional Event Tutorial ¨ AQS Conference, June 2010 18

AQS Process for EER 1) 2) 3) 4) 5) 6) Reporting Agency identifies data affected by an exceptional event Reporting Agency flags the data in AQS (online or batch) Reporting Agency enters the Exceptional Event Description in AQS (must be done online) Reporting Agency associates the data in AQS with the Exceptional Event (must be done online) EPA Regional Office either concurs or denies exclusion Affects AQS Summaries & Reports AQS Conference, June 2010 19

AQS - Exceptional Data Types AQS computes summaries (daily, quarterly, and annual) that either include or exclude the flagged data and labels each summary with an “Exceptional Data Type” ¨ Exceptional Data Type 0 1 2 5 Result No data has been flagged The summary excludes all flagged data The summary does not exclude any data The summary excludes regionally concurred flagged data Why do we bring this up here? You must be aware of these different summaries when you do your retrievals! ¨ AQS Conference, June 2010 20

Put it into practice - Exercise 1. 1 1. Name the 4 Categories of Data in AQS: 2. How do you Uniquely Define a Site in AQS? How About a Monitor? 3. What Would a Summary Record with a Duration Code of “W” and a Parameter Code of “ 44201” Represent? AQS Conference, June 2010 21

AQS User Interface – getting to AQS & getting around in AQS Standard Reports

Access AQS – Registration & start-up n Registration process n Need user ID and password http: //www. epa. gov/ttn/airsaqs/registration. htm n n n Read-only access is granted to all Update access is available only to authorized users AQS Web Application n Initial setup required http: //www. epa. gov/ttn/airsaqs/aqsweb AQS Conference, June 2010 23

Access AQS Username = 3 -character ID obtained through the registration process Password = your personal password Database = “aqsprod” Today only, Username = 3 -character ID provided by instructor Password = provided by instructor Database = “aqstrng” AQS Conference, June 2010 24

User Interface Parts n n Forms: Presents information to user and accepts input. Reports: Presents formatted data for printing (reports) or input by other software (workfiles) Menus: Allow selection of AQS Form or execution of specific action Icons: Allow execution of a specific action AQS Conference, June 2010 25

Forms: n Forms are the primary way that you interact with AQS. ¨ They provide a way for you to enter data: ¨ They display previously entered data: ¨ They may have buttons to allow you to request actions: ¨ They often have Lists of Values (LOVs): AQS Conference, June 2010 26

Form Types (1) n Some forms are designed to display only one record at the time: AQS Conference, June 2010 27

Form Types (2) n Other forms allow you to view/edit multiple records at the same time: AQS Conference, June 2010 28

AQS Login Form AQS Conference, June 2010 29

AQS Menus (1) n n Allow you to access specific AQS Forms The AQS Main Menu allows access to all forms that are part of AQS: AQS Conference, June 2010 30

AQS Menus (2): n Once a form has been selected, a menu specific to that form is displayed: AQS Conference, June 2010 31

AQS Menus (3): n The Action Menu lets you request specific operations: AQS Conference, June 2010 32

AQS Icons: n AQS Icons allow you to request actions from the Action Menu: n (Other icons will be covered later) AQS Conference, June 2010 33

Logging off AQS n n n Select Exit from the Action Menu or Click on the Exit Icon or Click on the red at the top right corner of the window. AQS Conference, June 2010 34

Browsing Data in AQS Standard Reports

What Can I Browse? n Reference Information Codes Used in the System ¨ Seasonal Definitions ¨ n Data Supplied to AQS in Small Groups of Information Site ¨ Monitor ¨ Sample Data ¨ Summary Data ¨ AQS Conference, June 2010 36

Where Do I Start? “Core” Information Reference What does this Mean? • You can see all “Production” data from Anyone • You cannot change any data AQS Conference, June 2010 37

What’s the big picture? n n Select the Type of Data you Want Get Only the Records You Need by Specifying Filters Execute the Query Scroll Through the Records AQS Conference, June 2010 38

How Do I Do That? n n Execute the Query Scroll Through the Records “Core” Information n Select the Type of Data you Want Specify Filters to limit the Records you get Reference n AQS Conference, June 2010 39

Forms Terminology Tabs Blocks Record w/ Focus Fields AQS Conference, June 2010 Records 40

Form Navigation Previous Block Next Block First Record Brief Definition of the Field with Focus Last Record Previous Record Next Record The 25 th Record of the current block and we Don’t know the Total Number of Records Meeting the Query. Click “Last Record” to get the Total Number AQS Conference, June 2010 41

Put it into practice n Do Exercise 1. 3 AQS Conference, June 2010 42

Read-Only vs. Screening Group Access Standard Reports

I’ve Logged On. . . Now What? AQS Conference, June 2010 44

What is the Difference Between “Read Only” & Screening Group Access? Read Only n n Allows you to Look at Any Data That has Been Deemed “Ready for the Public” You Cannot Change Any Data as “Read. Only” Screening Group n n Allows You to Look at Only the Data You Own (Whether it is Ready for the Public or Not) Allows You to Change Any Data That You Own AQS Conference, June 2010 45

Types of Access Allowed by Session Type of Access Menu Item Read Only Screening Group Admin Action (Any Item) Help (Any Item) Session Admin – Security Admin – All Other Options Retrieval (Any Item) (Extra Reports) Maintenance (Any Item) (Can Update) Critical Rev (Any Item) Certification (Any Item) Batch Correct (Any Item) AQS Conference, June 2010 46

Screening Groups n n n Defines What Group Owns a Monitor Only One Group Can Own a Monitor Only the Data Owner Can Change Data for This Monitor Users Are Assigned to One or More Screening Groups You Must Select a Screening Group in the Session If You Want to Change Data AQS Conference, June 2010 47

In this Class…. n Everyone Has Their Own Screening Group Everyone has their own set of monitors that they can change ¨ “TRAININGGROUPxx”, where “xx” are the Last Two Digits of Your Training ID ¨ AQS Conference, June 2010 48

Status Of Records n “P” = Production Status ¨ n Can be Seen by Everyone “S” = Statistically Evaluated Only for Raw Data ¨ Only viewable by the Data Owner ¨ n “R” = Relationally Valid Only for Raw Data ¨ Only Viewable by the Data Owner ¨ n “F” = Field Level Error Something Needs to be Fixed Before the Record is “Production Status” ¨ Can Only be Seen / Changed by the Data Owner ¨ AQS Conference, June 2010 49

Choosing “Read Only” or “Screening Group” Access n n Upon login Or, anytime during your session ¨ Go to “Main Menu” ¨ Then “Session” AQS Conference, June 2010 50

Updating Data On. Line Standard Reports

What Can I Maintain On-Line? Data Supplied to AQS in Small Groups of Information ¨ Site ¨ Monitor ¨ Sample Data ¨ Precision & Accuracy Data ¨ Summary Data ¨ Comments AQS Conference, June 2010 52

Where Do I Start? What does this Mean? • You can only see data from monitor that YOU OWN • You can change any data that YOU OWN AQS Conference, June 2010 53

Screening Groups Main Security Mechanism in AQS n You Have a List of Screening Groups To Which You Have Access n ¨ Different Levels of Access Possible ¨ Access Defined at the Time You Get Your User ID. Can Be Changed If Needed. n A MONITOR Can Only Be “Owned” by One Screening Group AQS Conference, June 2010 54

Monitor n In AQS, A Monitor Refers To a Sampling For a Single Item At a Site… Not to a Measuring Device! ACME Particu la Measu ring De te vice PM 10 - Total PM 10 – Lead PM 10 – Mercury PM 10 - Nickel 4 AQS Monitors 99 -9999 -81102 -1 99 -9999 -85128 -1 99 -9999 -85142 -1 99 -9999 -85136 -1 Site 99 -9999 AQS Conference, June 2010 55

Where To Go From Here New Options Available!! AQS Conference, June 2010 56

What’s the Big Idea? Select the Type of Data you Want n Get Only the Records You Need by Specifying Filters n Execute the Query n Modify the Records as Needed n AQS Conference, June 2010 57

How Do I Do That? Select the Type of Data you Want n Get Only the Records You Need by Specifying Filters n Execute the Query n Scroll Through the Records, Updating as Needed n AQS Conference, June 2010 58

Form Navigation Previous Block Next Block First Record Previous Record Last Record Next Record Rollback Changes Save Changes Insert Record Delete Duplicate June 2010 Record AQS Conference, Record Clear (Erase) Record 59

Save & Rollback n Rollback ¨ Throws away all changes back to the last time you Saved. You cannot “UNDO” a Rollback n Save ¨ None of the changes you make take effect until you save. AQS Conference, June 2010 60

Demonstration Standard Reports

Recap Screening Groups OWN a Set of Monitors n Signing on With a Screening Group Allows You Into New Areas of the Application n When You Use Maintenance with a Screening Group, You Only Have Access to Monitors You Own n Monitors are Not Physical Devices in AQS: Site – Pollutant measurement combos n AQS Conference, June 2010 62

Put it into practice n Do exercise 2 -1 AQS Conference, June 2010 63

Batch Process Overview Standard Reports

What Do You Mean By Batch Processing? Allows for Mass Entry of Data n All Processing of the Data Happens Without Your Interaction n The System Will Tell You When the Job Has Completed and Provide You With Reports at the End of Each Process n AQS Conference, June 2010 65

What Can Be Input by Batch? n Just About Everything ¨ Site & Site Subordinate Info ¨ Monitor & Monitor Subordinate Info ¨ Raw Data (Regular & Composite) ¨ Precision & Accuracy Data ¨ Blanks Data AQS Conference, June 2010 66

What Cannot Be Input by Batch n Comments ¨ Free-Format Text Describing Special Information for Sites, Monitors, Audits, and Raw Data Values Technical Systems Audits n Event Definitions n EPA Concurrence (For Regional People Only) n AQS Conference, June 2010 67

AQS Batch Data Input Flowchart Staging Tables Load File CDX Submit Correct Data Edit/Load Summary Edit Error Detail Errors N N R = Relational Status S = Stat Analysis Correct Y Production Tables Preproduction Status Raw Y Stats CR Maintain Raw Scan Report Stat Eval Post N Y End Raw Data Inventory Legend Batch Post Production Tables – Production Status Report Online AQS Conference, June 2010 68

What’s the Big Picture? Collect Samples Happens Outside of AQS n Format the Data n Send the Data so AQS Can See it (CDX) n Load the Data (Includes Error Detection) n Correct Any Errors & Reprocess Raw Data n Analyze and Approve Sample Data Only n Make Available to Everyone n AQS Conference, June 2010 69

Where Do I Start? AQS Conference, June 2010 70

Where To Go From Here Files that You have Transferred to CDX List of All Batch Processes run for Screening Group Available Processes Available Reports AQS Conference, June 2010 71

Batch Load: Use CDX to transfer data from their PC to the server 1. 2. Start AQS, Login and select Screening Group. Choose Batch Menu option AQS Conference, June 2010 72 37

Batch Load: Use CDX to transfer data from their PC to server (cont. ) 3. Click CDX button on batch process Screen (CDX is separate tool from AQS) AQS Conference, June 2010 73

Batch Load: Use CDX to transfer data from their PC to server (cont. ) 4. Enter user name, CDX password and Login AQS Conference, June 2010 74

Batch Load: Use CDX to transfer data from their PC to server (cont. ) 5. Select AQS File Transfer AQS Conference, June 2010 75

Batch Load: Use CDX to transfer data from their PC to server (cont. ) 6. Click on Select and Specify files to collect from your computer AQS Conference, June 2010 76

Batch Load: Use CDX to transfer data from their PC to server (cont. ) 7. Click on Send to deliver all files to AQS. Close browser window. AQS Conference, June 2010 77

Batch Load: Use CDX to transfer data from their PC to server (cont. ) 8. After receiving email notice of delivery, click on Refresh CDX to see the newly loaded files. AQS Conference, June 2010 78

OK, We are finished with CDX, so it’s on the server… AQS Batch Data Input Flowchart How do we load it? Staging Tables Load File CDX Edit/Load Summary Edit Error Detail Errors N N R = Relational Status S = Stat Analysis Production Tables Preproduction Status Raw Y Stats CR Maintain Raw Stat Eval Post Y Raw Data Inventory Correct Y Scan Report End Submit Correct Data N Post tion Status AQS Conference, June 2010 Production Tables – Produc Legend Batch Report Online 79

Batch Load: Use Batch Load to Install Data Select file (highlight) to load in CDX block 1. AQS Batch Data Input Flowchart Load File CDX Edit/Load Summary Edit Error Detail Errors Y N N R= Relational Status Raw Y Stats CR S = Stat Scan Analysis Report Stagin g Tables Submit Correct Data 3. Correct Production Tables Preproduction Status 1 Maintain Raw 3 Stat Eval Post Y Raw Data End Inventory Click Load File Wait for email or Refresh in session block for ERROR/COMPLETE status. 2. Post Production Tables – Production Status N Legend 2 Batch Report Online AQS Conference, June 2010 80

Batch Load: Use Batch Load to Install Data AQS Batch Data Input Flowchart Staging Tables Load File CDX Edit/Load Summary Edit Error Detail Errors N N R = Relational Status S = Stat Analysis Possible Results of Load - COMPLETED load - Load with ERROR No data in Staging tables, Summary reports successes Data in Staging tables, Reports successes and failures Production Tables Preproduction Status Raw Y Stats CR Maintain Raw Stat Eval Post Y Raw Data Inventory Correct Y Scan Report End Submit Correct Data N Post Production Tables – Production Status 1 2 3 6 12 60 16 72 Legend Batch Report Online AQS Conference, June 2010 81 41

Batch Load: Use Batch Load to Install Non-Raw Data Load File CDX Staging Tables Edit/Load Summary Errors N N Raw Legend Batch End Production Tables – Production Status Report Online AQS Conference, June 2010 82

Recap n Steps: ¨ If There is No Raw Data and No Errors n ¨ If There is No Raw Data and Errors n ¨ If CDX Load Correct Submit Correct Data There is Raw Data and No Errors n CDX Load Stat/CR Post Status = R Status = S Status = P AQS Conference, June 2010 83

Caveats: n n n A batch load job can end with a status of “WARNING” The only way to see the warning message is to look at the email job log. Example Warnings: ¨ Urbanized n Area Code replaced by Geospatial Lookup For PM 2. 5 you must enter a primary monitor on the Maintain Site Form AQS Conference, June 2010 84

Put it into practice n Do exercise 2 -2 AQS Conference, June 2010 85

Data Formats Standard Reports

Data Formats Overview n 2 types supported ¨ “Pipe-delimited” format 19 different formats; one for each type of data supported through the system n “|” n Formats can be found at: n http: //www. epa. gov/ttn/airsaqs/manuals/ ¨ XML n AQS XML Schema definition can be found at http: //www. exchangenetwork. net/exchanges/air/aqs. htm AQS Conference, June 2010 87

Pipe-Delimited Transaction Types AA AB AC AQS Conference, June 2010 88

Pipe-Delimited Transaction Types (Cont) MB MA MD MC MF ME MI MG MA MK = Monitor Protocols MH MJ MA|I|23|001|0002|44201|1|03|AREA|REGIONAL SCALE||TOP OF BUILDING|4|1|1||Y||2|||||2399|||| AQS Conference, June 2010 89

Pipe-Delimited Transaction Types (cont. ) AA - AC MA - MK RP RD RA RB RS RC AQS Conference, June 2010 90

Pipe-Delimited Transaction Common Fields n n Transaction Type – always the 1 st column Action Indicator – always the 2 nd column I = INSERT ¨ U = UPDATE ¨ D = DELETE ¨ n n n State Code – always the 3 rd column County Code – always the 4 th column Site ID – always the 5 th column AQS Conference, June 2010 91

Pipe-Delimited vs XML Example ¨ AQS Site ID: 01 -001 -9999 ¨ Pollutant Measured: Ozone ¨ POC (assigned as part of the monitor ID): 1 ¨ Does this value already exist in the system? NO ¨ How long did it take to form sample (the duration): 1 hour ¨ What kind of instrument was used? Dasibi 1008 -AH ¨ When was the sample collected? June 10, 2006 (continued on next slide) AQS Conference, June 2010 92

Pipe-Delimited vs XML Example (cont. ) ¨ What time did sample begin (local standard time): 1: 00 PM ¨ Sample Concentration: 0. 050 parts per million ¨ Other qualifiers that you want to apply to data? No ¨ Would you like to specify an alternate Minimum Detection Limit for this sampler? NO ¨ Would you like to specify an uncertainty value with this sample? NO AQS Conference, June 2010 93

Pipe-Delimited Format RD|I|01|001|9999|44201|1|1|007|056|20060610|13: 00|0. 050|||||||| XML Format <Action. Raw. Data. Insert> <Site. Identifier. Details> <State. Code>01</State. Code> <County. Code>001</County. Code> <Site. Identifier>9999</Site. Identifier> </Site. Identifier. Details> <Monitor. Identifier. Details > <Parameter. Code>44201</Parameter. Code> <Parameter. Occurrence. Code>1</Parameter. Occurrence. Code> </Monitor. Identifier. Details > <Transaction. Protocol. Details > <Duration. Code>1</Duration. Code> <Method. Code>056</Method. Code> <Unit. Code>007</Unit. Code> </Transaction. Protocol. Details > <Transaction. RDInsert. Details > <Observation. Date>20060610</Observation. Date> <Observation. Start. Time>13: 00</Observation. Start. Time> <Raw. Value. Insert. Details> <Sample. Value>0. 050</Sample. Value> </Raw. Value. Insert. Details> </Transaction. RDInsert. Details > </Action. Raw. Data. Insert> AQS Conference, June 2010 94

XML Format n As you can see, XML looks like HTML, but with some changes: ¨ The HTML standard defines a fixed set of “tags” that define the data content and formatting. ¨ XML allows you to create your own tags, with the meanings that you define. n XML Tradeoffs: ¨ XML files are “self describing” Conference, June 2010 ¨ XML files are. AQS bigger (100 X bigger, but 95

Data Formats Standard Reports

Data Formats Overview n 2 types supported ¨ “Pipe-delimited” format 19 different formats; one for each type of data supported through the system n “|” n Formats can be found at: n http: //www. epa. gov/ttn/airsaqs/manuals/ ¨ XML n AQS XML Schema definition can be found at http: //www. exchangenetwork. net/exchanges/air/aqs. htm AQS Conference, June 2010 97

Pipe-Delimited Transaction Types AA AB AC AQS Conference, June 2010 98

Pipe-Delimited Transaction Types (Cont) MB MA MD MC MF ME MI MG MA MK = Monitor Protocols MH MJ MA|I|23|001|0002|44201|1|03|AREA|REGIONAL SCALE||TOP OF BUILDING|4|1|1||Y||2|||||2399|||| AQS Conference, June 2010 99

Pipe-Delimited Transaction Types (cont. ) AA - AC MA - MK RP RD RA RB RS RC AQS Conference, June 2010 100

Pipe-Delimited Transaction Common Fields n n Transaction Type – always the 1 st column Action Indicator – always the 2 nd column I = INSERT ¨ U = UPDATE ¨ D = DELETE ¨ n n n State Code – always the 3 rd column County Code – always the 4 th column Site ID – always the 5 th column AQS Conference, June 2010 101

Pipe-Delimited vs XML Example ¨ AQS Site ID: 01 -001 -9999 ¨ Pollutant Measured: Ozone ¨ POC (assigned as part of the monitor ID): 1 ¨ Does this value already exist in the system? NO ¨ How long did it take to form sample (the duration): 1 hour ¨ What kind of instrument was used? Dasibi 1008 -AH ¨ When was the sample collected? June 10, 2006 (continued on next slide) AQS Conference, June 2010 102

Pipe-Delimited vs XML Example (cont. ) ¨ What time did sample begin (local standard time): 1: 00 PM ¨ Sample Concentration: 0. 050 parts per million ¨ Other qualifiers that you want to apply to data? No ¨ Would you like to specify an alternate Minimum Detection Limit for this sampler? NO ¨ Would you like to specify an uncertainty value with this sample? NO AQS Conference, June 2010 103

Pipe-Delimited Format RD|I|01|001|9999|44201|1|1|007|056|20060610|13: 00|0. 050|||||||| XML Format <Action. Raw. Data. Insert> <Site. Identifier. Details> <State. Code>01</State. Code> <County. Code>001</County. Code> <Site. Identifier>9999</Site. Identifier> </Site. Identifier. Details> <Monitor. Identifier. Details > <Parameter. Code>44201</Parameter. Code> <Parameter. Occurrence. Code>1</Parameter. Occurrence. Code> </Monitor. Identifier. Details > <Transaction. Protocol. Details > <Duration. Code>1</Duration. Code> <Method. Code>056</Method. Code> <Unit. Code>007</Unit. Code> </Transaction. Protocol. Details > <Transaction. RDInsert. Details > <Observation. Date>20060610</Observation. Date> <Observation. Start. Time>13: 00</Observation. Start. Time> <Raw. Value. Insert. Details> <Sample. Value>0. 050</Sample. Value> </Raw. Value. Insert. Details> </Transaction. RDInsert. Details > </Action. Raw. Data. Insert> AQS Conference, June 2010 104

XML Format n As you can see, XML looks like HTML, but with some changes: ¨ The HTML standard defines a fixed set of “tags” that define the data content and formatting. ¨ XML allows you to create your own tags, with the meanings that you define. n XML Tradeoffs: ¨ XML files are “self describing” Conference, June 2010 ¨ XML files are. AQS bigger (100 X bigger, but 105

Correct Function Standard Reports

Batch load: Error Process AQS Batch Data Input Flowchart Staging Tables Load File CDX Edit/Load Summary Edit Error Detail Errors N Submit Correct Data Correct Y Result of Load - Load with ERROR Reports successes and failures Production Tables Preproduction Status Legend 1 2 3 6 12 60 16 72 Batch Report Production Tables – Production Status Online AQS Conference, June 2010 107

Correct Process n n CORRECT is a Type of On-Line Editor That Works Against the “Staging Tables” Staging Tables are in the format of the Pipe. Delimited Transactions and are Not the “Real” Table (Because There is an Error that Prevents Them From Getting to the “Real” Table) AQS Conference, June 2010 108

Batch load: Correct Forms There is a form for correcting every type of data input record AQS Batch Data Input Flowchart Staging Tables Load File CDX Edit/Load Summary Edit Error Detail Errors N Submit Correct Data Correct Y Production Tables Preproduction Status Legend Batch Report Production Tables – Production Status Online 1 2 3 6 12 60 16 72 AQS Conference, June 2010 109

Batch load: Using Correct Forms 4 Execute Query 1 This will load all of this type of data in the Staging tables for your Screening Group. If you want to limit selection you must put in selection values before the execute query. Save 3 Read the error message(s) Error messages are the same as in the error details report Find, Analyze and fix the data Many chose to go straight to the Correct screens rather than run a report. Fixing the data often requires knowledge of the nature of the data and may require going back to data sources. The column name is sometimes informative. 2 Exclude unfixed data Excluding unfixed data will keep it from raising error flags during load. AQS Conference, June 2010 110 73

Batch load: Using Correct Forms Deleting Data (cont. ) The highlighted record can be deleted from the form. The record will be deleted from the Staging Table database only after a SAVE. All selected records will be deleted. The records will be deleted from the Staging Table database when the button is clicked. AQS Conference, June 2010 111

Batch load: Using Correct Forms Deleting Data (cont. ) n Delete by Screening group Delete By Screening Group will completely clear ALL records owned by the Screening Group in the Staging Tables. AQS Conference, June 2010 112

Search & Replace n n Not “Search & Replace” as you may know it! Replaces ALL Queried Values in a given column Regardless of the “Current Value” AQS Conference, June 2010 113

Correct Features n n New transactions may be entered using Correct forms After making changes/additions on Correct screen, SAVE changes “Delete All Selected” button will delete according to “Execute Query” selection criteria for the form; it will ignore subsequent changes. Can use “Delete by screening group” to completely eliminate all records in staging tables for your screening group AQS Conference, June 2010 114

Batch load: Submit Correct Data AQS Batch Data Input Flowchart Staging Tables Load File CDX Edit/Load Summary Edit Error Detail Errors N Submit Correct Data n Once data is corrected Submit correct data ¨ Recheck for errors ¨ Correct residual errors ¨ Correct Y Production Tables Preproduction Status Legend Batch Report Production Tables – Production Status Online AQS Conference, June 2010 115

Put it into practice n Exercise 2. 3 AQS Conference, June 2010 116

Post Data Standard Reports

Batch Load: Use Batch Load to Install Site/monitor Data Load File CDX Staging Tables Edit/Load Summary Errors N N Raw Legend Batch End Production Tables – Production Status Report Online AQS Conference, June 2010 118

Batch: Stat CR and Post AQS Batch Data Input Flowchart Staging Tables Production Tables Preproduction Status Raw R = Relational Status S = Stat Analysis Y Stats CR Maintain Raw Scan Report Stat Eval Post Y End Raw Data Inventory N Post tion Status AQS Conference, June 2010 Production Tables – Produc Legend Batch Report Online 119

Stat CR n Stat CR = Statistical Critical Review ¨ n Scan Report ¨ n Evaluation of data to discover possible but unlikely data Results available in Scan and Stat Evaluation Reports Provides information on maximum values and validity flags for raw data Stat Evaluation Report Compares pre-production data to existing production data using Shewhart test, Patterns test, and Gap test Additional information found in http: //www. epa. gov/ttn/airs/aqs/softw/AQSUser. Guide. pdf ¨ n AQS Conference, June 2010 120

Critical Reviews n Once data posted, accessible by public. Stat CR and Post activities help flag data anomalies prior to release n Real errors that fit with rest of data not captured n Anomalies found are not necessarily errors n Stat CR and Post must always be run when raw data is added to or changed in database AQS Conference, June 2010 121

Batch: Stat CR n Simply push button, wait for completion (e-mail/ refresh for notification) AQS Conference, June 2010 122

Batch: Statistical Evaluation Report n Only records that fail a test are reported AQS Conference, June 2010 123

Batch: Scan Report flags - * AQS Conference, June 2010 124

AQS Batch Data Input Flowchart Batch: Post Staging Tables n Post is final process in batch update process n One last chance to verify that data is ready for the world n Production Tables Preproduction Status R = Relational Status Raw Data Inventory Report is available after Post batch job runs S = Stat Analysis Post Y End Raw Data Inventory Post Production Tables – Production Status AQS Conference, June 2010 Legend Batch Report Online 125

Batch: Post form This information is in the Scan Report This information is in the Statistical Evaluation Report Post button on batch form calls-up new form to use to review data AQS Conference, June 2010 126

Batch: Post Form Raw Data Tab Raw Data display allows verification that incidents that “qualify” (explain) unusual value are entered before data is posted to public. Qualifier information cannot be entered in this form AQS Conference, June 2010 127

Batch: Post Form, Raw Data Remember, though aberrations look like “errors” they may be perfectly acceptable data values for posting 128 AQS Conference, June 2010

Batch: Post Form, Raw Data Remember, ALL records belonging to screening group are posted. It doesn’t matter who loaded data AQS Conference, June 2010 129

Batch: Raw Data Inventory Report AQS Conference, June 2010 130

Put it into practice n Exercise 2. 4 AQS Conference, June 2010 131