AQS Web
New User Tramm
HiAII'S ACJS
M»int«n»oc« Poc £ntica( Rtv C£rt*c»»k)fl B*ch CQirad
-------
AQS Basics
Presented by
Jonathan Miller, USEPA
What is AQS?
EPA's Oracle Database Application used
to House and Store Ambient Air Quality
Data
Centralizes the Location of Data
- Used to Determine if areas are Meeting Air
Quality Standard
- Used by Universities and Institutes to Perform
Health Studies
-------
AQS Origins
1970 "An Act to amend the Clean Air Act to
provide for a more effective program to improve
the quality of the Nation's air"
- Complete Rewrite
- Set National Ambient Air Quality Standards (NAAQS)
- Set New Source Performance Standards (NSPS)
- Set new motor vehicle standards ("clean" by 1985)
- Amended
• 1977 extend the motor vehicle and other compliance
timetables and recognized stratospheric ozone problems
AQS Origins (Cont)
For criteria pollutants, the Clean Air Act
establishes levels of pollutants allowed in the air.
Monitoring provides the data for enforcement
and evaluation
What do we do with the Information that is Being
Collected?
-------
SAROAD & PARS
Storage and Retrieval of Atmospheric
Data (SAROAD) Established in 1970
- Ambient Air Quality Data
- Office of Air Quality Planning and Standards
Precision and Accuracy Reporting System
(PARS) Established in 1970
- Determined how well the Monitors were
operating
- Office of Research and Development
1990-AIRS
Aerometric Information Retrieval System
(AIRS)
-Air Quality Subsystem (AQS)
• Combined SAROAD & PARS
- Air Facility Subsystem (AFS)
• Point Sources of Emissions
- Mobile Sources Subsystem (AMS)
-------
2002 - AQS Web-Enabled
• Moved from Mainframe to Oracle Client
Server Architecture
• Allows for More User Control Over Data
Entry Activities
• Reduced Maintenance Costs for EPA
AQS Data at a Glance
Data Table
• Raw data (2005) 81,000,000
• Site 18,000
• Monitor 227,000
• Quarterly Summary (2005) 304,000
• Annual Summary (2005) 115,000
• Precision (2005) 197,000
• Accuracy (2005) 18,000
-------
How Does AQS Fit in the
Big Picture?
Regulate
Analyze _4 Report (Load^
' Store Data ^*
AQS
Components of AQS
Oracle 10g Database
AQS
•Holds all the Data
•All the Processes and Code
•Enforcement of the Rules
•Provides a Way of Looking at the Database
•All the User Interface Modules
CDX
/ Oracle Forms &
Reports
External Application
for File Handling
To & From AQS
-------
Types of Data in AQS n
^ v
Types of Core Data in AQS
Site
- Where in the World You Are
- Identified By
• State & County & Site ID OR
• Tribal Area & Site ID
Monitor
- What You Are Attempting to Measure
- Identified by the Site & Pollutant & Occurrence
Data
- Results of Your Monitoring
- Types
• Sample Data
• Summary Data
• Precision & Accuracy Audit Data
- Identified by the Monitor & the Time the Sample Represents &
Availability Status
-------
Putting The Core & Reference
Information Together
An Ozone (44201) Monitor in Wake
County(183), North Carolina (37) May be
Represented as
-37-183-0001-44201-1
A PM10 (81102) Monitor for the St. Regis
Band of Mohawk Indians of New York
(007) May be Represented as
-TT-007-1234-81102-1
Reference Data in AQS
Lots of "Extra" Information about the Data
in AQS Available
- Sets of Codes Available for these
Descriptions
- Standard Codes Used where Available
Codes are Used to Identify States,
Counties, Tribal Lands, Pollutants, Sample
Lengths, etc...
-------
Parameter Code & Units Examples
Measurement Parameter Method Units Durations
CO
NO
NO2
NOx
O3
SO2
PM10(LTP)
PM10 (STP)
PM2.5- Reference
42101
42601
42602
42603
44201
42401
85101
81102
88101
088
090
090
090
091
092
076
079
079
007 (ppm)
007
007
007
007
007
105(ug/m')
001
105
1
1
.1
1
1.W
1.X.Y
1.X.7
1.X.7
7
method 076 Anderson Beta FH62I-N
method 079 TEOM 1400A
More Common Codes Used
Sample Durations
- 1 = User-Supplied 1-Hour Sample
- 7 = User-Supplied 24-Hour Sample
- W = System Calculated 8-Hour Average
- X = System Calculated 24-Hour Average
Geography
- FIPS State and County Codes
- OIA Tribal Lands Codes
- FIPS Place Codes
8
-------
Where Do I Go for Help?
On-Line
AQS Portion of the TTN
- www.epa.gov/ttn/airs/airsaQS
AQS Helpline
-Phone:(866)411-4372
- Email: epacallcenter@epa.gov
EPA's National Air Data Group
-Phone:(919)541-7738
- Email: miller.jonathan@epa.gov
-------
Components of AQS
Jonathan Miller
USEPA
JTEP
Introduction: The Big Picture
Handle
Data
Report (Load)
Data -^
AQS
10
-------
Introduction: The components of
the AQS System
The data,
e tools we
Introduction: Components
of the AQS System
Components
- Data Data
- Tools we use to manage data
- People who make it happen
11
-------
Introduction: Terminology
• AQS data
• AQS application
• Server
• Database
• Data Table
• Batch
• Interactive
Site
Monitor
Parameter
Raw Data
Composite Data
Precision/Accuracy
Summary Data
Introduction: Identify Components of
the Visible/conceptual Hierarchical
Model of AQS Data
Summary
data
Composite
data
Raw data
Monitor data
Site data
12
-------
Introduction: Other
Ways of Lookingat It
I Sites
C Monitors
[Raw data
tP&A
t Summary data
t Composite data
Monitor | Summary
Data
Introduction: Recognize the Identity Key for
Data According to the Hierarchical Structure
State Examples
06 = California
TT = Tribal
CO Canada
• Site Data
- State CountynYibal Site
- 06 001 9987
• Monitor Data
- State County/Tribe Site Pollutant POC(mstance)
- 06 001 9987 44201 1
• Raw Data
- State County/Tribe Site Pollutant POC Date/Time Statuslnd
- 06001 998744201 1 1998010105:00 R
County/Tnbc/S (ate/Province/...
06001 = Alamcda County, CA
CC 011 = Saskatchewan Provimx
80001 = Agua&calientes State
04017 = Navajo County. AZ
TT 7«0 - Nsvajo Nation.
13
-------
Introduction: Recognize the Identity Key for
Data According to the Hierarchical Structure
• Site Data
- State County/Tribe Site
- 06 001 9987
• Monitor Data
- State County/Tribe Site Parameter POC(instance)
- 06 001 9987 44201 1
• Raw Data
- State County/Tribe Site Parameter POC Date/Time
Statuslnd
- 06 001 9987 44201 1 1998010105:00 R
Introduction: Parameters
Measurement
Parameter
Method Units
Durations
CO
NO
NO2
NOx
O3
SO2
PM10(LTP)
PM10 (STP)
42101
42601
42602
42603
44201
42401
85101
81102
PM2.5- Reference 881 01
method 076
method 079
088
090
090
090
091
092
076,
076,
079
Anderson Beta
TEOM 1400A
007 (ppm)
007
007
007
007
007
079 105 (ug/m3)
079 001
105
FH62I-N
1 (1 Hour)
1
1
1
1,W (8-Hour Avg)
1.X.Y
1.X.7
1.X.7
7 (24 Hours)
-ITEP
14
-------
Introduction: Precision and
Accuracy Data
Data about precision and accuracy of air quality
monitors
Precision Data is "repeatability of measurement
values under specified conditions" Precision
data is KNOWN versus measured indicators.
This precision information can be summarized
quarterly, or created for groups of monitors.
Accuracy Data is measure of "closeness to the
truth"
Introduction: AQS Data
Raw data
- Individual values of pollutant concentrations or
meteorological conditions measured by monitor
- A value may represent
• 24 hour (or more) average "daily"
• Average hourly concentration "hourly"
- Raw data is by far the largest amount of data in AQS
- Be careful when querying raw data: can be very time-
consuming and expensive.
- Usually, Summary data is equally effective, less
costly
15
-------
Introduction: Identify
Components of the AQS System
Components
-Data
- Tools we use to manage data tools
- People that make it happen
Introduction: Identify Tools That
Used to Manage the AQS Data
• AQS
- User can directly enter data into AQS
Database using AQS web interface
• CDX
- User can use Central Data exchange to
transfer data to server for AQS to batch load
16
-------
Introduction: Identify Steps
Required to Load Data and Make
It Available at Production Status
Gather data
Transfer data
Load data into database
Review data
Post data to production
Introduction: Identify Steps
Required to Load Data and Make
It Available at Production Status
Interactive Batch
Gather data * Gather data
Load data in database . Transfer data
Review data .
Post data to Production ' Load data m database
• Review data
• Post data to Production
17
-------
Introduction: Identify Tools Required
to Load Data and Make It Available
at Production Status
Gather data • Gather data
Load data in database • Transfer data
Review data . Load data in database
Post data to Production . Reviewdata
• Post data to Production
AQS Application
CDX
Introduction: Identify
Components of the AQS System
Components are:
- The data,
- The tools we use to manage the data and
- The people that make it happen
18
-------
Introduction: The People
Tribal organizations
Federal, state, and local governments
Researchers (health & university)
General public
850 registered users
buir.ni fm TnUI
Introduction: The People
User management and data
management
Data Owners Everybody
Else
JTEP
19
-------
Introduction: Identify Smart
Working Issues
Don't query nation or decade
OOPS!! use Cancel Report to stop unwanted
reports
Login screen messages and emails-read'em
http: //www.epa.gov/ttn/airs/airsaqs/
archived%2 Odata/downloadaqsdata. htm
web site has much useful raw data ,so don't go
making your own large data extracts
20
-------
About Your User ID & User
Profile
Jonathan Miller
USEPA
AQS Architecture
Different Systems...
Different User Accounts & Logon
21
-------
Registering for a User ID
Get Forms From the TTN
- User Registration
- AQS User Security Guidelines
-www.eDa.gov/ttn/airs/airsaqs/registration.htm
Send Completed Form to Your EPA
Regional Contact
An Account is Set Up on AQS as Well as
CDX as Part of the Process
Logging On To AQS
Username = 3 Character ID
Obtained from the
Registration Process
Password = Your Assigned
Password
Database=?
- Usually = "AQSPROD"
- This Week :"LMTESTn
Usemame }
Password |
Database I"
I Connect [ Cancel |
22
-------
Changing Your Password
Must be Done Every 90 Days (For AQS &
CDX)
After 180 Days of Inactivity, Your User
Account is Locked
Since AQS & CDX Communicate, Their
Passwords Must be the Same
New Password Rules
• Must be at Least 8 Characters Long
• Must contain at least 1 Number
• Must contain at least 1 Character
• Must Begin with a Character
• Can only contain A - Z; 0-9 (no special
characters)
• You Cannot Reuse an Old Password
23
-------
Where to Change Your Password
Step 1: Select "Admin" -» "Security" from the Main Menu
[mi- _j ' ij -20459 Password changed
_
*. p J
B tM
QK
M r11
fr
F
P
Step 2: Fill in the "New Password" & "Confirm Password Fields
AOS
o»
Synchronize Your CDX Password
i1B441 7730
"-J
CMMyCod.
Ounii Vm did.
; J j 20708 Password changed to lower case version of
'^r current Oracla password
Step 2: Click "Synchronize CDX"
Step 1: Enter "Old" CDX Password
24
-------
About Your User Profile
Cannot Change:
- Agency Code
- User ID
- User Type
- AQS Contact
- Status Ind
Tribal User
- Determines How you
see AQS Site IDs
Tribal User "ON"
TT-549-0001
Tribal User "OFF"
36-001-0001
25
-------
Student Exercise 1.1
Day 1 - User Profile
Purpose: Access and change information associated with your user id
Step 1. Start AQS & Logon* using your training user id and password
Step 2. From the menus select Admin > Security
Step 3. You should see a form filled out with information pertaining to your user id
Step 4. Change the "First Name" and "Last Name" fields to reflect your name (feel
free to change any of the other information on the form as well).
Step 5. Click on Save H
Step 6. Click on Exit-IP-. Click "Yes" when prompted if you are sure that you want
to exit.
instructions for Accessing AQS for the Training Course
1. Open Internet Explorer
2. Click on "Favorites"
3. Select "AQS Training Application"
4. Enter your Training User ID (Located on the sticker on your materials) in the "Username"
field. Press the key.
5. Enter your Password for the Training User ID (also located on the sticker on your
materials). Press the key.
6. Enter "LMTEST" in the "Database" field. Press "Connect" button.
Questions:
1. Name the three components that comprise AQS:
2. What is the primary purpose of CDX as it relates to AQS?
3. How often do you have to change your password for AQS?
4. Which of the following is a valid password for the AQSPROD connection? (Circle one):
a) 4tunatel b)aqsprodl c)aqsisgr8 d) carolina_panthers
26
-------
Browsing Data in AQS
Jonathan Miller
USEPA
What Can I Browse?
Reference Information
- Codes Used in the System
- Seasonal Definitions
Data Supplied to AQS in Small Groups of
Information
-Site
- Monitor
- Sample Data
- Summary Data
27
-------
Where Do I Start?
gclon Help Session Aamin
What does this Mean?
•You can see all "Production" data from Anyone
•You cannot change any data
BB«it Monitor
BiOJJ" gun
Brett" E«w»
raBE PBK
-£• Accuracy
Coimxntt Data
Qtwte PragiMuctnm Data
£D) SUmmaiy
MaiOle concurrence ma
What's the Big Idea?
Select the Type of Data you Want
Get Only the Records You Need by
Specifying Filters
Execute the Query
Scroll Through the Records
28
-------
I Tabs |
Forms Terminology
v-v-.\—i---/-
Form Navigation
.« T < > *
Bnef Definition of
the Field with
Focus
Entei a Sate FIPS cooa mn riintaui ont ofnw so tan a omtr counuc
"Record ZS/7_ «CMC»
The 25th 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
29
-------
Student Exercise 1.2
Day 1 - Browsing Data in AQS
Purpose: Look at various types of Data available in AQS
Parti
Step 1. Start AQS & Logon using your training user id and password
Step 2. From the menus select Retrieval > Browse Annual Summary
Step 3. You should see an empty form
Step 4. Enter the following Query selections:
• State = North Carolina (State "37")
• Parameter = Ozone ("44201")
• Year = 2004 . . r . &
StepS. Execute the Query—- h^ ^ *v:->;-W*- &
Questions:
1. What is the total number of records returned by the query outlined in "Part 1"?
2. What is the 4th Highest 8-hour Running Average (Duration = W) Value for site 37-063-
0013-44201-1 for 2004?
Part 2
Step 1. Select Maintenance > Site from the menu
Step 3. You should see an empty form
Step 4. Enter the following Query selections:
• State = Georgia (State " 13")
• County = DeKalb ("089")
Step 5. Execute the Query
Questions:
1. What is the total number of Sites registered in DeKalb County?
2. What does the field "LDP Vert Method" mean?
3. What is the Supporting Agency code associated with Site 13-089-0002?
30
-------
Parts
Step 1. Start AQS & Logon using your training user id and password
Step 2. From the menus select Maintenance -> Sample Value -> Raw Data
Step 3. You should see an empty form
Step 4. Enter the following Query selections:
• State = Pennsylvania (State "42")
• County = Allegheny (County "003")
• Parameter = Ozone ("44201")
• Begin Date = 20040101
• End Date = 20041231
Step 5. Execute the Query
Questions:
1. How many blocks are on the Raw Data form?
2. Can you tell how the data is sorted? Describe:
3. What method code is associated with the first record that you can find?
4. **BONUS**: What does this method code mean / where would you go to find information
out about this method?
31
-------
Introduction to Standard
Reports
Jonathan Miller
USEPA
Reports
Browse Reports ^EDa™- e—"•£• i
' T> jm*mm i i
Standard Reports MEB is^.'.^l^L LH
- Create standard reports ' *°«»>s"°<*»"*«*'*w
- Report Criteria Set
- Types of Reports Available
• Extract Reports
• Summary Reports
• Raw Data Reports
• Site / Monitor Description Reports ,
_ - NCE.KEASEOIU.nC8WCXU.CBnB>*!
- Report formats
• Printable Formats (.rtf, .pdf, etc)
• Workfiles (Text files)
• XML
32
-------
Standard Reports: Create
Standard Reports
1. Select report
2. Specify Output type (Report, Workfile, XML)
3. Establish report-selection criteria
4. Modify report Sort Order criteria where
available
5. Modify output Report Options where available
6. Generate Report
7. When you leave Reports form, prompt to save
report Criteria Set.
Standard Reports
CnumSn JMomlof Selection | Are; Etttrku. 1 5WQi*' | Report Options | Rnnne Reports |
o—Mmv P««» TmFWAre _ _j
Deport OUmM
rsrtu>o» rXML """"'
Report SelacdonUodo
r oaSiMain
33
-------
Standard Reports (cont.)
Catena Set [
sue
Cede,
-J
r
( <
i ,
j •
r~ll
MaMoi Selection:) - _ s« , '01 Son Outer Repon Opna | Reineve Reports |
Slte-aonlBir Criteria
tarty Stt Hnaav H9a CUOBn StMDn EndCK.
CM. u cod. me end. ud. YVYV w CD vwv MM to
-
1 —1
•
J
:- |— | i — .1 III
L 1
1 |
j 1 i
' |
i 1 >
I i
Jr~i I!' J i i i i
Global Rapon Criteria Global Oalo Ranga
St«10«i EndOM
a^... ,, nmau yyyy MI GO VWV IM DO
'W'rl? .. ="•> MBBOdCod. KnBnCM. 1 1 1 1 1 1
n:
i
!
— ^
,
,
"ftinar,
* '•' ^ — r-r- — r~r-
i Global Seraanlng Group
J L J 11 1 !iJ
MaRipon
Standard Reports (cont.)
CnenaSal 1 htoiuot Selecum|| ^•saSHr-iun
Intal sue Gouty St. F
COM Co« Onto u c
_• ,- j ;.
, |
, I",
C:
H'
— r r^~ r~~
J J I^LJ i d
~
— 1
S
kraa
BdB
he.H
3er
-r
1
I
1
u
Global Ripon Grill
]"
- ;
i
L
i
(Mw
^
aiaF
SottOnfn | RvponOpmns
onNar Criteria
•era cum> Sanaa
KC CodB Codg VWV
j-
,
]
1
j
rji J
irla ! Gl<
r^,. p^ij =^r
1
(•port
1 GIB
_J
| Retneve Rvponv
—
—
GMOtU
DO WW M
—
—
.
M CO
-p:
F~-
ibal Data Range
EMO«
DO WW W CO
:F FFF
lal Scraaelng Group
d
34
-------
Standard Reports (cont.)
Criteria Set | l'-w'.f"x'-n Area Selection | SortOroer | Report Opnora | Retrieve Reports |
TltjU !
*l - r
z _ r
we Outr So
ode Code u
-
_ _
SSH*»Tvpe
.±J
J
1)
POttl
T
•
Zl
'-I
'
. J
-
_l
Ooognp
en
coa
1
I_
MulCrmi
AOCR
'
1
'
1
1
LJ
LMdUwTypii
3J
d
J
Protocol cncorta
rtlVPB Cod!
-1
J
3
a
pa. o
I—
r
Hi
SierateRei
KO>
Ida
-i
suit
1i
UAR MSA Ck
Coda Code Ce
|
1
1
1
1 .
1 J _]l
•^ '
J 1
3 "
* So.""*"
1 ,
1
1
i
teporti»a OomxaAFi
^1
13
m
Oito CrtUrti
SU1D.U CndtWa
VVW MM 00 WW IM DO
H ! !
3 i i
RFF
___ Scraonlng Group
i .""* ^ ___ _ _ ._ j
Standard Reports (cont.)
CraeruSet |^'r^io-*••>•?tt \ AnaSelectren I SenOrttr || Report Opuont | Rnn«»»Bepom |
Son Oidar
ptAre.coce
'ARAMETER.CODe
*l"
Bcietaa Rapoii | ~R«tlor« R»pon Pefaulu'
35
-------
Standard Reports (cont.)
Cratna Sa \ ,'jrtui -- -r^i 11 Am Sefeaen | Son Outer j Recoil Oplgm | Panaa Repots |
noiBCMjus
GM.Y SUTBnCS
IMIS
R/wvoMAEvans
ALTERNATE STAHMROS
1-
J
i
r"
i.
1
i_
i
i i
i i
i i
i i
•J
• .1
Report Progrea
12 tacnCWiMi
Rnonsagt |Ei:r.£6
Cancel Rgpod | Eat Report PiognM
Gamrata Repoil
Standard Reports (cont.)
__ Crtena Srt | Mrnrfo- eir »*••
JL am
OB—
HMP350
JVMP501
»r;| Area Salaclionn| _J5gi1.0Tdar ___ |^ Rapotl Optnnc
ONUNE (03190000409 25PM |Cancri««
ONUNE (03/30/2004 07 25 PM ICompllIld
Rurien Repeiti |
ilWO
(100
iir
IAMP440
BATCH
JAMP2EO
'IS
=ES
fciT
S*
B
B
B
B
B
B
B
B
B
B
B
axKiit Vtert
IKH6-I prft
iHodTnl
JIB.aiCMfrZprf
11/1/2005 < 02 PH
ll/l/20GB40)m
1.665.91'
pdKMOfta,0 bytes
36
-------
Extract (AMPSOn) reports
Reports useful for duplicating and reloading
data (especially duplicating site/mon)
Reports create data files in batch load format
- AMP500 - Extract Site/Monitor Data
- AMP501 - Extract Raw Data
- AMP502 - Extract Precision and Accuracy Data
- AMP503 - Extract Blanks Data
Extract Reports
Open Standard Report form under Retrieval
pulldown menu
Select an Extract report from Report Code LOV
Select Online or Batch for output
- If batch, select CDX or e-mail
Fill in Filename field
Go to Selections tab and specif
criteria in appropriate fields
Click on Generate Report buttoh;
37
-------
Standard Reports:
General Issues
Do NOT run reports without specifying some
limiting selection criteria
Almost all reports require date-selection criteria
Sort Order and Report Options available on
reports where data structure allows
Oracle provides output in several formats. PDF
generally most reliable.
Do not use IE window that started AQS for any
other purpose. If not blank gray, reports will not
run properly
Standard Reports: Batch/Online
• With web delivery, no appreciable
difference in cost of delivery between
online and batch creation of reports.
Delivery method is only decision
- Online: Web browser window display, save
from browser window
- Batch/CDX: Separate CDX transfer of report
to local disk
- Batch/email: Email delivery of report as Link
38
-------
Standard Reports: Criteria Set
CrMrnSel | Moiuor Sghctnn \ -piSeimr | fw.'rtu \ Report Options | Retnm Reports |
ILABRY
iyp.|prav*rc
jJUmnttmlWOlMUTYMCmgORT ' ' ...... '",
FREPOHT
r WORKFILE
TXML
r
HnFBMfVr
FKUaral
Report Seledion Mode
tf Monte SdfldfH(ddfll)
~ Generate Rapott I ICOXlo Rumra BaSTRipSnTJ
Standard Reports:
and/or Selection Logic
A and B or C
39
-------
Standard Reports:
and/or Selection Logic (cont.)
A and (B or C)
Standard Reports:
and/or Selection Logic (cont.)
• A or B or C
40
-------
Standard Reports:
and/or Selection Logic (cont.)
• A and B and C
Standard Reports: and/or in
Selections Tab Rows/Blocks
• AND within a row
- State and county AND site AND parameter.,
- Parameter AND method AND duration...
• OR between rows in block
- State/County OR State/County or...
• AND between blocks
- State AND Pollutant AND date...
41
-------
Standard Reports: and/or in
Selections Tab Rows/Blocks (cont.)
• All monitors in Iowa reported by University
Hygienic Laboratory during January '98
• State 19 and reporting agency 1080 and
all pollutants and (>19980101 and
<19980131)
Standard Reports: and/or in
Selections Tab Rows/Blocks (cont.)
• All criteria monitors in New York or New
England during January '98
• (State 36 or region 01) and (only criteria
pollutants) and (>19980101 and
<19980131)
42
-------
Standard Reports: and/or in
Selections Tab Rows/Blocks (cont.)
• Ozone or sulfur dioxide monitors in New
York and Region 1 during January '98
• (State 36 and region 01) and (44201 or
42401) and(>19980101 and <19980131)
• This is a Null dataset, "No Data found"
Standard Reports: Selections
i
Cmwn Set \ !
3 .d
sue CWly
Cede Co*.
-
1
1
l^i
_ r^L
P1
ndr^d
-U r-i
\
J
! I
J
^ gngraphi^^ltoria |W| ^^
U Coda Onto Coda Coda
)-
|
— di —
r-
(
r.
i
•DI — i
^1 -1
\
J
LJ
d
Protocol Crinrie.
tomte ugna Ouem
PaUenlype cot* eat* Co*
-M
|
J
jj
Pll
Ul
HI
iJI
y
Jz
•
-
: i
:l
:i
]l d
-^1 ' —
1 i
J I
CMSA
Cede
j-
i
L
| 1
2T--
i
1
i
•
^
q
.d
SUM Me * ISftlae
SewnrgGnMiNM50™"1"' °""Ip
i
43
-------
Standard Reports: Sort Order
CnenaStt | "a-fff Zt it • >r | AraaSelicun I SonOrta !| Etepon Opium | ReimnPepons |
SoitOfdn
Onto GckjmN
1 pIATt.COOe
I (OOMTV.OaOE
i prc_p
1 pARAIEIER.COM
roc
\
h
*
h
1'
h
1
r^
s
s
mi NtfigMo aun dmn
Gm«gt« RtpoH I '
Standard Reports: Report Options
. CrtenaS«__|i
nourcNuas
MLVSnTISIKS
INIS
RNMM1AEVBITS
ALTERNATE STAMMRDS
__|oit Orter I Remrt OpUoin
~~—EJ
C«n»ral» Rppan
44
-------
Standard Reports: Progress Popup
•s Report Progress
HHB
Report Progress
8 Percent Complete
Report Stage |FormaSngRepor
|| Refresh l| Cancel Report | Exit Report Progress j
Standard Reports: Retrieve Reports
CraemSei |i1ru»'V!*l AnsS«l«aion | Son Outer | Report Opnnc , Riuww Reports |
*
-"
SIS
ais
&MM35G
AMP501
ONUNE
ONUNE
3IB IAMPSOO IONUNE
3ia (AMP440
BIB (AMP440
BIB
BIB
BIB
BIB
AMP440
AMP440
AMP440
BATCH
BATCH
BATCH
BATCH
BATCH
AMP440 (BATCH
BIB (AMP440 (BATCH
BIB (AMP440 (BATCH
BIB
BIB
BIB
AMP2EO (BATCH
AMP260 (BATCH
AMP260 (BATCH
BIB (AMP260 (BATCH
Mn»f
*•» 1
teOHOMOeXPM
(03^0/2004 07 19PM
1000/200411 17AM
103/30/2004 11 17AM
10300/200411 17AM
103/30/200411 17AM
103/30/200411 17AM
CanuOid
Complelid
Comoleled
Completed
Complnod
Completed
too
100
100
ioo
100
100
Completed (100
Completed (100
(03/30/2004 11 17AM|Compl«ltd
103/30/2004 11 17AM
103/30/200411 17AM
103/30/2004)1 16AM
(03/30/200411 IEAM
(03/30/2004 11 16AM
(03/30/200*11 16AM
Completed
Completed
Completed
100
100
100
100
Completed (100 '
Completed (100
Competed
RMrohGwy | 1 CmrtRlwl | tMaHIMuol |
iqp
45
-------
Standard Reports: Create Different
Report Output Formats
CnienaSei I Mtuutoi Selection | <-*•) zt IOMT
RipmOpinm | Reuen Repone |
Citmsa |
TypepHVAIE
BeponCoM |.uwo
rnno
r SCMUCDX
Report Selection Mode
g«M»Silnrl»»(dg«l)
Gmerafe R>
HTML
PDF
PS
RTF
TEXT
XML
HP Pnntei Language
Hypu Tin Maikup Language
Adobe Portable Document Formal
Postscript
Rich Text Formal
Ten File
XML out put formal
Cancel
Saving a Criteria Set
Saves the Query... Not the Results of the Query
Go to the "Criteria Set" Tab
1) Enter a Name
2) Enter a Description
3) Mark as
- "Private" (Just for You)
- "Public" (For Anyone)
4) Save / Commit
S.
Cmena Set | Monitor Selection |
|
I Report Opinm | Reineve Reponi |
P
> Out
OMB (JONATHAN tOUBt
46
-------
Student Exercise 1.3
Day 1 - Standard Retrievals
Purpose:
• Generate a Standard Report from the system
• Create and save a criteria set for later use
Part 1 - Run the Quick-Look Report (AMP450)
Step 1. Start AQS & Logon using your training user id and password
Step 2. From the menus select "Retrieval" -^ "Standard Report Selection"
Step 3. Enter (or select from Drop-Down) "AMP450" in the "Report Code" field
Step 4. Click on the "Monitor Selection" tab
Step 5. Select at least 1 state and one county in the "Site Monitor Criteria" section;
Begin year of 2005; End year of 2005
Step 6. Select "Criteria" under the Pollutant Type in the "Global Report Criteria"
section
Step 7. Click on the "Report Options" tab
Step 8. Select "Yes" under Merge PDF files
Step 9. Generate the report
Step 10. When the report comes back, review the report
Step 11. Close Acrobat.
Step 12. Click on the "Criteria Set" tab
Part 2 - Generate a Raw Data Listing Report & Save
Selection Criteria
Step 1. Enter (or select from Drop-Down) "AMP350" in the "Report Code" field;
Make sure both "Workfile" and "Report" output types are checked.
Step 2. Click on the "Monitor Selection" tab
Step 3. Select State = 51; County = 510; Site id = 0009; Begin Date = 20050101; End
Date = 20051231
Step 4. Select "Criteria" under the Pollutant Type and "44201" under the Parameter
Code in the "Global Report Criteria" section
Step 5. Click on the "Report Options" tab
Step 6. Select "Yes" under Merge PDF files; Change the Daily Statistic to "Mean"
(also answer Question #1 while you are here)
Step 7. Generate the report
Step 8. When the report comes back, review the report (both the workfile and the pdf
versions)
Step 9. Close the report.
Step 10. Click on the "Criteria Set" tab
Step 11. Enter "Training Sample CS" under the "Criteria Set" field; Enter "This
is a test criteria set" under "Desc".
47
-------
Step 12. Click "Commit"
Step 13. Click on the "Session" selection from the main menu
Questions;
1. In the Report Options screen, what do you think the "Alternate Standards" section of the
report means?
2. What is the difference between saving criteria set as "PRIVATE" vs. "PUBLIC"?
3. In the workfile, what do you think the "#" symbol means in the first few lines of the
workfile?
Part 3 - Generate a Report from a Saved Selection
Criteria
Step 1. Select "Training Sample CS" from the Criteria Set Drop-Down
Step 2. Generate the report
Step 3. Click on the "Exit AQS" Icon
Step 4. Select "No" when it asks you if you want to commit your changes
Part 4 - Generate a Report from a Public Saved
Selection Criteria
Step 1. Select "Public" from the "Type" ("PUBLIC" vs "PRIVATE")
Step 2. Select "Jons Class Sample" from the Criteria Set Drop-Down
Step 3. Generate the report
Step 4. Click on the "Exit AQS" Icon
Step 5. Select "No" when it asks you if you want to commit your changes
48
-------
On-Line vs. Screening Group
Access
Jonathan Miller
USEPA
Yea! I've Logged On....
Now What?
49
-------
The Three S's
Session
Screening Groups
Status
AQS Terminology - Session
A Session is a Set of Information the System
Assigns After You Log On
Your Session is Defined by:
- Who you are
- When you logged on
- If you selected "Read-Only" Access or Access a
Screening Group
You Session Settings Define Where in the
Application You are Allowed to Go and What you
Can Do Once You Get There
50
-------
What is the Difference Between "Read
Only" & Screening Group Access?
Read Only
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
• 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
Types of Access Allowed by
Session Type
Menu Item
Action (Any Item)
Help (Any Item)
Session
Admin - Security
Admin - All Other Options
Retrieval (Any Item)
Maintenance (Any Item)
Critical Rev (Any Item)
Certification (Any Item)
Batch
Correct (Any Item)
Type of Access
Read Only
•/
S
•/
•f
jc
•
•f
1C
11
1C
*
Screening Group
•
•
'
'
«
'(Extra Reports)
' (Can Update)
«
*
'
'
Admin
•
•
'
'
'
•
'
•
'
'
'
51
-------
Screening Groups
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
In this Class....
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
52
-------
Status Of Records
"P" = Production Status
- Can be Seen by Everyone
"S" = Statistically Evaluated
- Only for Raw Data
- Only viewable by the Data Owner
"R" = Relational^ Valid
- Only for Raw Data
- Only Viewable by the Data Owner
"F" = Field Level Error
- Something Needs to be Fixed Before the Record is "Production
Status"
- Can Only be Seen / Changed by the Data Owner
What If I Want to Change From "Read
Only" to "Screening Group" Access?
From the "Main Menu"
Action ben> Session Admin Beweral Maintenance Critical Kn CEnncaton Baicn CQrncl (
• From a"
Sub Menu"
Acgon U>lD S«a Monu Audit Enm SamEUValuaa PBKinon Accuracy Banks summary CQncurtance Ind Main Menu
tr
taan Blip Session Admin Betneval Halnunancc CnucalRn CEftmcanon B«cn CQnecl
tr
L
53
-------
Screening Groups and
Updating Data On-Line
Jonathan Miller
USEPA
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
54
-------
Where Do I Start?
pS;> _i. i. >J J'.-J 3 :.'". •"• ""_' . .. . - ... 1-iPIT.I
|. ||3 ^Ti: BrioTxl
CRiMOririfcct
Selicl > Saoonlng Granp lor thb a
lOrewKean i;
l'T
i !i |»0'»miO"iMmoaenbl |«nai i
I'M tusauuivan l»ig> ,
What does t/i/s Mean?
•You can only see data from monitor that YOU OWN
•You can change any data that YOU OWN
IOOK fOK MBAB5 TO Tm^ertli «i«) i
Screening Groups
Main Security Mechanism in AQS
You Have a List of Screening Groups To
Which You Have Access
- Different Levels of Access Possible
- Access Defined at the Time You Get Your
User ID. Can Be Changed If Needed.
A MONITOR Can Only Be "Owned" by
One Screening Group
55
-------
Monitor
In AQS, A Monitor Refers To a Sampling
For a Single Item At a Site... Not to a
Measuring Device!
PM10 - Total
PM10-Lead
PM10- Mercury
PM10- Nickel
4 AQS Monitors
99-999-9999-81102-1
99-999-9999-85128-1
99-999-9999-85136-1
Site 99-999-9999
Where To Go From Here
Action Help Session Admin Retrieval
||CQrrect|
Site
Monitor
Audit
Event
New Options
Available!!
^^^^^u^^^9^E Saw Data
PBecision Composite Data
Accuracy Qeiete Reproduction Data
Blanks
Summary
CQncurrence Ind j
Main Menu
56
-------
What's the Big Idea?
Select the Type of Data you Want
Get Only the Records You Need by
Specifying Filters
Execute the Query
Modify the Records as Needed
How Do I Do That?
•
Select the Type of Data you
Want sen,
EEsBESEBE gawoau
Get Only the Records You ""~
Need by Specifying Filters
^^^*h ^^ • •• V M 1^^ • ^^*%XXXI %•• \* • ^^%H
Blank!
Summary
cgncunnu Ira)
na|n Demi
Execute the Query
Scroll Through the Records,
Updating as Needed
57
-------
Form Navigation
.<<
Commit /
Save
Changes
Clear (Erase) 1
Record J
Commit & Rollback
• Rollback
- Throws away all changes back to the last time
you Committed. You cannot "UNDO" a
Rollback
• Commit
- Can be thought of as a "Save". None of the
changes you make take effect until you
COMMIT.
58
-------
Demonstration
Recap
• Screening Groups OWN a Set of Monitors
• Signing on With a Screening Group Allows
You Into New Areas of the Application
• When You Use Maintenance with a
Screening Group, You Only Have Access
to Monitors You Own
• Monitors are Not Physical Devices in AQS:
Site - Pollutant measurement combos
59
-------
Student Exercise 2.2
Day 2 - Creating Records On-Line in AQS
Purpose: Use the On-Line Maintenance Forms to create new site and monitors.
Part 1 - Create a New Site
Step 1. Start AQS & Logon using your training user id and password
Step 2. Select your screening Group
Step 3. From the menus select "Maintenance" -> "Monitor"
Step 4. Make sure you are in "Tribal" Mode (Turn Tribal Mode "ON" using the
"Action" -> "Turn On Tribal Mode" from the menu items)
Step 5. Click on the "Cancel Query" icon
Step 6. Create a new site with the following information: (If a field is not specified,
leave it blank)
Tribal Code: 001 (Eastern Band of Cherokee Indians of North Carolina)
Site: xxOl; where "xx" are the last two digits of your training id. So T50 would use
a site ID of 5001 for example.
Site Latitude = +36.4136
Site Longitude = -76.758
LDP Coll Method = 012
LDP Horiz Datum = NAD83
LDP Scr Scale = 10000
LDP Acer Value = 10
LDP Vert Meas = 50
LDP Vert Ace Value = 10
LDP Vert Method = 001
LDP Vert Datum = NAVD88
Street Address = Test Site for Training Class
City Code = 25680 {Use the Drop-Down to Pick}
UA Code = 0000
AQCR Code = 168 (Use the Drop-Down to Pick}
Land Use Type = Agricultural
Loc Setting = RURAL
Site Estab Date = 20060101
Time Zone = EASTERN
Step 7. Click on the "Agency" tab and enter the following information:
Agency Code = 001
Role = "SUPPORTING"
Begin Date = 20060101
Step 8. Click on the "Site Basic" Tab
Step 9. Click on the "Create Monitor" button and continue with "Part 2"
-------
Part 2 - Create a New Monitor
Step 1. Enter the following information in the "Monitor Basic" tab:
Parameter: 44201 (Ozone)
POC: 1
Probe Height = 3
Horizontal distance = 0
Vertical distance = 2
Probe Location: Top of Building
Step 2. Click on the "Sample Periods" tab
Step 3. Enter a Begin Date of "20060101"
Step 4: Click on the "Type Assign." Tab
Step 5: Enter a Monitor Type of "Special Purpose" and a Begin Date of "20060101'
Step 6: Click on the "Agency Roles" tab
Step 7: Enter an Agency Role of "Reporting"; Agency Code = 0776; Begin Date =
"20060101". Repeat this step for agency roles of "COLLECTING" and
"ANALYZING"
Step 8: Click on the "Objectives" Tab
Step 9: Select "GENERAL/BACKGROUND" for Monitor Objective Type; MSA
Represented = "6640" (Raleigh-Durham-Chapel Hill)
Step 10: Click the "Commit" icon.
Step 11: ExitAQS.
Questions:
1. What do you think it means when we leave the "End Dates" on these tabs
blank?
2. Please let us know if there were any confusing issues with entering this
information:
61
-------
Batch Process Overview
Jonathan Miller
USEPA
What Do You Mean By Batch
Processing?
Allows for Mass Entry of Data
All Processing of the Data Happens
Without Your Interaction
The System Will Tell You When the Job
Has Completed and Provide You With
Reports at the End of Each Process
62
-------
What Can Be Input by Batch?
Just About Everything
- Site & Site Subordinate Info
- Monitor & Monitor Subordinate Info
- Raw Data (Regular & Composite)
- Precision & Accuracy Data
- Blanks Data
What Cannot Be Input by Batch
• Comments
- Free-Format Text Describing Special
Information for Sites, Monitors, Audits, and
Raw Data Values
• Technical Systems Audits
• Wide-spread Event Descriptions
• EPA Concurrence (For Regional People
Only)
63
-------
AQS bub Ota Iffm Flnch»n
What's the Big Picture?
Collect Samples
Format the Data
Send the Data so AQS Can See it (CDX)
Load the Data (Includes Error Detection)
Correct Any Errors & Reprocess
Analyze and Approve Sample Data
Make Available to Everyone
Raw Data
Only
64
-------
Where Do I Start?
Selicl « Semiring Group for Ihh iralon
RmOrtvUsn
• Smmr.gOavAcm,
afOKMATON MAMAOEtSJI 0 ytftfC
lwet> NBWUM3-1S »e
M
0". I
t*C«l.Ai14Ee-«1-U7nibn COtav t JCtttEKtep
Where To Go From Here
Action Help Session Admin Retrieval Maintenance QnticalRev CErtiflcation | Batch) CQrrect
13 fFiles that You have"! ^^
Fn-»«.a>x ,^t.L««i Transferred to CDX
FteCift 1 1 3MB
-il ^--^ ^ ^
S'^f^"*
-ll
[List off All Recentlv ^1 ^^ m ** -T"1"
Run Batch Processes *j&~w *** ^-»«Jt*w_____ __
^»»«.« «. »« ".T«LH [" "J Available
f Available Reports 1 vMumM ; •»-« ».
EWuMCumn UCnOm
1
©
65
-------
Batch Load: Use CDX to transfer
data from their PC to the server
1. Start AQS, Login and select Screening Group.
2. Choose Batch Menu option
fiction tjelp Session Admin jjetneval Maintenance EOC Critical Rev certification
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)
FUnfmrcOUC n*y»
f«rO«.
66
-------
Batch Load: Use CDX to transfer
data from their PC to server (cont.)
4. Enter user name, CDX password and Login
Password PiMS*r»-
you Us* Namt and PassMora
pfxl
Batch Load: Use CDX to transfer
data from their PC to server (cont.)
5. Select AQS File Transfer
^ i 6 & « >a i a- £» a.*
igji. I «~ai r«.»» u^i Httf ,|, ..la >•» S
67
-------
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
IREMOT- I
I OEM |
-en
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.
3)1-* •
US ENVIRONMENTAL PROTECTION
I AGENCY
I «AOUMJlY8ua8VSTEHSUBHinM.rORII
Y«u uMf tarn iccesi ftc web cubbd AOS Apcteiao-i to nfcdtt nd bid ftc folded diu tfo tte
AQSSynon
Flute nox dm dxn nor be • bmf ddv »ii!t»«» Gc u ptocmed ad udt ndiblc u AQS
68
-------
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.
' FUn«n»COX m*»l
, Menu
aOHMtti V IttXltM
m
-=*""""" "I
ij
I :r ~ i ''
~j '" TuT^v?-""1 m-1»»»», ~|
OK, We are finished with CDX,
so it's on the server...
How do we load it?
AQS B«ub D»l» Input Flo»clan
69
-------
Batch Load: Use Batch Load
to Install Data
AQS toicb Dm lapel Fl»»cb»n
Select file (highlight) to load in CDX block
Click Load File
Wait for email or Refresh in session block for
ERROR/COMPLETE status
Batch Load: Use Batch Load
to Install Data
AQS Bllcb Dill Ippm FlOTChMl
Possible Results of Load
- COMPLETED load -TN° data ln Stasinstables-
LSummary reports successes
- Load With ERROR ("Data in Staging tables,
~l_Reports successes and failures
Air Qiiglily Sutnyslem
EdULom Summary Report
Air Oimliti Suh&\stcm
KdltEmrRcpm
70
-------
Batch Load: Use Batch Load
to Install Non-Raw Data
AQS B.llh DMD Input Flimclnn
Recap
Steps:
- If There is No Raw Data and No Errors
• CDX -> Load
- If There is No Raw Data and Errors
• CDX -> Load -> Correct -> Submit Correct Data
- If There is Raw Data and No Errors
• CDX -> Load -» Stat/CR -> Post
Status = R
Status = S
Status = P
71
-------
Data Formats
Jonathan Miller
USEPA
Big Picture
Formats of the Records "Tell" AQS What
You Want to Do
Each Record Will Be Able to Answer the
Following Questions:
- What Type of Data Are You Talking About?
- Is This New Data or Are You Correcting
Existing Data?
- What Specific Record Are You Talking About?
72
-------
Data Formats Overview
3 Types Supported
- "Pipe-Delimited" Format
• 19 Different formats; One for Each Type of Data
Supported Through the System
. -r
• Formats Can be found at:
http.//www.epa.gov/ttn/airs/airsaqs/manuals/
-XML
• AQS XML Schema Definition Can be Found at
http://www.exchangenetwork.net/exchanges/air/aqs.htm
- 80 Character Fixed Length File format ("AIRS" Days)
Pipe-Delimited Transaction Types
UTMUstng la-SCO UTMtmHq "~
_
UVOHMMnl 387 _ UPHorblM
LDP Cre lute D ~ LOP*etry«i>
LOPVlftMMI U LWMn«af»lU
_ )m>iom
KN5£IITHKTRUBICC1«T HUmUffi
dtycodi sag
LMMtag SUBURBAN
73
-------
Pipe-Delimited Transaction Types
-<-^N ^m-
MB J /wb
,"? V, C f<. f~1 Duptctla Mtmaor I
HA|I|23|001|0002|44201|1|03|AREA|REGIONAL SCALE | |TOP OF BUILDING|4 11111 | Y | 12 | | | | |2399| III
Pipe-Delimited Transaction Types
(Cont)
74
-------
Pipe-Delimited Transaction
Common Fields
Transaction Type - Always the 1st Column
Action Indicator - Always the 2nd Column
-1 = INSERT
- U = UPDATE
- D = DELETE
State Code - Always the 3rd Column
County Code - Always the 4th Column
Site ID - Always the 5th Column
Remember These?
Measurement Parameter Method Units
Durations
CO
NO
N02
NOx
03
S02
PM10(LTP)
PM10(STP)
42101
42601
42602
42603
44201
42401
85101
81102
088
090
090
090
091
092
076
079
007 (ppm)
007
007
007
007
007
105 (ug/m3)
001
PM2.5- Reference 88101 079
105
1,7
1,7
method 076
method 079
Anderson Beta FH62I-N
TEOM 1400A
75
-------
Raw Data Example
AQS Monitor ID: TT-531-1234-44201-1
Date- 20060701 (July 1,2006)
Time- 1300 (1PM)
Method- 014
Units 008 (Parts per Billion)
Sample Duration 1 (1 Hour)
Sample Value: 55
RD|I|TT|531|1234|44201|1|1|008|014|20060701|13-00|65||||||||||||||||
Action ("I" = Insert; "U" = Update, "D" = Delete)
Optional Fields
Transaction Type ("RD" = Raw Data Record)
Make Sure You Have the Right
Codes
Some Parameter Codes May Look or Sound
Similar, But Are Different.... BE CAREFUL!!
Example: PM2.5
- 88101: Data Collected from a FRM/FEM Monitor that
is Comparable to the NAAQS
- 88502: Data that has been determined to be "FRM-
Like". Not for NAAQS, but used for AQI
- 88501: Uncorrected data that doesn't match FRM
results
- 88500: Total Atmospheric Data
- 88503: FDMS reference channel data
76
-------
Where Do I Find the Codes?
AQS
- "Retrievals"->"Browse Sample Methodologies"
- "Retrievals"-*"Browse Parameters"
Oracle Discoverer
- All Reference Tables are Available for Querying
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 the sample (the duration). 1 hour
- What kind of instrument was used? Dasibi 1008-AH
- When was the sample collected'' June 10, 2006
- What time did the sample begin (local standard time): 1 00 PM
- Sample Concentration: 0.050 parts per million
- Any other qualifiers that you want to apply to the 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
77
-------
Pipe-Delimited Format:
RD|110110011999914420111|11007|056|20060610113:00|0 050| I I I I I I I I I I I I I I
XML Format:
01
cCOuntyCode>001
9999
44201
l
lc/DurationCode>
059
007
20060610
13 00
0 050
78
-------
Student Exercise 2.3
Day 2 - Batch Loading Data in AQS (Clean Run)
Purpose: See how the batch load process works when there are no errors in the file
Parti
Step 1. Start AQS & Logon using your training user id and password
Step 2. Select your screening Group
Step 3. From the menus select "Batch"
Step 4. Click on the CDX button
Step 5. Select the file "Ex2_3_Txx" where "Txx" is your training id from the
instructor-provided directory.
Step 6. Close CDX and Return to the Batch screen
Step 7. Click the "Refresh CDX" button until your file appears on the list.
Step 8. Click on "Load File" and wait for the status of the submitted job to read
"COMPLETE".
Step 9. Click the "Edit/Load Summary" button on the bottom of the screen.
PLEASE NOTE
If the status of your job says "ERROR", please advise the instructor or an assistant immediately.
Questions:
1. Using the "Edit/Load Summary" report, how many records were in the file that you loaded?
How many were "Raw Data" types of records?
2. If you were to run the "Edit Error Detail" report, how many records would you guess would
appear on this report based on the results of the "Edit/Load Summary" report?
Part 2
Step 1. Using Windows Explorer, locate the "Ex2_3_Txx" file.
Step 2. Open the file.
Step 3. Using the "Data Input Formats for the Re-engineered Air Quality Subsystem"
document, see how the layouts of the transactions apply to the entries in the
text file.
Questions:
1. What field is contained in the 10th field of one of the "RD" transactions? What is the value?
2. What is the monitor ID we are entering data for in this file?
79
-------
Post Data
Jonathan Miller
USEPA
Batch Load: Use Batch Load
to Install Site/monitor Data
AQS B«lcb D«l« Inpgl Floticlnn
Complete Exercise
2.4
VS. ] a... i zi~.
80
-------
Batch: Stat CR and Post
AQ& Bilch DiU lapui Fhmch«n
Stat CR
• Stat CR = Statistical Critical Review
- Evaluation of data to discover possible but unlikely data
Results available in Scan and Stat Evaluation Reports
• Scan Report
- 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.epagov/ttn/airs/aqs/softw/AQSUserGuide.pdf
81
-------
Critical Reviews
Once data posted, accessible by public. Stat CR
and Post activities help flag data anomalies prior
to release
Real errors that fit with rest of data not captured
Anomalies found are not necessarily errors
Stat CR and Post must always be run when raw
data is added to or changed in database
Batch: Stat CR
Simply push button, wait for completion (e-mail/
refresh for notification)
82
-------
Batch: Statistical Evaluation Report
,
teasd teta Upoj
Air Quality Subsystem
STATISTICAL EVALUATION REPOR'
{Screening Group Name LBAUDER
Monitor Id Date Time Error
Above Gip Cm
48-141-9934-12128-
2
48-141-9954-12128-
2
Apt 01. 1999 00
Apr 06 1999 00
Fall Shewhart ten
Vilue exceeds relative marunum level
defined for parameter
Only records that fail a test are reported
Batch: Scan Report
oti.rcti avitu amtarrsinu. mcirecTiai K=JXH
MR QUALITY r.trni
1 H.e* l«
[noiatnt M Jn« (i vMr 9 Rax aa..*-TiB» MAX
f'j'"iJM-"'MW1-43>iof-l1 bj* '!' S5W~S1 Hit
1 tM~OTiV !f422-* OT8 ^ ZRfl?vl 03
• 15t-t9LS-]£C£3- 070 ~ 29&2-Z U
! nj-»«>-!«<*7- OT1 <0m-l 03
: i9i-n»-ui<£- o-e to«;-< 01
i il>»si-i»tx;- t-t Jim-: cj
i U)-9SJb-l«Ii£- 07E iSa-J 0]
. -i»3->«'. itsiz r.t :tK > a
!»-i»-»u-uia- OTI iam-2 »
s9-i»i-»S3-i£sn- C7e =312-3 n
»»-l»>»»bl-14f-"i- 07e i002-4 0-1
4ft L4] «'*44-L?S28 2 OOX l"t*-2 1T4 ItW/Oi/IO CO • DCfl
1
ic«n Mwm (W01J5:
B«.-T.~ «.
O'./JO 00
04/» CO
ISVOT oo
10/71t*0&
tt/ui oo
07/07.00
o.y JO-CD
ci 'oz eo
07/07 00
10/SJ CD
04/06 BO
V 7nO V3
,._ r MKK o^.. TA]M zjtf. r a.
t> 04/11 CO 1
.n •»/:» oo i
03 1!/OHM '
.0: OCY-.9 E3
&3 d/'iV'Od i
03 02/61 09
03 04/14 35
n 07/st 03 <
.0? 11/04 09
Lll 04/10 n C b
Report flags "sports" in the data with *
83
-------
Batch: Post
Post is final process in
batch update process
One last chance to
verify that data is
ready for the world
Raw Data Inventory
Report is available
after Post batch job
runs
AQS BMCB Dili Inpai Ftoicton
Staging
Times
Production Tables •
R- Relational
Status
S = Stat
Analysis
Batch: Post form
RnrOnmm I _R»Jla»_J_
This information is in
the Scan Report
This information is in
the Statistical
Evaluation Report
Sutfriul Dau
»t«iBH».C«i BnrDM
flJZ
Post button on batch form calls-up new form to use
review data
84
-------
Batch: Post Form Raw Data Tab
.IDlxl
KM fan* 0» MniOeMi Mod Dinttn CUfnq coiaMt
OuallflnDiUlli
fig"*
Rmum ID Bart Piocisting | Port Pan la Produclmn
Raw Data display allows verification that incidents that "qualify"
public. Qualifier information cannot be entered in this form
Batch: Post Form, Raw Data
RawOala
F—
PKI OM lo Pmhclun
I
•, ^saa'^-s^0-
roulCiB Vtan
SMMcalOaM
»aa
MOJOC r«fco
*raoc ^dk Dun «•> ML
may be perfectly acceptable data values for posting
85
-------
Batch: Post Form, Raw Data
RavOwuw RnOau
MMOrD tn-780-OT3-<2«01-J
Ra1*0'u
ammmm
'1
M
j
'!'
_,jl ___
SBSnM «MS«(*Mi
VBW
fffri 1M tM (Mil rytfp IM fjVto
rZE^ELEgHr.g'.EZ:
i i-
|nxn«onuTK«s
Portban'io'ProiL'ciioit'T
Remember, ALL records belongjnajo screening
group are posted. It doesn't matterwPioTbaded d
Batch: Raw Data Inventory Report
t
,
>
i
i
1
i
1
*
c=
A ds-n
'&arn&*aauaart*tai
Monitor Id
19-1S4935-16822-1
19.13U93S-16822-I
19.1SW9SS-16S22.1
19-ISW9S5-16S22-1
19-1S49I5-1C8B-1
19-1SJWS5-16S22 1
19.1SJW35-16S22-1
19-15V993S-16822-1
I9-1S49S3-168Z2-1
19.1SM9SS-1682M
19-1S49SS-I68S-1
19-13>893i.l6a22-l
19-U949SS-U822O
19^1334933- 16822-3
19.1318953.16822-4
l»15%9953-168Z2-4
Air Quality Subsystem
ffl*BOt> Raw Data Inventory Report
SenrauQ Cr««p. LBAUDER
Aug 28. 2003
Duration Year-Mon Inserts Updates
34 HOOKS 2O32-1
34 BOORS 20QB4
34 BOORS 2002O
24 HOURS 20DQ-4
24 BOORS 2032-3
24 HOOKS 2002*
24 HOURS 2002-7
24 BOORS 20024
24 BOORS 20024
24 ROOKS 2032-10
24 HOOKS 2032-11
24 BOORS 2002-12
24 BOORS 2002-6
24 BOORS 2002-7
24 BOORS 2002.1
34 HOOKS 2002.2
1 0
3 0
2 0
3 0
2 0
2 0
3 0
1 0
3 0
2 0
3 0
2 0
2 0
3 0
1 0
3 0
Deletes
Oj
I
i
0
86
-------
Student Exercise 2.5
Day 2 - Post Process
Purpose: Post pre-production data to a production status
Part 1 - Run the Statistical Report
Step 1. Start AQS & Logon using your training user id and password
Step 2. Select your screening Group
Step 3. From the menus select "Batch"
Step 4. Click on the "Stats / CR" Button
Step 5. Wait until the status of the job = "COMPLETE"
Step 6. Click the "Scan Report" Button. Review the report when it completes
Step 7. Close the report
Step 8. Click the "Stat Evaluation" Button. Review the report when it completes
Step 9. Close the report
Questions:
1. What types of records are being evaluated in this process?
2. What is the status of the records BEFORE and AFTER running the Statistical
processes?
BEFORE:
AFTER:
3. If you did find strange data identified by the statistical process, how would you
go about correcting the problem?
Part 2 - Post the Data
Step 1. Click on the "Post" button
Step 2. Select any of the lines on the "Raw Overview" tab
Step 3. Click on the "Raw Data" tab. Question: What does this data represent?
Step 4. Click the "Post Data to Production" button
Step 5. Click "OK" on your job being processed
Step 6. Click the "Return to Batch Processing" button
Step 7. When the Post job completes, click the "Raw Data Inventory" button.
Review the report. Question: How many records were updated in this job?
Step 8. Close the report
Step 9. Exit AQS
Questions;
1. Don't forget the question in "Step 2"
2. Don't forget the question in "Step 7"
3. What is the status of the raw data records after this process completes?
87
-------
Correct Function
Jonathan Miller
USEPA
Batch load: Error Process
AQS Batch Data Inpul Flowchart
Result of Load
- Load with ERROR
- Reports successes and
failures
Afr Quality Subsystem
EdULoad Summary Ripen
AirOiiiilllt Suhs\slcm
tdh fcrwr Hep«rt
88
-------
Correct Process
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)
Batch load: Correct Forms
AQS Bilch Din Inpnl Floncbiil
There is a form for correcting
every type of data input
record
Air Quality Sntayslrai
EdXILoad Sunmuy Rtpon
| Erobe Obs
L .'
f ' Cotlocalion
t Protocol
AQency
Tabjgem Road
89
-------
Batch load: Using Correct Forms
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
Rood tho on of 111088090(8)
Error messages are the same
as in the error details report
Many chose to go straight to
the Correct screens rather
than run a report
The column name is
sometimes informative
VT~ rSaue
\ fZ-TT
I—J
Fixing the data often
requires knowledge of the
nature of the data and may
require going back to data
\sgurces
Exclude unfixed dttlu
Excluding unfixed data will
keep it from raising error
flags during toad
73
Batch load: Using Correct Forms
Deleting Data (cont.)
£ctn Hftp S
-------
Batch load: Using Correct Forms
Deleting Data (cont.)
'Baw
Eficam
Accuracy
Compouu
'Annual Summary
Detota By Screening Group
will completely clear ALL
records owned by the
Screening Group in the
Staging Tables
Search & Replace
Not "Search & Replace" as you may know
it!
Replaces ALL Queried Values in a given
column Regardless of the "Current Value"
S«irc* atxl Rtplu*
fias
Repbn
S Ctnctf
91
-------
Correct Features
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 Tor 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
O«.T™-.™. Be sure this is what you want to do It works
'"JSSSX&sr quickly and it deletes EVERYTHING for the
screening group, including data entered by other
users in your screening group.
Batch load:
Submit Correct Data
AQS Batch Data Input Flonchirt
Once data is corrected
- Submit correct data
- Recheck for errors
- Correct residual errors
Q2
-------
Complete Exercise 2.4
Analyze and correct the following Batch load
error types
- Site/mon does not exist
- Overlapping data
- Inactive monitor on date of entry
- Value not found in reference table
93
-------
Student Exercise 2.4
Day 2 - Batch Loading Data in AQS (With Errors)
Purpose: See how the batch load process works when there are errors in the file
Parti
Step 1. Start AQS & Logon using your training user id and password
Step 2. Select your screening Group
Step 3. From the menus select "Batch"
Step 4. Click on the CDX button
Step 5. Select the file "Ex2_4_Txx" where "Txx" is your training id from the
instructor-provided directory.
Step 6. Close CDX and Return to the Batch screen
Step 7. Click the "Refresh CDX" button until your file appears on the list.
Step 8. Click on "Load File" and wait for the status of the submitted job to read
"COMPLETE".
Step 9. Click the "Edit/Load Summary" button on the bottom of the screen.
Step 10. Click the "Edit Error Detail" button on the bottom of the screen and
review. The instructor will provide a sheet outlining each of the errors, what they
mean, and how to correct them.
PLEASE NOTE
If the status of your job says "COMPLETE", please advise the instructor or an assistant
immediately.
Part 2-Correct Utility
Step 1. Click on "Correct" -> "Raw" from the menu.
Step 2. Follow the instructions provided by the instructor's sheet as how to identify
and resolve the issues using functions in CORRECT.
Step 3. After all corrections have been made, click on "Batch"
Step 4. Click on "Submit Correct Data" to process the records from CORRECT
Step 5. Repeat Steps 1 - Step 4 until all errors have been corrected. You will know
this when the status of the "Submit Correct Data" job says "COMPLETE"
Step 6. Exit from AQS.
94
-------
Error Corrections
Error #1
You will see that you have 1 record that has an error because of "Monitor
Inactive"
Why?; The record was entered with a start year of 2002. The
monitor was not active until 2003.
How do I fix ft?: Do a query to find all records that start with the
year 2002. Change the year to 2006.
Error #2
You will see that you have 1 record that has an error because of "Invalid
Monitor ID"
Why?: You own monitor 37-073-90xx, where "xx" is the last 2 digits
of your screening group. There is one record with a value of 37-
073-OOxx.
How do I fix it?: Do a query to find all records that start with the site
id of "OOxx". Change the site id to "90xx".
Error #3
You will see that you have 1 record that has an error because of "Value
may not overlap another sample".
Why?: The record has a start time of "05:30" and a duration of 1
hour. There is another record that did pass that has a start hour of
"06:00". So the value that started at 05:30 couldn't be 1 hour
because it would bump into the sample that started at 06:00.
How do I fix it?; Do a query to find all records that start with the
hour of "05:30". Change the value of the hour to "05:00"
Error #4
You will see that you have 16 records that has an error because of "Invalid
Protocol"
Whv?: The record references an invalid method code (currently
"919")
How do I fix ft?: Using "Search & Replace", change all of the
method code values of "919" to "019".
95
-------
Reporting Requirements &
Data Certification
Jonathan Miller & Ryan Callison
What 40CFR Part 58 Says:
Paragraph a): Pertains to:
- SO2> CO, N02, NO, NOy, NOX, Pb, PM10
Mass, PM2.5 mass, PM2.5 FRM/FEM
Paragraph b): Due 90 Days at the end of
the Calendar Quarter
Paragraph d): Pertains to:
- VOCs, Carbonyls, NH3, HNO3 From PAMS
- Due 6 Months After the End of the Calendar
Quarter
96
-------
Annual Certification
Due by July 1 of the Following Year
- 2006 Data must by Certified by July 1, 2007
for Example
Certifications Must Have the Following:
- Letter from the Senior Air Pollution Control
Officer
- AMP450 (Quick Look Report)
- AMP240 (PQAO Annual Summary Statistics)
- AMP255 (New P&B Statistics)
Why Have These Deadlines
So you can be Reasonably Sure that You
Have a Complete Dataset When
Performing Analysis
So you can be Reasonably Confident That
the Owner of the Data Says That the Data
is "Good"
97
-------
Changing Raw Data Values
1. From Main Menu, click Maintenance,
SampleValues, Raw Data
2. Enter monitor identifying info, click Execute
Query.
3 Click on field in Raw Data area to change.
4. Click Insert Record icon in top menu
5. Click Duplicate Record icon
6. Change incorrect value
7. Enter Action Ind of IT
8. Enter Reported Scale value (Reported Scale is
the number of decimal places)
9. Click Save icon.
10. Click Main Menu, Batch, click Stats CR.
11. Click Scan Report from Batch Reports tab.
12. Click Stat Evaluation from Batch Report tab.
13 Click Post from Batch Load tab.
14. Click Raw Data tab, click Post Data to
Production
15. Click Return to Batch Processing from Raw
Data tab.
16. Use Refresh button to verify Session Status is
'Completed'.
17. Click Raw Data Inventory report from Batch
Report tab
Changing Site/Monitor Data
1. From Main Menu, Maintenance, Monitor.*
2. Enter Site/Monitor identifying info.
3 Click Execute Query.
4. Make changes.
5. Click Save icon
* Changing site data is the same except you would
specify Site from the Maintenance option.
Using Correct to Change Records in
the Staging Tables
1. From Main Menu, click Correct, Raw.
2. Enter identifying information, Execute Query.
3. Correct errors
4. Click Save icon.
5. Click 'Sul " Correct Data' on Batch Load tab.
Tips
• Date format is: YYYYMMDD
• Under Maintain, if you are logged on in a
screening group, you can only access records
assigned to your screening group.
• When using reports, be as specific as possible
in providing criteria.
Passwords must be changed every 90 days.
• AQS User Support: 866-411-4EPA (4372)
- CDX User Support: 888-890-1995
"%" is the wildcard for Oracle queries.
Changing Passwords
To synchronize/change passwords:
1. From Main Menu, click Admin, Security.
2 Enter new AQS password, click 'Ok'.
3. Select 'Canyon' or 'CDX' from Server Name,
4 Enter current password and click 'Synchronize'.
Deleting Raw Data Records
1. Use the Maintenance option to display the
record. (Main Menu, Maintenance, Sample
Values, Raw Data)
2 Enter monitor identifying information.
3. Click Execute Query button.
4 Insert an empty row below the record by
highlighting the record to be deleted and
clicking Insert Record button
5. Duplicate the record by clicking the Duplicate
Record button.
6 Add an action indicator of "D".
7. Click the Save button.
8. Click Main Menu, Batch, click Stats CR.
9. Click Scan Report from Batch Reports tab.
10. Click Stat Evaluation from Batch Report tab.
11. Click Post from Batch Load tab.
12 Click Raw Data tab, click Post Data to
Production.
13 Click Return to Batch Processing from Raw
Data tab
14. After verifying the session status is 'Completed',
click the Raw Data '"""itory button from the
Batch Report tab.
AQS Web
Quick Reference
Guide
AQS Data Input Flowchart
Staging
Tables
Production Tables -
Preproduction Status
• ' Y
Rim Data!
InvenlorvP'
Post
Tables - Production Status
US EPA
OAR/OAQPS/ITPID/IMG
AQS Project Office
Version 2
-------
AQS Web Addresses
AQS Web is accessed from:
http://www epa gov/ttn/airs/airsaqs/aqsweb/aq
swebwarning htm
AQS Web User Guide:
http://www.epa.gov/ttn/airs/airsaqs/aqsweb/aq
swebhome htm
Accessing AQS Web the First Time
Prior to using AQS Web for the first time, the
userneeds to install some additional software
and completethe registration on CDX This
can be found at:
http://www.epa gov/ttn/airs/airsaqs/aqsweb/aq
swebhome.htm
Creating Reports
1. From the Main Menu, click Retrieval, Standard
Reports Selection Criteria.
2. Provide Report Code on Criteria Set tab.
3. Provide selection criteria on Selections tab.
4. If necessary, complete Sort Order, Report Options
and Raw Data Options tabs.
5. On the Generate Report tab, determine if report is
to run using Online or Batch mode. (Online reports
are sent to the browser, batch reports arc used for
large reports and are sent to users CDX mailbox.)
6. Click Generate Report on Generate Report tab.
7. Jobs run online will be returned to the browser.
8 If run as a batch report1
9. Click 'CDX to Retrieve Batch Report'. On
Generate Report screen.
10. Login to CDX, click 'Inbox' on main CDX screen.
11. Double click 'CDX Admin xxxx' where xxx is the
file name you assigned to the file in AQS and
follow direction to download report.
Browse Data
1. Select Maintenance from Main Menu
2. Select the type of data you want to browse:
Site, Monitor, Sample Values (Raw Data)..
3. Enter criteria for limiting the data (For example,
state, county, site...).
4 Click the Execute Query button.
Inputting Data Using Batch
1. Select Screening Group Access and Screening
2. Group
3. Select Batch from Mam Menu
4. Click 'CDX' and login to CDX.
5 Click 'AQS: Air Quality System—File Transfer'.
6 Select file(s) to be sent to AQS and click
'Send1.
7. Wait for file to appear on AQS batch list
8 Click Load File on Batch Load tab.
9. Use Refresh button to check Session Status
until Session Status is not 'Active'.
10. Verify record counts on the Batch Reports tab,
Edit/Load Summary report.
11. Data other than Raw Data (Site, Monitor etc) is
sent to AQS.
For Raw Data do the following additional steps
1 If there are errors see'Using Correct to
Change Records in the Staging Tables.'
2 Verify record counts on the Batch Reports tab,
Edit/Load Summary report.
3. Click Stat CR from Batch Load tab
4. Run Scan and Stat Evaluation reports from
Batch Report tab.
5. Click Post button on the Batch Load tab.
6. If 'CR Exists', 'Stats Exist' or 'Monitor Locked' is
not 'N' on Raw Overview tab; review record.
7. Click Raw Data tab and 'Post Data to
Production' buttons
8. Click the Return to Batch Processing on Raw
Data tab.
9. Use Refresh button to verify Session Status is
'Completed'.
10. Click the Raw Data Inventory on Batch Reports
tab.
Inputting Raw Values Usin
Maintenance
1. From Mam Menu, click Maintenance, Sample
Values, Raw Data
2. Enter monitor identifying info, click Execute
Query.
3. On Raw Data tab, click on one of the records in
the 'Raw Data' area of the screen
4. Click Insert Record icon in top menu and enter
new info, (put an 'I' in the Action Ind)
5. Click Save.
6 Click Mam Menu, Batch, click Stats CR.
7. Click Scan Report from Batch Reports tab
8. Click Stat Evaluation from Batch Report tab
9. Click Post from Batch Load tab.
10 Click Raw Data tab, click Post Data to
Production.
11. Click Return to Batch Processing from Raw
Data tab
12. After verifying the session status is 'Completed',
click the Raw Data Inventory report from the
Batch Report tab.
Insert a New Site/Monitor Using
Maintenance
1. From Screening Group and Main Menu, click
Maintenance, Site. Click Cancel Query button
2. On Basic Site Data tab, provide State, County
Code, Site Id.
3. Complete information on Required and Optional
tabs of Maintain Site Data screen (Basic Site
Data, Agency Roles ..)
4. Click Save.
5 Click Create Monitor button on Maintain Site
Data Screen
6. Provide information on Monitor Basic tab as
well as other tabs. (Several required fields).
7. Click Save button.
8. Verify that Site & Monitor have been entered by
clicking from Main Menu, Maintenance, Site and
Monitor.
9 Enter state, County, Site Id, click Exec. Query.
10. Site/monitor should be displayed.
-------
Loading Data Into AQS
x
Q
O
SiibmitlGbrrect-Dat;
O
Correct
Transaction Errors
Perform Statistical
Evaluation of Data
Post Data to
Production
I
c
-
Review Edit/Load
Summary & Edit
Error Details
No
Review Stat / CR
& Scan Reports
Files that have been
transferred to CDX but
not Loaded into AQS
Files from CDX ready to Load:
FteMO
When & What
Processes Have
Taken Place
Pod
-------
Data Input Formats for the Re-engineered Air Quality Subsystem
This document provides the format to process batch transactions for the Air Quality Subsystem. The first field of all
input formats is the transaction type, which indicates the type of data in the record. (The valid transaction types are
listed below) The second field is the Action Indicator, which indicates the requested database manipulation action
The valid Action Indicators are "I" for insert, "U" for update, and "D" for delete.
All transactions are pipe delimited ("|"). A delimiter does not need to follow the final field in a record, so there will
always be one less delimiter than fields for the given transaction type. It is not essential that the proper number of
delimiters be provided for a given transaction. So if you need to only update one column on the database with
"Monitor Basic" information, you can ignore empty field delimiters in that row If you need to report a given field
as null, place two delimiters back to back ("||") Text fields should not be enclosed with single or double quotation
marks
Other Notation R - Field is required for any action
R(n) - Field is Required for action *n' (R(I) means required for an Insert Action for example)
X(n) - Conditionally Required for action 'n'
Site Information 2
AA Basic Site Information 2
AB Site Street Information 3
AC Site Open Path Information 3
Monitor Information 4
MA Basic Monitor Information 4
MB Monitor Sampling Periods 4
MC Monitor Type Information 4
MD Monitor Agency Role 5
ME Monitoring Objective Information 5
MF Monitor Sampling Schedule 5
MG Monitor Tangent Roads 6
MH Monitor Obstruction Information 6
Ml Monitor Regulatory Compliance 6
MJ Monitor Collocation Period 6
MK Monitor Protocol 7
Reported Data Values 8
RC Composite Raw Data 8
RD Hourly.-Daily, and Sub Hourly Raw Data 8
RA Accuracy Data 9
RP Precision Data 9
RS Annual Summary Data 10
RB Blanks Data 11
Please Note
The "Formatting Rules" described on the following pages are a ''quick reference" and are not intended to
illustrate the complete validation procedures that a particular piece of data will be subject to.
The AOS Data Coding Manual explains business rules and errors in detail.
The AQS Data Dictionary fully descnbes the formatting requirements and data dependencies.
-------
Daia Input Formats
EPA Air Quality System
Site Information
AA Basic Site Information
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal IndicatorR
County Code / Tnbal Code R
Site ID R
Latitude x(l)
Longitude X(l)
UTM Zone X(I)
UTM Easting X(I)
UTM Northing x(l)
Honzontal Collection Method R(l)
Horizontal Datum R(l)
Source Scale R(l)
Honzontal Accuracy R(l)
Vertical Measure R('}
Time Zone
Agency CodeR(l)
Street Address R(l)
City Code R(l)
Urban Area Code*0
AQCRR(I)
Land Use Type R(l)
Location Setting R(I)
Date Site Established R(l)
Date Site Terminated
Zip Code
Congressional District
Block
Block Group
Census Tract
Class I Area
Formatting Rule
AA = Basic Site Info.
Must = I, U, or D
Must exist in STATES Reference Table or be "IT for Tribal Site
Must exist in COUNTIES or TRJBAL_AREAS Reference Table
Must exist in SITES table if Action Indicator is 'U' or 'D'
Number- 10.6 signed
Number -11.6 signed
Number - 2.0 formal
Number - 8.2 format
Number - 8.2 format
Must exist in LDP_COLLECTION_METHODS Reference Table
Must exist in LDP_HOR1ZONTAL_DATA Reference Table
Number - 12.0 format
Number - 8.2 format
Number - 8.2 format
Must exist in T1ME_ZONES Reference Table, if supplied
Must exist in STATE_AGENC1ES Reference Table
Text
Must exist in CITIES Reference Table
Must exist in URBANIZED_AREAS Reference Table
Must exist in AQCRS Reference Table
Must exist in LAND_USE_TYPES Reference Table
Must exist in LOCATION_SETTINGS Reference Table
Date - YYYYMMDD format
Date - YYYYMMDD format
Must exist in ZIP_CODES Reference Table, if supplied
Must exist in CONGRESS1ONAL_DISTR1CTS Reference Table,
if supplied
Must exist in BLOCKS Reference Table, if supplied
Must exist in BLOCKS Reference Table, if supplied
Must exist in BLOCKS Reference Table, if supplied
Must exist in CLASS_ONE_AREAS Reference Table, if
supplied
Must exist in LOCAL_REGIONS Reference Table, if supplied
Text
Date - YYYYMMDD format
Date - YYYYMMDD format
SECTORS Reference Table
Local Region
Local Site Name
HQ Evaluation Date
EPA Region Evaluation Date
Direction from Central Business District to Site Must exist in COMPASS
Distance from Central Business District to Site Number - 8.2 format
Meteorological Site Type Must exist in MET_SITE_TYPES Reference Table, if supplied
Meteorological Site ID Must exist in Reference Table, if supplied
Distance to Meteorological Site Number - 8.2 format
Direction to Meteorological Site Must exist in COMPASS_SECTORS Reference Table, if
supplied
Local Site ID Text
Vertical Collection Method R(l) Must exist in LDP_VERTICAL_METHODS Reference Table
Vertical Datum R(I) Must exist in LDP_VERTICAL_DATA Reference Table
Vertical Accuracy R(l) Number
Version 2.12
June 21,2005
Page 2
-------
EPA Air Quality System
Data Input Formats
AB Site Street Information
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Tangent Street Number R
Street Name R(I)
Road Type R(1)
Traffic CountR(l)
Year of Traffic Count R(l)
Direction from Site to StreetR(l)
Source of Traffic Count
Formatting Rule
AB = Site Tangent Street Data
Must = I, U, or D
Must exist in STATES Reference Table or be 'TT for Tribal Site
Must exist in COUNTIES or TR1BAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Number - 2.0 format
Text
Must exist in ROAD_TYPES Reference Table
Number- 120 format
Date - YYYY format
Must exist in COMPASS_SECTORS Reference Table
Must exist in TRAFFIC_VOLUME_SOURCES Reference Table,
if supplied
AC Site Open Path Information
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Open Path Number R
Direction from Receiver to Transmitter R(l)
Beam Length R(l)
Height of Transmitter R(l)
Height of Receiver R(l)
Minimum Beam HeightR(l)
Maximum Beam HeightR(l)
Land Use Under Path R(1)
Formatting Rule
AC = Site Open Path Data
Must = 1, U, or D
Must exist in STATES Reference Table or be 'TT' for Tribal Site
Must exist in COUNTIES or TRIBAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Number - 2.0 format
Must exist in COMPASS_SECTORS Reference Table
Number - 8.2 format
Number - 8.2 format
Number - 8.2 format
Number - 8.2 format
Number - 8.2 format
Must exist in LAND USE TYPES Reference Table
Version 2.12
June 21,2005
Page 3
-------
Data Input Formats
EPA Air Quality System
Monitor Information
MA Basic Monitor Information
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Project Class
Dominant Source
Measurement Scale
Open Path Number
Probe Location Code
Probe Height
Probe Horizontal Distance
Probe Vertical Distance
Surrogate Indicator
Unrestricted Air Flow Indicator
Sample Residence Time
Worst Site Type
Applicable NAAQS Indicator
Spatial Average Indicator
Schedule Exemption Indicator
Community Monitoring Zone
Pollutant Area Code - 1
Pollutant Area Code - 2
Pollutant Area Code - 3
Pollutant Area Code-4
Pollutant Area Code - 5
Close Date
MB Monitor Sampling Periods
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Date Sampling Began R
Date Sampling Ended
MC Monitor Type Information
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Monitor Type R
Monitor Type Begin Date R
Monitor Type End Date
Formatting Rule
MA = Basic Monitor Information
Must = 1, U, or D
Must exist in STATES Reference Table or be "FT for Tribal Site
Must exist in COUNTIES or TRIBAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITOR table if Action Indicator is 'U' or 'D'
Must exist in PROJECT_TYPES Reference Table, if supplied
Must exist in DOMINANT_SOURCES Ref. Table, if supplied
Must exist in MEASUREMENT_SCALES Ref. Table, if
supplied
Must exist in Site Open Path Table, if supplied
Must exist in PROBE_LOCATIONS Reference Table, if supplied
Number - 8.2 format
Number - 8.2 format
Number - 8.2 format
'Y'or'N'
'Y' or 'N'
Number - 8.2 format
Must exist in WORST_SITE_TYPES Reference Table, if
supplied
'S', 'A', or 'B', if supplied
'Y' or 'N', if supplied
'Y' or 'N', if supplied
Number - 4.0 format
Must exist in POLLUTANT_AREAS Ref.
Must exist in POLLUTANT_AREAS Ref.
Must exist in POLLUTANT_AREAS Ref.
Must exist in POLLUTANT_AREAS Ref.
Must exist in POLLUTANT_AREAS Ref.
Date - YYYYMMDD
Table, if supplied
Table, if supplied
Table, if supplied
Table, if supplied
Table, if supplied
Formatting Rule
MB = Monitor Sampling Period
Must = I, U, or D
Must exist in STATES Reference Table or be "IT for Tribal Site
Must exist in COUNTIES or TR1BAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Date - YYYYMMDD
Date - YYYYMMDD
Formatting Rule
MC = Monitor Type Assignment
Must = 1, U, or D
Must exist in STATES Reference Table or be "IT for Tribal Site
Must exist in COUNTIES or TRJBAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Must exist in MONITOR_TYPES Reference Table
Date - YYYYMMDD
Date - YYYYMMDD
Version 2.12
June 21,2005
Page 4
-------
Data Input Formats
EPA Air Quality System
IMD Monitor Agency Role
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal IndicatorR
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Agency Role Name R(l)
Agency Code R
Agency Role Begin Date R(l)
Agency Role End Date
Formatting Rule
(MD = Monitor Agency Role
Must = I, U, or D
Must exist in STATES Reference Table or be 'TT for Tribal Site
Must exist in COUNTIES or TR1BAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Must exist in AGENCY_ROLES Reference Table
Must exist in STATE_AGENCIES Reference Table
Date - YYYYMMDD
Date - YYYYMMDD
ME Monitoring Objective Information
Field Name
Transaction Type R
Action IndicatorR
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
POCK
Monitor Objective Type R
Urban Area Represented X(l)
MSA Represented X(l)
CMSA Represented X(l)
MF Monitor Sampling Schedule
Field Name
Transaction Type R
Action IndicatorR
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Formatting Rule
ME = Monitor Objective
Must = 1, U, or D
Must exist in STATES Reference Table or be 'TT' for Tribal Site
Must exist in COUNTIES or TRIBAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Must exist in MONITOR_OBJECTIVE_TYPES Reference Table
Must exist in URBANIZED_AREAS Reference Table, if
supplied
Must exist in MS AS Reference Table, if supplied
Must exist in CMSAS Reference Table, if supplied
Formatting Rule
MF = Monitor Sample Schedule
Must = I, U, or D
Must exist in STATES Reference Table or be 'TT' for Tribal Site
Must exist in COUNTIES or TRIBAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Must exist in COLLECTION_FREQUENCIES Reference Table
Date - YYYYMMDD format
Date - YYYYMMDD format
Parameter R
POCR
Required Collection Frequency Code R
Required Collection Frequency Begin Date
Required Collection Frequency End Date
Monthly Required Collection Frequency - Jan. Number - 12.0 format, if supplied
Monthly Required Collection Frequency - Feb Number - 12 0 format, if supplied
Monthly Required Collection Frequency - Mar Number - 12.0 format, if supplied
Monthly Required Collection Frequency - Apr Number - 12.0 format, if supplied
Monthly Required Collection Frequency - May Number - 12.0 format, if supplied
Monthly Required Collection Frequency - Jun Number - 12.0 format, if supplied
Monthly Required Collection Frequency - Jul Number - 12.0 format, if supplied
Monthly Required Collection Frequency - Aug Number- 12.0 format, if supplied
Monthly Required Collection Frequency - Sep Number - 12.0 format, if supplied
Monthly Required Collection Frequency - Oct Number - 12.0 format, if supplied
Monthly Required Collection Frequency - Nov Number - 12.0 format, if supplied
Monthly Required Collection Frequency - Dec Number - 12.0 format, if supplied
Version 2.12
June 21,2005
PagcS
-------
EPA Air Quality System
Data Inpul Formats
MG Monitor Tangent Roads
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Tangent Street Number R
Distance from Monitor to Tangent
RoadR(I-U)
Formatting Rule
MG = Monitor Tangent Road
Must = 1, U, or D
Must exist in STATES Reference Table or be 'TT for Tribal Site
Must exist in COUNTIES or TRIBAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Must exist in Site Tangent Street Table
Number - 8.2 format
MH Monitor Obstruction Information
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Probe Obstruction TypeR
Direction from Monitor to Probe Obstruction R
Distance from Monitor to Probe Obstruction R
Probe Obstruction Height R
MI Monitor Regulatory Compliance
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Regulation Code R
Compliance Indicator
Compliance Date
MJ Monitor Collocation Period
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Collocation Begin DateR
Collocation End Date
Distance from Primary Sampler
Primary Sampler Indicator
Formatting Rule
MH = Monitor Obstruction
Must = I, U, or D
Must exist in STATES Reference Table or be 'TT' for Tnbal Site
Must exist in COUNTIES or TRIBAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Must exist in PROBE_OBSTRUCTION_TYPES Ref. Table
Must exist in COMPASS_SECTORS Reference Table
Number - 8.2 format
Number - 8.2 format
Formatting Rule
MI = Monitor Reg. Compliance
Must = I, U, or D
Must exist in STATES Reference Table or be 'TT' for Tnbal Site
Must exist in COUNTIES or TRIBAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Must exist in PARAMETER_REGULATIONS Reference Table
'Y'or'N'
Date - YYYYMMDD format
Formatting Rule
MJ = Monitor Collocation Penod
Must = I, U, or D
Must exist in STATES Reference Table or be 'TT' for Tribal Site
Must exist in COUNTIES or TR1BAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Date - YYYYMMDD format
Date - YYYYYMMDD format
Number - 8.2 format
'Y'or'N'
Version 2.12
June 21,2005
Page 6
-------
EPA Air Quality System
Data Inpul Formats
MK Monitor Protocol
Field Name
Transaction Type R
Action IndicatorR
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Monitor Protocol ID (MP ID) R
Duration Code R(l)
Reported UnitR(l)
Method Code R(l)
Collection Frequency Code
Composite Type
Alternate Method Detectable Limit
Formatting Rule
MK = Monitor Protocol
Must = I, U, or D
Must exist in STATES Reference Table or be 'TT for Tribal Site
Must exist in COUNTIES or TRIBAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Number - 2.0 format
Must exist in SAMPLE_DURATIONS Reference Table
Must exist in UNITS Reference Table
Must exist in SAMPL1NG_METHODOLOGIES Reference Table
Must exist in COLLECT1ON_FREQUENCIES Reference Table,
if valued
Must exist in COMPOSITE_TYPES Reference Table, if valued
Number - 5.5 format
Version 2.12
June 21,2005
Page?
-------
Data Input Formats
EPA Air Quality System
Reported Data Values
RC Composite Raw Data
Field Name
Transaction Type R
Action Indicator R
State Code / Tnbal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Reported Unit*0
Method Code R(l)
Composite Year R
Composite Period R
Number of Samples R(0
Composite Type
Reported Sample Value R (l)
Monitor Protocol ID (MP ID)
Qualifier Code-1
Qualifier Code -2
Qualifier Code -3
Qualifier Code -4
Qualifier Code -5
Qualifier Code -6
Qualifier Code -7
Qualifier Code -8
Qualifier Code -9
Qualifier Code-10
Alternate Method Detectable Limit
Uncertainty Value
Formatting Rule
RC = Composite Data Type
Must = 1, U, or D
Must exist in STATES Reference Table or be 'TT for Tribal Site
Must exist in COUNTIES or TRIBAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tnbal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Must exist in UNITS Reference Table
Must exist in SAMPLING_METHODOLOG1ES Reference Table
Date - YYYY format
Must exist in COMPOSITEJTYPES Reference Table
Number- 10.0 format
Must exist in COMPOS1TE_TYPES Reference Table
Number - 5.5 format
Must exist in Monitor Protocols table for the monitor
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Number - 5.5 format
Number - 6.5 format
RD Hourly, Daily, and Sub Hourly Raw Data
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal Indicator R
County Code / Tnbal Code R
Site ID R
Parameter R
POCR
Duration Code R(l)
Reported UnitR(l)
Method Code R(l)
Sample Date R
Sample Time R
Reported Sample Value X(IU)
Null Data Code X(I-U)
Collection Frequency Code
Monitor Protocol ID (MP ID)
Qualifier Code-I
Qualifier Code -2
Qualifier Code -3
Qualifier Code -4
Qualifier Code -5
Qualifier Code -6
Qualifier Code -7
Qualifier Code -8
Qualifier Code -9
Qualifier Code-10
Alternate Method Detectable Limit
Uncertainty Value
Formatting Rule
RD = Raw Data Type
Must = I, U, or D
Must exist in STATES Reference Table or be 'TT' for Tribal Site
Must exist in COUNTIES or TR1BAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITOR table with Parameter
Must exist in SAMPLE_DURATIONS Reference Table
Must exist in UNITS Reference Table
Must exist in SAMPLING_METHODOLOGIES Reference Table
YYYYMMDD format
hh:mm format
Number - 5.5 format
Must exist in QUALIFIERS Reference Table, if valued
Must exist in COLLECTION_FREQUENCIES Reference Table
Must exist in Monitor Protocols Table for the Monitor
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Number - 5.5 format
Number - 6.5 format
Version 2.12
June 21,2005
Page 8
-------
DaCa Input Formats
EPA Air Quality System
RA Accuracy Data
Field Name
Transaction Type R
Action IndicatorR
State Code / Tribal IndicatorR
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Accuracy Audit ID Number
Duration Code Rau)
Reported UnitR(IU)
Method Code R(MJ)
Year Represented
Quarter Represented
Accuracy Date R
Audit Type R(l)
Local Primary Standard R(l)
Audit Class R(I)
Accuracy Type R(l)
Audit Sample ID
Expiration Date
Audit Scheduled
Level 1 Actual Value xau)
Level 1 Indicated Value X(UJ)
Level 2 Actual Value X(LU)
Level 2 Indicated Value X(UJ)
Level 3 Actual Value X(IU)
Level 3 Indicated Value xau)
Level 4 Actual Value X(IU)
Level 4 Indicated Value X(LU)
Levels Actual Value X(llU)
Level 5 Indicated Value xau)
Zero Span
RP Precision Data
Field Name
Transaction Type R
Action IndicatorR
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Precision ID R
Duration Code Rau)
Reported UnitRau)
Actual Method Rau)
Precision Date R
Actual Value
Indicated Method
Collocated POCX(I)
Precision Sample ID
Agency Performing FRM Audit*0
Formatting Rule
RA = Raw Accuracy Data
Must = I, U, or D
Must exist in STATES Reference Table or be "TT for Tribal Site
Must exist in COUNTIES or TR1BAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Number - 2 0 format (Legacy "Sequence #")
Must exist in SAMPLE_DURATIONS Reference Table
Must exist in UNITS Reference Table
Must exist in SAMPLING_METHODOLOGIES Reference Table
Date - YYYY format
'Ql','Q2','Q3',or'Q4'
Date - YYYYMMDD format
Must exist in AUDIT_TYPES Reference Table
Must exist in LOCAL_PR1MARY_STANDARDS Reference
Table
Must exist in AUDIT_CALASES Reference Table
Must exist in ACCURACYJTYPES Reference Table, if supplied
Text
Date - YYYYMMDD format
Date - YYYYMMDD format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5 5 format
Formatting Rule
RP = Raw Precision Data
Must = I, U, or D
Must exist in STATES Reference Table or be 'TT' for Tribal Site
Must exist in COUNTIES or TR1BAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Number - 2 0 format
Must exist in SAMPLE_DURATIONS Reference Table
Must exist in UNITS Reference Table
Must exist in SAMPL1NG_METHODOLOG1ES Reference Table
Date - YYYYMMDD format
Number - 5.5 format
Must exist in SAMPLING_METHODOLOGIES Reference table
Indicated Value Number - 5.5 format
Must exist in MONITOR Table (only for collocated data)
Text - Up to 10 characters
Must exist with STATE_AGENCIES Table (only for FRM data)
Version 2.12
June 21, 2005
Page 9
-------
Data Input Formats
EPA Air Quality System
RS Annual Summary Data
Field Name
Transaction Type R
Action indicator R
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Duration Code R(LU)
Reported UnitR(LU)
Method Code R(LU)
Summary Year R
Exceptional Data Type ID R
Count of Observations
Count of Exceptional Events
Maximum Value
Maximum Value Date
Maximum Value Time
Maximum Value 2nd Highest
Maximum Value Date 2nd Highest
Maximum Value Time 2nd Highest
Maximum Value 3rd Highest
Maximum Value 4th Highest
Maximum Value 5th Highest
Minimum Sample Value
Arithmetic Mean
Arithmetic Standard Deviation
Geometric Mean
Geometric Standard Deviation
Percentile Sample Value - 10th
Percentile Sample Value - 25th
Percentile Sample Value - 50th
Percentile Sample Value - 75th
Percentile Sample Value - 90th
Percentile Sample Value - 95th
Percentile Sample Value - 98th
Percentile Sample Value - 99th
Percent of Observations
Count of Half-MDL Substitutions
Formatting Rule
Must exist in Reference Table (RS = Annual Summary)
Must = I, U, or D
Must exist in STATES Reference Table or be 'TT' for Tribal Site
Must exist in COUNTIES or TRIBAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table with Parameter
Must exist in SAMPLE_DURATIONS Reference Table
Must exist in UNITS Reference Table
Must exist in SAMPLING_METHODOLOGIES Reference Table
Date - YYYY format
Number - 5.0 format
Number - 5.0 format
Number - 5.5 format
Date - YYYYMMDD Format
Date - hh:mm format
Number - 5.5 format
Date - YYYYMMDD format
hh'nim format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 5.5 format
Number - 6.4 format J
Number - 5.0 format —"-l^
At least one of these
fields must be valued
on an Insert or Update
Version 2.12
June 21,2005
Page 10
-------
EPA Air Quality System
Data Input Formats
RB Blanks Data
Field Name
Transaction Type R
Action Indicator R
State Code / Tribal Indicator R
County Code / Tribal Code R
Site ID R
Parameter R
POCR
Duration Code R(IU)
Reported UnitR(LU)
Method Code R(KU)
Blank Type R
Blank Date R
Blank Time R
Blank Value X(l)
Null Data Code X(IU)
Qualifier Code-1
Qualifier Code -2
Qualifier Code -3
Qualifier Code -4
Qualifier Code -5
Qualifier Code -6
Qualifier Code -7
Qualifier Code -8
Qualifier Code -9
Qualifier Code-10
Alternate Method Detectable Limit
Uncertainty Value
Formatting Rule
RB = Raw Blanks Type
Must = 1, U, or D
Must exist in STATES Reference Table or be "TT for Tribal Site
-Must exist in COUNTIES or TR1BAL_AREAS Reference Table
Must exist in SITES table with the {State Code, County Code} or
Tribal Code
Must exist in PARAMETERS Reference Table
Must exist in MONITORS table (with Parameter)
Must exist in SAMPLE_DURATIONS Reference Table
Must exist in UNITS Reference Table
Must exist in SAMPLING METHODOLOGIES Reference Table
Either 'FIELD' or 'TRIP'
YYYYMMDD format
hh:mm format
Number- 10.5 format
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Must exist in QUALIFIERS Reference Table, if valued
Number - 5.5 format
Number - 6.5 format
Version 2.12
June 21,2005
Page 11
-------
United States
Environmental Protection
Agency
Office of Air Quality Planning & Standards
Outreach & Information Division
National Air Data Group
Research Triangle Park, NC 27711
AQS Fundamentals
Version: 1.0
Date Revised: September 5,2006
Version 1.0
-------
United Slates Office of Air Quality Planning & Standards
Environmental Protection Outreach & Information Division
Agency National Air Data Group
Research Triangle Park, NC 27711
Version 1.0
-------
AQS Fundamentals Introduction
1 INTRODUCTION 1-1
1.1 A BRIEF HISTORY OF AQS 1-1
1.2 SOWHATISAQS? 1-2
1.3 DATA IN AQS 1-2
131 Sues . . 1-2
132 Monitor Description Information . . .. .1-3
133 Ambient Air Quality Data . . . .... 1-3
134 Putting the Data Together . .... ... . 1-3
2 WHO AM I? YOU AND YOUR USER ID 2-1
2 l REGISTERING FOR A NEW USER ID 2-1
2 2 LOGGING ON TO AQS FOR THE FIRST TIME 2-2
23 LOGGING INTO AQS 2-3
237 mien Things go Right .. .... .. 2-3
232 When Things Go Wrong . . ... 2-4
24 CHANGING YOUR PASSWORD 2-5
241 New Password Rules.. . . . . • 2-5
242 When Things Go Right . . . . . 2-6
243 When Things Go Wrong 2-6
2 5 SYNCHRONIZING YOUR PASSWORD WITH CDX 2-7
257 When Things Go Right . ... . . 2-7
252 When Things Go Wrong . . ..2-8
3 CDX - MOVING FILES AROUND 3-1
3.1 GETTING TO CDX 3-1
311 Accessing CDX Directly .... 3-2
3.72 Accessing CDX via AQS . ... 3-2
3.2 TRANSFERRING FILES TO CDX 3-3
3.3 RETRIEVING / DELETING FILES FROM CDX 3-4
3.4 WHERE TO GO FOR CDX HELP 3-6
4 AQS APPLICATION 4-1
4.1 ABOUT ORACLE FORMS 4-1
4.2 AQS MAIN MENU & ICONS 4-3
4.3 ACTION FUNCTIONS-ORACLEFORMS JARGON 4-5
4.31 Save versus Rollback .... . . .... . 4-5
432 Navigation Buttons (Main Menu & Exit) . . 4-6
4.3 3 Oracle Form Functions (Edit, Query, Block. Record & Field) .. 4-6
434 Working in "Tribal Mode" 4-9
4.4 HELP 4-10
441 Application Help 4-70
442 Other Help Menu Items ... 4-11
4.4.3 Field Level Help 4-11
444 User Manuals and Guides ... . - 4-12
45 SESSIONS & SCREENING GROUPS 4-12
4.57 About Sessions . . . . . 4-72
4.52 About Screening Groups .... ... .. . 4-73
4.53 "ReadOnly" Versus Screening Group Access . 4-73
454 Changing Your Session Profile 4-74
46 ADMINISTRATION TOOLS 4-15
Version 1.0 \.\
-------
AQS Fundamentals Introduction
47 RETRIEVALS-GETTING DATA OUT OF AQS 4-15
47.1 Overview 4-16
472 What is Returned . 4-16
4.7.3 Tribal Mode 4-17
4.7.4 Creating a Report .. 4-18
4.7.5 Monitor Selection and Area Selection Tabs 4-22
476 Saving Criteria Sets 4- 30
477 Generate Report .. . . .. 4-31
47.8 Retrieving Previously Executed Reports 4-32
4.8 MAINTENANCE - REVIEWING, EDITING, AND CREATING DATA 4-34
4.8.1 Working as "ReadOnly" vs with a "Screening Group" 4-34
48.2 Types of Data Available .... . 4-35
483 Executing a Query 4-37
4.9 BATCH PROCESSING - WORKING WITH LARGE AMOUNTS OF DATA 4-38
491 Formatting AQS Data . .... 4-38
4.9.2 About the Batch Process . . . 4-43
493 Batch - When Things Go Right .. . . 4-46
49.4 Batch - When Things Go Wrong 4-47
4.10 WORKING WITH CORRECT 4-47
410.1 Search and Replace 4-49
410.2 Delete All Selected . . 4-49
4.103 Delete by Screening Group . 4-49
5 APPENDIX A - INITIAL AQS SETUP 5-1
5.1 ORACLE JlNiTiATOR 5-1
5.2 JAVABEAN CERTIFICATE 5-1
5.3 CDX REGISTRATION 5-2
Version 1.0 I-2
-------
AQS Fundamentals Introduction
1 Introduction
Let me guess: You currently work in the air quality division of an environmental
information organization. Your boss has dumped a stack of floppies and CD's on your
desk and has told you to send this stack of air quality data to the national database
repository. What's more, you have also been tasked with getting air quality information
from other organizations. You are in a panic! Where do I go?!? What do I do when I get
there?!?
Never fear! The United States Environmental Protection Agency (USEPA) is here to
help! The place where you want to go is the Air Quality System, or AQS. AQS is THE
data source for ambient air quality data in the United States. AQS has information about
air quality data beginning in 1957 and continuing to present day. There is information
about where air quality monitoring is taking place, what kinds of pollutants are being
monitored, and the data itself. State, Local, and Tribal air quality organizations submit
millions of data points to AQS annually. These data are stored and summarized in
multiple forms. There are summarization that are generic (such as annual averages,
highest values, and number of observations) and some are reflections of the National
Ambient Air Quality Standards (NAAQS). AQS is regarded as the most comprehensive
resource for air quality information available.
Does all of this sound too good to be true? Well, there is a catch. Given that AQS is so
large and some of the summarizations are rather complex, the data may not necessarily be
handled in a way that will always make sense to you. That is where this guide comes into
play. It is intended to simplify the sometimes confusing process getting data in and out
of AQS.
This guide offers step-by-step instructions on performing many of the basic functions
available through the application. AQS is constantly changing based on user suggestions
and changes in the air quality regulations. Consequently, as time goes on, you may find
that some of the screens may differ slightly from the examples presented. However, as
with everything complex and wonderful, there are always new things to leam and
discover. Hopefully, this guide will give you the background and fundamentals required
to understand the premise and allow you find the resources required to get you the
information you need.
1.1 A Brief History of AQS
In response to the promulgation of the Clean Air Act of 1970, the Storage and Retrieval
of Ambient Data (SAROAD) system was created. Eventually, interest in other types of
air quality data grew, and a new system was created. The Aerometric Information
Retrieval System (AIRS) stored not only the data from SAROAD, but also had emission
inventories from stationary point sources as well as estimated emissions from
automobiles. Each of these different types of data constituted their own subsystem within
AIRS. The SAROAD portion was called "AQS" for "Air Quality Subsystem". In 1999
it was decided that it would be beneficial for EPA to move their national systems to
Version 1.0
1-1
-------
AQS Fundamentals
Introduction
client-server architecture (your PC handles some of the workload) rather than the
traditional mainframe model that had been used in the past. As part of this decision
making process, it was decided that AQS would be the only AIRS component that would
be migrated to this new architecture. The "S" in "AQS" changed from "Subsystem" to
"System", since it was no longer a component of a larger system, So you may hear AQS
referred to as "AIRS" or "AIRS-AQS" or even as "that new-fangled SAROAD thing".
They all mean the same thing.
1.2 So what is AQS?
Named User
CDX
Figure 1-1
As illustrated in Figure 1-1, AQS is actually three different systems that act as one unit:
• AQS Application: Most people find working with a database very confusing. So
"front-ends" are built to help you out. These are the forms and reports that the
user interacts with in order to navigate through the system.
• AQS Database: A relational database which houses the ambient air quality
monitoring data
• Central Data Exchange (CDX): Think of CDX as "Windows Explorer" for AQS.
This component transfers and manages files to and from AQS.
1.3 Data in AQS
OK... There is no getting around it; AQS is a database and many people find databases
very confusing. So instead of talking about AQS in database technical jargon, let's talk
about the type of data that exists in AQS. AQS can be broken down into 3 major
categories of data:
1.3.1 Sites
Identified by:
• State, County, and Site Id OR
• Tribal code and Site Id, if it is located on Native American lands
Description:
Version 1.0
1-2
-------
AQS Fundamentals Introduction
Site information refers to where on earth the monitoring site is located. A site is
identified by its. A site can also be identified by the combination of its. Such
things as latitude/longitude coordinates, what geopolitical areas it is in (like what
county, what city, what EPA Region, etc), and local names (such as local address
and the local site name for example) are kept here. In addition, information about
nearby roads is also stored with the site information.
1.3.2 Monitor Description Information
Identified by:
• The site where the monitor is located AND
• The pollutant code AND
• POC - Parameter Occurrence Code. Used to uniquely identify a monitor if
there is more than one device measuring the same pollutant at the same site.
Description:
For the purposes of AQS, a monitor does not refer to a specific piece of
equipment. Instead, it reflects that a given pollutant is being measured at a given
site. Monitor description information covers several different types of
information about how the monitor is configured. When was the monitor
operational? How high is the probe? Where is the probe located? How close is
this monitor to the highway? What monitoring networks does it belong to? What
is the objective of this monitor? Who owns and operates this monitor? These are
all good questions that can be answered with this type of information.
1.3.3 Ambient Air Quality Data
Identified by:
• The monitor where the measurement was taken
• The time when the sample was taken
Description:
There are a few different types of data stored within AQS:
1. Raw Data: This is by far the most abundant data m the database. The raw data
are the individual measurements collected from the monitors. To date, there are
over 1 billion raw data values available through AQS.
2. Precision and Accuracy Data (P&AV In order to ensure that the data collected at
the monitoring sites is of high quality, systems of checks are performed. This
data reflects the results of these evaluations.
3. Summary Data: Because there is so much raw data submitted to the system, the
system creates multiple levels of summaries of the raw data. The data is
summarized on a daily, quarterly, and annual basis. Additionally, if there is a
national standard for pollutant with a special averaging time (such as a running 8-
hour average for example), the system will create these summarized averages as
well.
1.3.4 Putting the Data Together
So there you have it! Sites are established to monitor for many different pollutants. The
monitors are located at these sites and there is lots of descriptive information associated
with these devices. Each monitor can collect a bunch of data. Each data point is
Version 1.0 1-3
-------
AQS Fundamentals
Introduction
collected at a given time by the monitor. Understanding these basic definitions is critical
in understanding how the data is organized in the system. So, since pictures can paint a
thousand words, take a look at Figure 1-2. The thing to take home are the basic
properties of the three major types of data and that as you move from top to bottom, the
volume of data you are dealing with greatly increases:
Less Data
Sites
State Code
County Code
Site ID
Tribal Code
Where Are the Samples
Being Taken?
Monitors are Located at Sites
I
Monitors
AQS Site ID
Parameter Code
POC
How are the Monitors
Set up / Configured?
Monitors Collect Data
More Data
Data Table
Monitor ID
Date / Time of Sample
The data being
collected
Figure 1-2
Version 1.0
1-4
-------
AQS Fundamentals Who Am I? You and Your User ID
2 Who Am I? You and Your User ID
Remember Figure 1-11 Keep m mind that AQS is composed of a few different pieces:
• AQS Application: Most people find working with a database very confusing. So
"front-ends" are built to help you out. These are the forms and reports that the
user interacts with in order to navigate through the system.
• AQS Database: A relational database which houses the ambient air quality
monitoring data
• Central Data Exchange (CDX): Think of CDX as "Windows Explorer" for AQS.
This component transfers and manages files to and from AQS.
Each of these different pieces resides on a different server at the National Computer
Center. As such, you need to be registered on each of these different machines.
Fortunately, as part of the user registration process, you will only have one user id. We
will discuss a little later how you go about making sure you have the same passwords for
the different accounts.
2.1 Registering for a New User ID
The process for obtaining a new user id is fairly straight-forward.
• STEP J: Fill out the "AQS User Registration Form". Keep in mind that the form
must be signed by the AQS contact at your agency. An Adobe portable document
file (pdf) version of the form can be obtained at
httt>://www.epa.gov/Un/airs/airsaqs/registration.htm
• STEP 2: Read and sign the "AQS User Security Guidelines'". An Adobe portable
document file (pdf) version of the form can be obtained at
http://www.cpa.gov/ttn/airs/airsaQS/rcgistration.htm
• STEP 3; Forward the completed forms to the appropriate person. The
"appropriate" person depends on the kind of agency that you are representing.
c; If you are a State, Local, or Tribal government official, you forward the
forms on to the EPA Regional contact in which you live.
:; If you are a contractor working on a federal contract, you should give the
forms to your EPA contract or project officer rather than the EPA
Regional contact. The contract or project officer will then contact their
ADP coordinator to establish AQS user ids.
• It should be noted that when establishing user ids, the ADP coordinator will
establish an Oracle account on AQS.
• There is no cost for having an account, but it is required so we may track usage
and to enforce security within the application.
Although none of these steps are difficult, it always seems you get bogged down by the
red tape of processing your request. If you have problems with obtaining your user id,
feel free to contact:
Version 1.0 2-1
-------
AQS Fundamentals Who Am I? You and Your User ID
• Your EPA Regional contact (list of current contacts available at
http://www.epa.gov/ttn/airs/airsaqs/contacts.htm)
• EPA Helpdesk at 1-866-411-4372. Keep in mind this is the number for all
applications run by the EPA, so be sure to mention that you are trying to find out
what the story is with your AQS user id.
Once you obtain your password, you will be assigned a temporary password. This
password will expire the first time you enter the application. You will need to change it
and then synchronize it with your CDX id. How do you do that? Funny you should ask!
Read on!
2.2 Logging on to AQS for the First Time
You've completed one big hurdle.... You have a user ID. Now it is time to log on to
AQS. Ready? Let's go!
• STEP 1: Go to http://www.epa.gov/uWairs/airsaqs/aqsweb/aqswebhome.htm.
This web page provides the link to the AQS application. But if this is the first
time that you are accessing AQS from a given computer, go on to "STEP 2". If
you have signed on to AQS at least once on your current computer, proceed to
"STEP 3".
• STEP 2: YOU ONLY NEED TO DO STEP 2 ONE TIME FOR A GIVEN
COMPUTER.... EVER! Click on the "AQS Initial Setup" hyperlink. This link
will provide you with a set of instructions as to how to go about downloading the
components needed to properly install the JInitiator software. This process also
will also allow you to utilize additional AQS related tools (such as Oracle
Discoverer). For a full list of the instructions on this setup, please refer to
Appendix A.
• STEP 3: Click on the "Proceed to AQS" link at the bottom of the page.
• STEP 4; You will be directed to a page indicating that you are about to enter a
federal government computer application and that you agree to the terms listed. If
you are satisfied with the statement, click on the "I agree to these terms and want
to enter the AQS application" hyperlink.
Once you complete "STEP 4", you are on your way to the AQS application. I want you
to take a look at some thing here. Notice mat two windows are opened. One is just a
boring gray screen and the other contains the login window (See Figure 2-7). DO NOT
CLOSE THE GRAY WINDOW! You can minimize it, shrink it, or move it.... Just
don't close it. You need this window to stay open as long as you are in AQS in order for
some of the reporting functions to work properly.
Version 1.0 2-2
-------
AQS Fundamentals
Who Am I? You and Your User ID
Figure 2-1
The last step in logging into the system is to enter your newly acquired user name and
password in the appropriate fields on the form. From this point, it is just like logging on
for anyone else. So if you need more help, keep reading the next section!
2.3 Logging into AQS
2.3.1 When Things go Right
In order to log on to the system, have your user id and password ready. Go to the
following link:
http://www.epa.gov/ttn/airs/airsaqs/aqsweb/aqswebhome.htm
This web page serves as the "launching pad" for giving you
access to AQS. From here, click on this button to continue.
Proceed to
AQS
You will be directed to a page indicating that you are about to enter a federal government
computer application and that you agree to the terms listed. If you are satisfied with the
statement, click on the "1 agree to these terms and want to enter the AQS application"
hyperlink. You are now at the AQS logon screen which looks like Figure 2-1. Notice
Version 1.0
2-3
-------
AQS Fundamentals
Who Am I? You and Your User ID
that two windows are opened. One is just a boring gray screen and the other contains the
login window (See Figure 2-1). DO NOT CLOSE THE GRAY WINDOW! You can
minimize it, shrink it, or move it.... Just don't close it. You need this window to stay
open as long as you are in AQS in order for some of the reporting functions to work
properly.
You will also see that you have to specify the database to which you are connecting.
Unless you are in some kind of a training class, you should connect to the "AQSPROD"
database. For AQS, the user id, password, and database can be entered in either upper or
lower case.
2.3.2 When Things Go Wrong
When you fill out the fields, press the "Connect" button. If you typed everything in ok,
you will enter the application. If not, you will get one of the following errors:
ORA-12545 Connect failed because target host or object
does not exist
Error #1
You will get this error if you
either mistype (or don't type
in) the "Database" field.
Remember: it should be
"AQSPROD"
ORA-01005 null password given, logon denied
Error #2
You will get this error if you forget to
enter the password or the username
field.
Loqon Error
ORA-01017 invalid username/password. logon denied
Error #3
You will get this error if you
mistype either the usemame or
password fields. If you have 3
consecutive "Invalid Usemame /
Password" errors, the application will automatically close and your account will be
locked. To unlock the account, you need to call the EPA Helpdesk at 1-866-411-4372.
FRM-40735. ON-ERROR trigger raised unhandled
exception ORA-03114
QK
Error #4
Version 1.0
2-4
-------
AQS Fundamentals
Who Am I? You and Your User ID
You will get this error if you hit the "Cancel" button.
Loaon Error
ORA-28000 the account is locked
QK
Error #5
If you have had more than 3 consecutive "Invalid
Useraame / Password" errors, your account will
be locked. The only way to unlock the account is
by calling the EPA Helpdesk at 1-866-411-4372.
Error #6
OK, so it's not an error, exactly. This is a warning letting you know that your password
is about to expire (or has expired). It lets you know how many days you have before you
are required to change it. If you need to change your password, please look in the section
of this guide "How to Change Your Password".
2.4 Changing Your Password
Your AQS and CDX passwords will need to be changed at least every 90 days. That may
seem to be exceedingly frequent, but that is the price you pay for having access to federal
government mission critical application!
2.4.1 New Password Rules
Because there are lots of clever people out there, your new password must meet certain
rules in order for it to be valid and difficult to guess. So the real "Step 1" of this process
is to make up a good password. In order for your password to be valid, it must meet ajl
of the following criteria:
1. Must be at least 8 characters long
2. Must contain at least 1 number
3. Must contain at least 1 character
4. Must begin with a character
5. Passwords can only contain A-Z and 0-9
6. You cannot reuse an old password EVER.
7. Password cannot be a common word or a common word followed by a string of
digits
8. Password cannot be your user id followed by a string of number.
9. Password cannot be your user id in reverse order followed by a string of number.
10. Password cannot be the name of the server or the oracle instance name
(AQSPROD) with or without trailing digits
11. Password cannot be the user's first or last name with or without trailing (or
leading) string of digits.
12. Password cannot be the user's first initial and last name with or without trailing
(or leading) string of digits.
Version 1.0
2-5
-------
AQS Fundamentals
Who Am I? You and Your User ID
2.4.2 When Things Go Right
Here are the steps needed to change your AQS password (we'll explain how to change
your CDX password in a second):
Step #1
From the Main Menu, click on "Admin" and
then "Security" under the "Admin" item.
; on
'> i ,'
Retrieval Maintenance Critic?!
Reference
Step #2
On the section of the Security form labeled "Change Your Oracle Passwords", enter your
new password in the "New Password" field next to the "AQS" label. Press the tab key
(or click the "Confirm password" field) and fill out the "Confirm password" field with
the same new desired password. Click on "OK" when complete.
• Change Your Oracle Passwords
AQS New Password- j
Confirm password1 l_
You should receive this if everything is OK. Press
the "OK" button to continue.
OKjj
^eetm'ty- Chanqe Password
-20459 Password changed
OK j
2.4.3 When Things Go Wrong
Error #1
-20458 Password does not match confirmation Please
re-enter
Your "New Password" must
equal your "Confirm password"
entries. If they don't, you will
get this error Oust admit that
you don't type very well!).
Error #2
If your "New Password" violates any of the rules listed in "New Password Rules" section
above, you will get a message specifying what error has occurred.
Version 1.0
2-6
-------
AQS Fundamentals
Who Am I? You and Your User ID
2.5 Synchronizing Your Password with CDX
The folks who brought you AQS realize that it is a pain to keep up with multiple
passwords and user ids. Unfortunately, AQS relies on using CDX for handling files to
and from the application. Additionally, CDX is a completely different application from
AQS. Consequently, it requires its own logon information. Fortunately, the user ids
between the two systems are the same. They also have their passwords set to the same
initial value when your account is created. AQS has a mechanism in place that assists
users in keeping their passwords between the two systems the same. That saves some
brain cells in having to remember multiple passwords as you move though the system.
If you have just changed you AQS password, it is a good idea to exit the AQS application
and come back in using the recently changed password. There have been instances where
the application gets confused as to what the right password is to synchronize with CDX if
you don't leave and come back in.... better safe than sorry!
2.5.1 When Things Go Right
If you have managed to change your password, this part will be a piece of cake! All that
you need to know is what your current password on CDX is (if you've been keeping your
password in sync with AQS, it should be your "old" AQS password). Easy peasy!
Step #1
From the Main Menu, click on "Admin" and
then "Security" under the "Admin" item.
Step #2
On the section of the Security form labeled "Change Your Oracle Passwords", enter your
current CDX password in the "Current CDX Password" field next to the "CDX" label.
Click on "Synchronize CDX" when complete.
Change Your Oracle Passwords
AQS New Password: J 'Confirm password j
CDX Current COX Password )
! L ~
; Ij Synchronize
"i :
CDX | |
You should receive the
following message if
everything goes well:
-20708 Password changed to lower case version of
current Oracle password
Version 1.0
2-7
-------
AQS Fundamentals Who Am I? You and Your User ID
2.5.2 When Things Go Wrong
The only thing that can go wrong is if you don't remember the old password. In this case
you will get an error saying just that. Suggestion? Well, either correctly type in the old
password or call the CDX Helpdesk (1-888-890-1995) and have them reset your CDX
password.
Version 1.0 2-8
-------
AQS Fundamentals
CDX - Moving Data Around
3 CDX - Moving Files Around
EPA's Central Data Exchange (CDX) is the gateway for state and local governments,
industry, and tribes to provide their environmental data to the EPA. As mentioned
earlier, CDX is a different application from AQS. So why are we talking about it?
Because eventually you will want to either put data into AQS or to get data out of AQS
and CDX plays a very big role in making that happen.
CDX is a shared application. If there is an environmental program that requires the
transferring of data files, there is a good chance CDX is involved. As such, each user
with a CDX account is allocated a certain amount of space on the network. Once you
have used your space up, there is no more until you do some house cleaning! So
remember to keep your area neat!
CDX supports a wide variety of data formats to and from its system. However, for our
purposes let's just say that CDX provides all that we need to get files in and out of AQS.
In this section we will show how to move a file to make it available to AQS.
You can go to the following areas in CDX (Accessed by the links on the left side of the
CDX screens:
• About CDX; Provides generic information about the
functions and capabilities of CDX
• MvCDX: This is the "launching pad" for all the
activities that you can perform in CDX. See Figure 3-2
for an example picture.
• Inbox; Link to new files sent to your account
• Change Password; Function that allows you to change
your CDX password
• Frequently Asked Questions; Your standard FAQ type
ofpage
• Help & Support; If this excellent guide falls short for
your needs, you can look here for more resources (perish
the thought!)
• CDX Home; The entry point for CDX
• Terms & Conditions; This is some legalese to tell you
what you legally can and can't do with this application
• Logout; Logs you out of CDX when you are all done
About CDX
MyCDX
Inbox
Change Password
Frequently Asked
Questions
Help X. Support
CDX Home
Terms L Conditions
Logout
3.1 Getting to CDX
You will soon see there are lots of different ways to do the same thing within AQS. This
is illustrated by how to access CDX. You can access CDX in either of the following
ways:
• Go directly to the CDX website OR
• Access CDX through AQS
Version 1.0
3-1
-------
AQS Fundamentals
COX - Moving Data Around
3.1.1 Accessing CDX Directly
Once you register for access to AQS, you automatically are registered as a user of CDX.
Once you have obtained your user id and password you can log into COX by going to the
following URL:
https://cdx.epa.gov/SSL/cdx/login.asp
This will take you to the following screen:
vf d > UriM SUM 6»«aftji>e>r«lpMiMM«fe>c!95eul
Af«*«l ft tKI of All
Itetf MB] fratiJfay-'yt Mitt*;
Figure 3-1
3.1.2 Accessing CDX via AQS
As mentioned earlier, AQS uses CDX as the "traffic cop" of files that are generated from
AQS as well as the files that are used to input data to AQS. Consequently, there are
multiple links to the CDX login screen (Figure 3-1}. Each of these methods will open up
a new Internet Explorer window. That is to say that the AQS application is still running
in another window.
3.1.2.1 From the Main
Menu
Perhaps the most direct link to the
CDX login screen is on the AQS
Main Menu under the "Retrieval"
entry. This menu item is available
at the top of every screen in AQS.
vcllon yelp Session
Maintenance Critical Rev
Browse Annual Summary
BEJowse Monitoring Season
BiQwse Sample Methodology
BioYYse State Threshold
Browse Parameter
BrowsE Audits
:.trnc!.T
Standard Report Selection
Version 1.0
3-2
-------
AQS Fundamentals
COX - Moving Data Around
-«. *i ' r
K a
3.1.2.2 From the Retrievals
Standard Selection Form
Since files can be created from AQS and
stored in your CDX account, AQS has a
burton that will take you to the CDX Login
screen.
(run b«P 3«s»i> (Mi BlIUu! tjttimunn £i*cuR
H Ji & ^ - Ql ft ffl«T
S«O\ CQnM
3.1.2.3 From the Batch
Processes Form
Since files can be submitted to
AQS from your CDX account,
AQS has a button on the batch
processes form that will take you
to the CDX Login screen. Notice
that this form also provides a
view of the files that are available
to be processed by AQS.
Welcome,
Mr JONATHAN MILLER
Ceniial Data Exchange-MyCDX
Last Login September 13.2006
Registered Since May 9, 2003
Recertification Date May 9, 2003
CDX Registration Status Active
Use this link to RECEIVE
files FROM EPA
You have 2 new messages in vour Inbox
Clianne System
Password
Edit Persoii.il
IlltOlllliTtlOII
Available Account Profiles:
• AQS An Quality Syslc-'n
Edit Current
Account Profiles
Add New
Employer Profile
Use this link to
SEND files TO EPA
Figure 3-2: MyCDX Page
3.2 Transferring Files to CDX
One of the primary functions AQS serves is the ability to store large amounts of ambient
air quality data. In order for that to happen, there has to be a mechanism to getting the
data files from here to there ("here" being your computer and "there" being the EPA).
Version 1.0
3-3
-------
AQS Fundamentals
CDX - Moving Data Around
AQS relies on CDX to manage the movement of files. So now that you are in CDX, how
do you go about moving files?
Step 1: Log on to CDX by filling out the usemame and password fields of Figure 3-1
and clicking "LOGIN".
Step 2: From the MyCDX
page (see Figure 3-2), select
"AQS: Air Quality System -
File Transfer". This will
take you to the "Air Quality
Subsystem Submittal Form".
Step 3: Click on the
"SELECT" button. This will
open a file dialog box where
you can select files located
on your local PC or network
device.
Step 4: Select the file(s) that
you want to upload from the
file dialog box. Click
"Open" when complete.
You will return to the Air
Quality Subsystem Submittal Form
with the files you selected
appearing in the box. When you
have all the files selected, click on
the "SEND" button below the box
of the list of files. This will open a
progress bar that will show how
long you have until your files have
been transferred.
US CiHimuMHttlfnnttlanAm»*tr
AOS WBB SUBWTIAI. FORM - STANDARD FORM
TUB lorm a lor mlomng ite MUIIMOIS to «QS To pet un lot u0oed use me Select boon To
remove esngto Ha from trisuptoed use me Remove buaon To remove elites from ttauotoad, use
me deer button
Fin aubmkud mjy be up to 1M MB In ebe
[OC laqsUtteRaguestiU6Q3AII
-------
AQS Fundamentals
CDX- Moving Data Around
Step 2; From MyCDX (see Figure 3-2), select "AQS: Air Quality System -File
Transfer". This will take you to the "Inbox" hyperlink. This will only be underlined if
you have files to download. This link will take you to a screen that looks something like
this:
US. enviroMnMtml Protection Agtncy
Your AQS Report File Is Availablg CDXTestFile zip
Receipt acknowledgement
Youi AQS Report File Is Available YR_Joo3_PM25 J>71 o ap
Download Selected Files
Wed 9/13/200610 36 AM
Tue 8(15(200614 58PM
Fn 10/1/2004 11 01AM
Figure 3-3
So what do we have here?
First have a selection check box. Use this box to indicate what files you want to deal
with. So check the boxes that you want. Next we have a file type filter. This column
(labeled "All" on Figure 3-3) is actually a toggle as to what kinds of files are displayed.
Your choices are:
"All" Shows read and unread messages
SI Only shows unread messages
^ Only shows previously read messages
Each time you click on the icon, it will change from "All" to El to Q. These icons also
appear next to the files below this column header. £3 means that a file has not been read.
If there is no icon next to the filename, it means that it has previously been read. Next is
the address where the file originated. Since these files are generated by AQS processes,
the "From" will always be some kind of system user. Clicking on this column label will
sort the data by the "From" addressee. Next is the subject. Again, this is a generic
subject generated by AQS to give you some idea from what process was this file
generated. Clicking on this column label will sort the data by the "Subject". Finally is
the date. This indicates when the file was created. Clicking on this column label will
sort the data by the "Date".
Step 3; Select the file(s) that you are interested by clicking the check box.
• S«e»r«¥ Wwirin
Do mu
V/hie Hat horn B> Infernal an be moU IS. Ik lype can
POorM* hm you corvula llyoudonotlt
open a lava Iht tofMaia Whd i Hie nfc?
Step 4: Click either the "DELETE" button (to
remove the file), or "Download Selected Files" to
transfer the files to your PC. If you clicked
"Download Selected Files", you will be prompted
with this message box. Click "Open" to see the
files, or click "Save" to save the zip file to your
local machine.
Version 1.0
3-5
-------
AQS Fundamentals COX - Moving Data Around
3.4 Where to go for CDX Help
Do you still have questions on what CDX can do? There are a few different sources for
help with CDX:
• CDX Helpline: 1-888-890-1995. This helpline is available Monday - Friday,
8 am - 6 pm eastern time.
• Email: Send in your questions to CDX Helpdesk personnel at
EPACDX@csc.com. Include "Technical Support" in the subject line.
• Fax: You can also fax in your question to the helpdesk personnel at
1-301-429-3905. Fax support is available Monday - Friday, 8 am - 6 pm eastern
time.
Version 1.0 3-6
-------
AQS Fundamentals
AQS Application
4 AQS Application
As we have mentioned before, AQS contains a lot of data. Navigating through all that
data at times can feel like walking into an untamed jungle. Fortunately, there is a clear
path through the chaos. The AQS application is designed to take some of the complexity
out of dealing with the AQS database. Ready? Let's go!
4.1 About Oracle Forms
A form is piece of software that helps you navigate through the system. It may provide a
link to another form (like the Main Menu form), or it may display information contained
in one or more database tables (like the Raw Data Form). AQS utilizes many forms to
display the information in the database. A different form exists for each type of data
within AQS. For more information about the different forms, please go to section 4.8
which talks about the "Maintenance" function within AQS. But for now, we are going to
look at the various components of a form. As an example, we will use the Raw Data
form (basically because it has just about everything that we can expect to see in a form!).
Action Help §lte Monitor Audit Event Sample Values PBecislon A£curacy Blanks summary Concurrence Ind MajnMenu
'
am"
iW MW^ rSiLjs&^™ X'^JC'^* Jvx^u1' O^xiw £
Save
(Record. 112^736
Figure 4-1
«osc»
Version 1.0
4-1
-------
AQS Fundamentals AQS Application
As you can see in Figure 4-1, there are many different things being shown at one time.
Starting from the top, we have the following pieces:
f - - -- 1 TtCeBor f
fiction Help §113 comor AutM iveni Samgie values Pfjecisicn Accuracy llanns summary Concurrence (nfl va;n renu N F orm Clenu
B a £L Z# . %l 'O J «7<»*»A»a:k»' ? ,
Across the top is the Title Bar for the form. In this case, it is the AQS Application Form.
Next is the menu that applies to this particular form. Please note that items on this bar
can change. In fact, you'll notice in this example that the menu items are different here
than they are for the Main Menu. Below the form menu are the icons for the application.
These icons do not change based on the form currently displayed. However, not all icons
apply to all forms. If an icon does not apply, then it will be grayed out.
So what do we have below this? That will depend greatly on what type of form you are
dealing with. So for right now we'll talk about the different components of the forms
rather than how this particular form functions. To see particular features of a given form,
please refer to section 4.8 (Maintenance) of this document.
Kl _ _ Title Bar
Raw Data Comments Ta"s
TribdCodB Stale County Site Parameter POC BegmlNte: EndPUg Standard Unto
| ,37~ JJET '0001 |88101 ' ~
Raw Data ** ""P""* 3mlml
Up ID Date Time kid SarnpteVaje Sample Value M kid hid Date Code Description
_ ^.. _ _ ^_ _ ,~IT1
-/T~_ 2006031 frqgQO ft9 | 1
n»c?! ITTl I '
(1 '20060309 DCfyj^V. [17 2
jl~~ 2006030800-00 ,""" |14_8 _ " "~"~ 'F i~
fi 2006030700:00
fT" '2006030600-00 P>xJ-" POOROUAgiv Records wo focus
p 2008030500-00
•h 200603D4 oo-oo j7_2_ ^- Data Blocks
[T~ 2006030300-00
jr|ll~ 2006030200-00
QiuMerCode On-^net r>rc_ QuaWei Type
.v „ ' : i"" .v ** "
Monitor Protocol
Mp ID Pvflnflter Mnpod
i 88101 file
Unt Da
|TbT |T~
•ton ColFrequmc-/ AltMdl
Again, starting at the top we have a title bar. This tells you the following:
• What form your are working with ("Maintain - Raw Data")
• The type of access you have according to your session you signed in with ("Read
Only")
• If you are working in "Tribal Mode". If you are not in Tribal mode, then this text
will not appear.
Next we have Tabs. Tabs do not appear on every form. Tabs provide additional
information for a record that currently has focus. You can tell which record has focus
because it is highlighted in the turquoise color. In this case, the tab will provide any
Version 1.0 4-2
-------
AQS Fundamentals AQS Application
comments provided for the raw data record corresponding to March 11, 2006 (since that
is the raw data record that has the focus).
That leads us into the discussion of data blocks or simply blocks. Different blocks are
separated by a light gray box around their fields. In this example, we have 4 distinct
blocks worth of information:
• Monitor Information
• Raw Data
• Qualifiers
• Monitor Protocols
Blocks are collections of records that have a relationship with another set of data. Um...
what? Well, in this example you have 1 monitor record (37-063-0010-88101-1) that has
a bunch of different raw data value records. So all the records from the raw data block
belongs to the monitor displayed. Well, the relationships just keep going. The data that
shows up in the qualifier block are the qualifiers that have been entered for the raw data
record that has focus. So for monitor 37-063-0010-88101-1 on March 11,2006 (the raw
data record with the focus) has a qualifier code of "V" which means "Validated Value.
The last block indicated how the data on March 11,2006 was collected (what method,
units of measure, and amount of time needed to collect the sample).
The last component of the form to
Enter a query, press F8 to execute. Cji_ist of Values j- j r- •* i r n » a. * u
2—)r:± . ^ y|u&iuivdiue&—i discuss definitely falls into that phrase
Record 1/1 Enter-Qu
LlstofValu "last but not least". It is the information
bar. It is at the very bottom of the form, and can easily be overlooked. But it is always a
good idea to look down there for some very good information. The top line will tell you
what action is expected (in this case, it wants you to enter a query). The first block on the
next line will show you the number of records you have retrieved in your query. BE
CAREFUL! This record count is for the block where the cursor is currently located. For
this example, the cursor was in the monitor block. Sometimes you will see a message
here that says "Record I/?". The "?" means that not all records have been retrieved yet.
The next block shows if you are currently in query mode. What this is intended to show
you is if you are entering filtering criteria, or if the query has already been executed. The
last block will tell you if the field where your cursor is pointed has a list of values
associated with it to help you. As you can see, sometimes these descriptions are cut off.
If they are truncated, just place your mouse over the field you want to see and the full
description will appear (as shown).
4.2 AQS Main Menu & Icons
The AQS main menu and icons guides you through the various functions available to you
within the application. It is important to note that we are talking about the main menu
here, which implies that other menus do exist. If there are sub menus or a change in a
menu that is available, it will be discussed within the section that causes the menu to
change.
Version 1.0 4-3
-------
AQS Fundamentals AQS Application
Many of the common functions that are performed in the system have icons that can be
used to execute the functions. In cases where icons or keystrokes can be used, both will
be provided and then you can decide which you would rather use.
The AQS main menu is always located at the top of the form and looks like this:
Action Help Session Admin Retrieval Maintenance Critical Rev CErtification Batch correct
Here is a brief explanation of each of the elements that appear on the main menu. Each
of these will be explained in greater detail in their own section below.
"Action"
As mentioned before, we are interfacing with an Oracle database here. The action
functions are common database operations that you may want to use.
"Help"
This menu item will guide you towards available system documentation and
explanations of various functions in the system.
"Session"
This is a shortcut to bring you back to the screen where you can select if you want
to be a "Read-Only" or a "Screening Group" type of user while you are in AQS.
"Admin"
You can change your contact information as well as maintain your system
password here.
"Retrieval"
This is the primary method of selecting data from the system. Data can be created
as fixed-formatted reports, work files, or on-line tables.
"Maintenance"
This is an on-line query function that allows you to browse AQS one record (or a
small number of records) at a time.
"Critical Rev"
This is a function that can only be accessed by EPA Headquarters personnel. It
tracks data that has been marked as being questionable for some sort of reason.
The records are periodically reviewed, and data owners are notified if there are
continuing questions. This function is not covered in this document.
"Certification"
This is a function that can only be accessed by EPA Headquarters personnel. It
allows EPA Personnel to indicate that a given monitor-year's worth of data has
passed all the requirements in order to be certified. This function is not covered in
this document.
"Batch"
The batch utilities are used when large amounts of data need to be processed into
the system.
"Correct"
This utility is used in conjunction with the "Batch" utilities. If errors are identified
during the batch editing process, they may be corrected and resubmitted using this
Version 1.0 4-4
-------
AQS Fundamentals
AQS Application
utility. Since the function is so closely linked to the batch process, it is discussed in
detail in the "Batch" section of this document.
4.3 Action Functions - Oracle Forms Jargon
4.3.1 Save versus Rollback
One of the real powers that a database has is that the
information that you are seeing is being shared by everyone.
The data is stored in a common place (in this case, on a server
at the NCC), and follows the same rules for everyone. AQS is
a living, breathing thing. The data within it are constantly
changing. If you have access to a screening group, you may
have the ability to change information in AQS as well. If you
do, you need to understand the concept of "Save" and
"Rollback" as it applies to the database.
Edit
Query
Block
Record
Eield
Help
Window
Tribal Mode On
Tribal Mode Off
4.3.1.1 Save
When you attempt to change data that is stored on tables within
AQS, the change doesn't take effect until you save (or commit)
those changes. You cannot "undo" or "go back" once you save
your changes. If you have saved your changes, your only choice is to manually change
them back. So when do you need to save? Keep in mind that unless you are querying
data, any entry that you make into AQS is probably updating a table. So many times
when you try to leave a screen, you will get a message that looks like this:
Do you want to save the changes you have made?
Yes
No
Cancel
What this is trying to tell you is that you have made a change somewhere in the database
and if you want to SAVE these changes.
So how do you go about saving your data? As usual, there are several options:
1. Since we are in the "Action" menu item section, I guess we should mention this
option first! Click on "Action" -> "Save".
2. As you have already seen, if you try to leave a screen you will be asked if you
want to save your changes. Answering "Yes" will save your changes.
3. Click on the "Save" icon. It is in the upper left corner of the
screen and looks like this >
4. Hit the "F10" key on your keyboard. Note that the action menu will show
alternative key strokes that you can use if they are available.
Version 1.0
4-5
-------
AQS Fundamentals AQS Application
4.3.1.2 Rollback
A "rollback" will erase all changes made to the database since the last time you saved. In
general, AQS does not let you go too far before it makes you save your work, so
honestly, this function is quite limited. However, you may find the occasional need to
erase and start over. So here's the different ways to "Rollback" your work:
1. Since we are in the "Action" menu item section, 1 guess we should mention this
option first! Click on "Action" •> "Rollback".
2. As you have already seen, if you try to leave a screen you will be asked if you
want to save your changes. Answering "No" will rollback your changes.
3. Click on the "Rollback" icon. It is towards the upper right side .^
of the screen and looks like this » W*
4. Hit the "Esc" key on your keyboard. Note that the action menu will show
alternative key strokes that you can use if they are available.
4.3.2 Navigation Buttons (Main Menu & Exit)
••••i^MESSl There are several places within
H3 B &C' ' 1 §>& i D « T "« > • » * - TSS. n -t * ,i . /-.,-, . . .1 . i
•^m^mmmmmmmmmmmmmmmmmJ the AQS menu Structure that have
j a short cut to the "Main Menu".
To the left is a screen capture of
the Main Menu form. There
really isn't anything that you can
do on this form other than use it
as a launching point to all the
other areas within the application.
The other short cut navigation
button that is available is the
"Exit" function. This will end
HBBBI your AQS session. If you have
made changes to the form that you are on, you will get a message prompting you if you
want to save your changes. If you haven't made any database changes since the last time
you saved, you will exit the system. As usual, there is more than one way to exit the
application:
1. Click on "Action" -> "Exit".
2. Hold down the "Ctrl" key and "q" on your keyboard.
3. Click on the "Exit" icon. It is the third icon moving from
left to right.
4.3.3 Oracle Form Functions (Edit, Query, Block, Record & Field)
If you have general questions about Oracle forms, please refer to section 4.1 (About
Oracle Forms). These function all relate to dealing with fields and records displayed in
the forms. Nearly all of the functions provided on the menu are available through the use
of icons and / or short cut keys. In these cases, the alternate methods of executing the
functions will be provided.
Version 1.0 4-6
-------
AQS Fundamentals
AQS Application
4.3.3.1 Edit
The edit functions allow you to perform basic
functions against a field. The following functions can
be performed under the "Edit" list:
Edit
Display List
Ctrl+e
F9
Cut
Copy
Paste
Display list
(Ctrl + "x")
(Ctrl + "c")
(Ctrl + "v")
(F9) - This will produce
a "Drop down" list of entries for the field where
your cursor is currently located (if applicable).
• Edit (Ctrl + "e")
I've included the "Edit" function, but it is a fairly useless function. This is because AQS
controls what mode a record is in based on the authority you have for the record. So
basically, either you have edit authority or you don't. So if you ever find a neat use for
this function, let me know!
Edit
Qount Hits
Fetch Next Set
Shifl*F2
Ctrl+>
Execute
criteria entered.
4.3.3.2 Query
As the name implies, these are functions that you
can do while you are trying to query records from
the database. The following functions can be
performed from the "Query" list:
(F8) - Executes the current query with the filter
Cancel ^ (Esc) - Cancels the current query
Last Criteria - This will load the last set of values for which you queried in
the form.
Count Hits (Shift + F2) - Counts the number of records that the query will
return. This is especially useful when you get a message that says "Record
I/?". Selecting this function will replace the "?" with the number of records
that the query finds.
Fetch Next Set (Ctrl + ">") -
Previous
* £lear
F11
F12
Shrtt*F5
4.3.3.3 Block
Admittedly, these functions also have limited use.
However, they are included for completeness sake.
These controls are really designed for those who
really don't like to work with a mouse. Essentially the "Next" and "Previous" will step
you to different areas of the form, one section at a time (or one Tab at a time if
applicable). This can easily be performed by clicking on the area of the form you would
like to be in. The "Clear" function simply clears entries that are in any field with the
block where the cursor is located. This is duplicated by using the "Enter Query"
function.
Version 1.0
4-7
-------
AQS Fundamentals
AQS Application
Edit
Queiy
Block
Eield
Help
yvindow
Tribal Mode OQ
Tribal Mode Off
Previous
Next
Scroll Up
Scroll Down
insert
Remove
Lock
Shifl'Up
Shrft*Down
Page Up
Page Down
F6
ShifI+F6
4.3.3.4 Record
As you can see from the picture, there are a lot of
functions under this menu item. However, since
most of them have alternate keystrokes and / or
icons, the menu items themselves are pretty
limited in the usefulness. But there are lots of
good icons to introduce here, so let's get going!
Lock
Quplicate F4
Clear Shift*F4
Menu Item
Previous
Next
Scroll Up
Scroll Down
Lock
Insert
Remove
Duplicate
Clear
Description
Takes you to the previous record selected by the
query
Takes you to the next record selected by the query
This will display the previous set of records that can
be displayed on a scrollable block of records
This will display the next set of records that can be
displayed on a scrollable block of records
No real use for this function given that the access to
records in AQS is based on your role in the current
session.
Inserts a new record into the current block below
where your cursor is currently located.
Deletes the entire record where the cursor is currently
located
Makes a copy of the record where the cursor is
currently located and places in the very next record
line. This can only be used in a multi-record type of
block.
Erases all the entries for a given record. It does not
delete the record. It makes all the values for the
record NULL
Goes to the first record selected by the query
Goes to the last record selected by the query
Icon
.<_
>
±
*
S
±3
T
*
Keystroke
Shift +
"Page Up"
Shift +
"Page
Down"
"Page Up"
"Page
Down"
F6
Shift + F6
F4
Shift + F4
Edit
Query
Elock
Record
Previous
fcjext
Qlear
Duplicate
Shin+Tab
Tab
Ctrt*u
F3
4.3.3.5 Field
Much like the "Block" functions, these functions
also have limited use. However, they are included
for completeness sake. These controls are really
designed for those who really don't like to work
with a mouse. Essentially the "Next" and
"Previous" will step you to different areas of the
Version 1.0
4-8
-------
AQS Fundamentals
AQS Application
form, one field at a time in the order determined by the programmer. This can easily be
performed by clicking on the field in the form you would like to be. The "Clear" (Ctrl +
"u") function SSf] simply clears the content of the field where the cursor is located. It is
essentially the same as a "Delete". The "Duplicate" (F3) function ft will copy the
contents of the current cell with the value from the record directly above it. Obviously,
this function will only work if there are multiple records displayed within a given block.
4.3.4 Working in "Tribal Mode"
Remember at the very beginning of this manual we talked about what uniquely identifies
a site in AQS? Actually, I don't remember either, so it's worth mentioning again. There
are two ways that you can select sites in AQS:
1. Specify the State code, county code, and site id where the site resides. This is
the default method of identifying sites in AQS. However, there is another way.
2. Specify the Tribal Code and site id where the site resides. For those sites that
exist on Tribal lands and the owners of the data have identified which Tribal
lands to which these sites reside, you can identify the site by this combination of
values.
To help illustrate this, take a look at the map below:
•*»».»«*»• ; .imi::m»!Mni»
\
#?"V ;& »I Let's say we have a site located at red
star on the map. The site ID for this
(*~«*.« site is "0001". This site clearly would
i$«wtj>v#AiJ#*w». «*
reside within the St. Regis Mohawk
Indian Reservation (Tribal code =
"007"). However, it also resides
within Franklin County, New York
(State code = "36" and county code =
"033"). So, within AQS you could
either identify this site as:
• 36-033-0001 (default mode)
OR
• TT-007-0001 (Tribal mode)
By checking the "Tribal User" box on
your user profile, you set the default method of displaying site id in the Tribal mode
versus the default mode. This method of displaying the sites applies to all forms and
reports within the system.
If you are wondering if you are currently in Tribal mode, just look
at the blue bar at the top of your form. If you are in Tribal mode,
the bar will look like this:
Where the words "Tribal Mode" will appear after the listing of
your currently selected screening group or "Read Only".
So how do you tell AQS that you want to view the data in Tribal
mode or Default mode? Two ways:
Version 1.0 4-9
-------
AQS Fundamentals
AQS Application
1. The last two items on the "Action" menu bar will let you toggle between "Default
Mode" and "Tribal Mode" by either turning the Tribal mode "On" or turning the
Tribal mode "Off.
2. To make viewing data in Tribal mode your default setting when you sign on to
AQS, check the "Tribal User" check box on the "Admin" -» "Security" form on
your user profile (See section 4.5 for screen prints of this form). The Tribal user
check box is in the bottom right comer of the user information of this form.
4.4 Help
Keys
Display Error
About
Clrl*F1
Shirt*F1
Well, I've tried to structure this guide according to the way the items
are laid out on the main menu. As with any good plan, occasionally
one must stray from the plan a bit. The "Help" menu item is such an
item. Many of the most helpful elements of the system are not
accessible through the menu structure. In fact, some aren't even in the
application at all! But we will look at what is available through the
application first.
4.4.1 Application Help
Gosie O
Vf • «f-r
Go >"t B - 6 eoohnarta. gpcoujsokay "^Check. . .
f^A^J^JQ-PocHJpalocto skUp»«doNo»l • LJMil • ®Myy» fctywoKf to MM
'
Accuracy Report
Ad Hoc Reports
Add A New Role
Add A New User
Admin Message of the Day
Air Quality Indei Report
Air Quality Indei Summary Report (AMP41 OS
Air Quality Indei wonfflie roimat
Air Quality Report
All Parameters Format
All Parameters Qu'ck Look Work File Format
All Roles
Annual Summary
Application Administrator
Application Audits
AQI
Batch
Batch load formats
Browse
CADump
California Dump Bucket
Carbon Monoxide Formal
Camon Monoxide Work File Formal
cox
AQS HELP APPLICATION
Welcome to the AQS Help System
Please select the Index tab to access a hst of informative topics on the AQS Applicanoa Or use Seaich to
End a specific word or phrase
Additional documentation and training materials can be found at this address
1,11 •,'^\-' tpj..u1i-;«'Jam faa ..i '"r.u
AQS Help Desk 1 (866) 411 4EPA
HdpRmmnl 1402-02-06
41 »J local rtrjnn
Figure 4-2
Of the four choices that appear on the "Help" menu, the first item ("Help") is the most
useful. When you click on "Help" (or Fl if you prefer), you will be taken to the on-line
help. This is an application that is external to the application and will open another
browser window. The topics are organized in two ways:
Version 1.0
4-10
-------
AQS Fundamentals AQS Application
• Index
• Search
Each of these options is accessible through the left hand side of the application (see
Figure 4-2 above). For the Index feature of help, when you click on a displayed topic,
the information for that topic will appear on the right. For the "Search" feature, type in a
word or phrase that you are trying to get help with ("Probe Height" for example). Hit the
"Enter" key to execute the search. All topics with the word or phrase in the title or the
description will be displayed on the left-hand side of the screen. Again, click on the
subject of interest to see the detailed description on the nght side.
4.4.2 Other Help Menu Items
There are three other menu items available to you: "Keys", "Display Error", and
"About". The "Keys" item (also invoked by Ctrl + Fl) will show you a listing of all the
short-cut keystrokes available in the system. The last two items can be very helpful if
you are having problems. This information may not help you, but it will help our support
staff root out your problem. The first is the "Display Error" (also invoked by Shift + Fl).
-^n .^nn K. 1—: 7, Selecting this function will display the
FRM-42100 No errors encountered recently . e . i
last error encountered on the message
Record-1/1 LJ I L bar at the bottom of the form. Always
keep an eye on this portion of the form. It
doesn't really jump out at you, but the system is
trying to provide you with useful information.
The other function that is available through the
AQS Version 3-1
Status Production
Current Form'
help menu is "About". "About" provides a
, „ description of which form you are currently
Current Form Version- > ** _ i .r «.L i«o • »i r
using. For example, from the Sessions form,
clicking on "About" provides the information
you see on the left. Noting this information can
be very helpful to the programmers to find any errors that may be happening within the
system.
4.4.3 Field Level Help
There is another category of help that is available through the system. It is the field level
help. This is information that is provided as you are on a given field within the system.
The information is displayed in the information bar at the bottom of the screen. For
example, if you put your cursor on the "State Code" field on the Maintenance -> Site
form, you will see this information appear in the information bar:
Enter a State FIP8 code that identifies one of the 50 states or other countries
This information can also be acquired by pressing the ? \ icon in the upper right of the
icon bar. Although the information it displays is nearly identical to what is presented in
the information bar, it is displayed in a separate window using the Help function
interface. Some people find this interface a better since they could then go and find help
on other items in a separate application without interfering what they are doing on AQS.
As usual, many ways of doing the same thing.
Version 1.0 4-11
-------
AQS Fundamentals
AQS Application
4.4.4 User Manuals and Guides
The final area of help is the user manuals and guides that we make available for the
system on OAQPS Technology Transfer Network (or the TTN). The manuals are all
available in Adobe's Portable Data File (PFF) format. The manuals provide the most
detailed documentation for how fields are derived and used with the AQS application.
You can find these manuals at http://www.epa.gov/ttn/airs/airsaqs/manuals/manuals.htm.
4.5 Sessions & Screening Groups
The first confusing thing that we must address is this concept of a "Session" and a
"Screening Group". These items are used extensively throughout the system and will
determine what features will be available to you.
After you enter your user id and password to enter the AQS application (see section 2.3
"Logging in AQS" if you have question as how to do this), you will be given a screen that
looks like this:
Action Help Session fldmn Betneval Maintenance QnticalRev CEiMcatlon Batch CQrrect
B £ B.
: p««a Only met;
' ScratflfigOraupAcaa
vouhiraaccetMdAQSFROD RtnvdalKor 1993 format Is now wattle tor teen* Rmr date «mMU for i*fUe (or 2000 to pram*
FVOUr«B)ASSISTANCI.PLEASE CALL THE H>AC*U.CQKTER AT 1-886-411 -4EPA(1-B86-«l1-4372) Hours 600anv830pnEaitam
Figure 4-1
4.5.1 About Sessions
Whether you know it or not, as soon as you enter AQS you start a session. A session is a
profile that you establish for yourself when you enter the system. The default profile that
is granted when you enter is the "Read Only User" profile. What this means is that you
can see all data that has been deemed ready to be given to the public by the owner of the
data. Notice in the blue bar above the radio button that it has the name of the form you
are working with (in this case, the "Select Session Access" form) as well as what type of
role you are assigned ("Read Only" in this case). The "Read Only" role should be
selected if you only need to look at data. If you need to insert, update, or delete data that
you own, you need to select a screening group. Umm.... A what?!?! Read on!
Version 1.0
4-12
-------
AQS Fundamentals
AQS Application
4.5.2 About Screening Groups
A screening group is an organization that ownsjnonitors within AQS. This organization
could be a state agency, a county agency, a tribe, an EPA office, a private organization,
or a government contractor. You may have been given access to many different
screening groups, or you may have access to no screening groups. It all depends on how
your user profile was established when you filled out the registration form (See section
2.1 "Registering for a User ID" for more information). You can see what screening
groups you have access to by clicking on the "Screening Groups Access" radio button on
the "Select Session Access" form. When you do, your screen should look something like
this (your list of screening groups will very likely not be the same):
Acnon Help Session Admin general Maintenance QmlcalRev certification
*
Batch CQnecl
a 2s
One«dOrt»User
'^Screening Oiaf Access
O» V - r»
Select the screening
group that you
would like to work
with. Most of the
time, you will only
have one screening
group to choose
from. Click on the
"OK" button when
complete.
Notice the blue bar
above the radio
button has changed
to show that you
have selected a
screening group:
4.5.3 "Read Only" Versus Screening Group Access
So is there a big difference in entering the system as a read-only user verses one with a
screening group? YES! First of all, you are prevented from certain areas of the system is
you are a read-only user. Take a look at the following chart. It illustrates what menu
items are accessible depending whether you are signed on as "Read Only" or with a
screening group:
m AQS
Action Help Session Admin Retrieval Maintenance Critical Rev CErtification Batch COrrect
Menu Item
Action (Any item)
Help (Any item)
Selected User Access
Read Only
^
,/
Screening Group
•/
v^
Admin
-/
-------
AQS Fundamentals
AQS Application
Menu Item
Session
Admin - Security
Admin - All other options
Retrieval (Any item)
Maintenance (Any item)
Critical Rev (Any item)
Certification (Any item)
Batch (Any item)
Correct (Any item)
Selected User Access
Read Only
/
S
X
^
-/
X
X
X
X
Screening Group
S
^
X
S
/
X
X
/
S
Admin
^
S
S
S
V
S
S
V
S
Hey! Wait a minute! What is this "Admin" column that suddenly showed up?!? Well,
this is a special role that is available to a few users (the author of this guide being one of
them). These are special functions and tables that are needed to be maintained to keep
the system running. I have included this role to show who has access to these other menu
items. If you try to select a menu item that you do not have appropriate access based on
your session profile, you will get this message:
Error You are not authorized to access this module
QK
So there are a couple of menu items that can't be accessed as a read-only user. What
else? If you sign on as a read-only user, you can only view data that has been approved
by the data owner as ready to view. There are some reports that look at data that is "pre-
production" (not ready to be viewed by everyone). These reports are not accessible to
read-only users. This will be explained more thoroughly in section 4.3 Retrievals -
Getting Data out of AQS.
In a similar fashion, the data you can access using the "Maintenance" function varies
greatly depending what kind of access you select during your session.
• Read-only: You can access any data that is classified as "Production" data
• Screening Group Access: You can select any data (production or pre-production)
from any monitor that the screening group owns. However, you cannot access
any data that is not owned by the screening group.
This will be explained in greater detail in section 4.4 Maintenance - Reviewing, Editing,
and Creating Data.
4.5.4 Changing Your Session Profile
If you realize in the middle of working with AQS that you need to change from a read-
only user to start working with one of your screening groups (or vice versa), it is no big
Version 1.0
4-14
-------
Reference
User Statistics
Appl Audits
Export Ca Dump Bucket
M?nt?ir '".ritcrm Ss?^
AQS Application
deal. You can change your session profile at
any time. All you have to do is click on the
"Session" menu item:
Clicking on this
menu item will send
&AQS
Action Help! Session I Admin Retrieval Maintenance Critical Rev Certification Batch correct
you back to the screen shown in Figure 4-1 and you can then select your new profile.
4.6 Administration Tools
For most users, there is only one active menu choice under the "Admin" main menu:
"Security". This will allow you to change descriptive information about yourself as well
as give you access to changing your AQS and CDX passwords. When you select
"Admin" -» "Security", you will see the following screen:
User Profile
Fire* Name
MM ;K
Last MILLER
Phone [919-541 7738 ' Street Address 1 (EPA BuiWmg
Zip Code lOT" _J
County Code JUPJ
UeerlD JJNZ
Cty |f.*r*!n-l
Fax J9195417738
User Type
SMiwM F~
E Mail [miller jonathan@eparrai c
Street Address 2 Room 335F
Stale Code 21
Agenqr Code |1108 __J
AQS Contact N"~ ^
EPAReonnCode 04 |
Tribal User ~
Change Your Oracle Passwords
AQS New Password- i
CDX
Confirm password I
OK
Current COX Pessword.
Synchronize CDX
Your user information is automatically selected. You will not have the ability to look at
information for anyone else. Notice there are two sections in this form. The top half of
the form provides information that you are able to change. Where lists of values are
available, a drop-down list is provided. The bottom half of the form allows you to
change your AQS and / or CDX passwords. Detailed instructions of how to change your
passwords are found in sections 2.4 Changing Your AQS Password and 2.5
Synchronizing Your CDX Password.
4.7 Retrievals - Getting Data out of AQS
When you get right down to it, AQS can essentially do two things: Get data into the
system, and get data out of the system. This section deals with how to get large
quantities of data out of the system in a formatted report. There are many different types
Version 1.0
4-15
-------
AQS Fundamentals AQS Application
of reports and formats, and options available when you are creating reports in AQS.
Collectively, these filter options, report options, and output options are called a criteria
set. Criteria sets can be saved for later use or saved in a "public" area to be used by other
users. So let's get started, shall we?!?
4.7.1 Overview
The standard report criteria interface screen (also referred to by its module name as the
"R3J form"} is the mechanism by which all standard reports are created. The R31 form
collects the following information from the user in order to create a report:
• What report to run
• The type of output desired
o Formatted report
o Text workfile
o XML output
• Delivery Method
o On-line (interactively)
o Batch (background)
• What data to be included in the report
o By geography
o By time periods
o By reporting organization
o By screening groups
o By land use
o By monitor types
o By parameter, parameter category, sampling duration, and/or sampling
methodology
• Specify report sort order
• Select report options
Collectively, this information is called a "Criteria set". Criteria sets can be named and
saved to be used at a later time. This topic is covered in detail under Section 2.2.7:
Saving Criteria Sets and Section 3: Using Saved Criteria Sets.
It is important to realize that not all parts of R31 are available for all reports. For
example, if you were creating a report for annual summary information, it would not
make sense to base your time selections on a Year / Month / Day basis since the report
only displays data on a yearly aggregation.
4.7.2 What is Returned
The type of information that is sent back to the user is very much dependant on what type
of report has been requested. All reports have a cover page which shows the following
information:
• What report was run
• What information was supplied to R31 to generate the output
Version 1.0 4-16
-------
AQS Fundamentals
AQS Application
This report is always generated in Portable Document Format (PDF). If a "REPORT"
output option is specified and the report is also generated in PDF format, the cover sheet
and the report may be "merged" together into a single document. At this time, you must
specify the "REPORT MERGE" option to "YES" in order for this feature to work. For
more information, see section 2.2.6.1, "Merge PDF File Option" for more information.
If the output of the report is in a different file format from the cover page or if the
"REPORT MERGE" option is set to "NO", then a ZIP file is created containing the PDF
cover page and the various requested report formats.
4.7.3 Tribal Mode
Within the application, sites may be identified in one of two ways:
Method L Unique combination of State Code - County Code - Site ID. The site may
or may not be identifiable by "Method 2".
Method 2. Unique combination of Tribal Code - Site ID. If a site can be identified
by this method, it can also be identified by "Method 1". This method is called the
"Tribal format"
If users want to select and sort data by Tribal Code and display the information in the
Tribal format, they must be in "Tribal mode" for the current session. You can determine
if you are in Tribal mode by looking at the blue title bar above the R31 tabs. If the words
"Tribal Mode" appear like this, for example,
M-j«KnnKiiMajimiMiiMiE»njijM»i«ia.i5nTOnM»iii»CT«am«ui
then you are in Tribal mode. If you are not in tribal mode, the title bar will look
something like this:
HBQ
HBO
There are 2 ways to set the session to be in Tribal mode:
/. From the "Action" Menu item - This will alter the current session to rum the
Tribal mode on or off
Version 1.0
4-17
-------
AQS Fundamentals
AQS Application
Print Screen
Mam Menu
Exit
Edit
Query
Block
Record
Uelp
Window
Shift +F8
Ctrl-H)
Tnbal Mode Off
r Ares Selection
[dew)
Gem
2. Update User Profiles - To change the default way your session is set with respect
to Tribal mode you may update your user profile record as appropriate. Go to
Admin / Security menu items and check or uncheck the "Tribal User" check box
on the "User Profile" tab as appropriate. If the user is defined as a tribal user on
this screen, they will be put in Tribal mode whenever they sign on to the system.
Please note that this mode also applies to other areas of the application, such as
the Maintain form modules.
user Profile
|
27711
First Name
Phone
Zip Code
County Code J063^
User 10
UnixAcet
JONATHAN
total JK'" "i
919-541 7738 [~~ Street Address 1 JEPA Balding
Last
City JResea'di
JNZ !
BphCfifflQO
J9195417738
User Type
Status btd
EMail
miller jonathan@epemad eptj
Street Address 2 Room335F
ij Stale Code
Agency Code
AOS Contact
EPA Regam Code
Tribal User
MILLER
3L.
1108
4.7.4 Creating a Report
The Standard Reports Selection Form is the primary user interface in generating the pre-
defined (or standard) reports within the AQS application. Different interface options
appear for each report, depending on if the report supports the particular feature.
4.7.4.1 Accessing R31
The R31 form is accessed by selecting the "Standard Report Selection" option from the
"Retrieval" menu (see Figure 4-3 below).
Version 1.0
4-18
-------
AQS Fundamentals
AQS Application
Action tjelp Saimn Admin ^^^Q Maintenance Cntica
B & fl -A cS .d 1. Bimne Annual Summery
| BrQnt Sample Methodology
, BnfH» Stau Tnreehold
, I BrowSe Parametar
i r{ Bro«E Audit.
JCDX *
i -----' -JMainMenu
R»* CEinfieman Batch CQntci
Figure 4-3 - Accessing Standard Reports
4.7.4.2 Criteria Set Tab
When first entering the R31 form, the "Criteria Set" Tab is active and the form is blank.
This form will remain blank until either:
a) The user selects a previously saved "Criteria Set" name from the drop-down list
(shown in section "2" in Figure 2 below). These previously saved criteria sets
may be either "Private" criteria sets created by the user or "Public" criteria sets
created by another user. Use of this option is covered in Section 3 ("Using Saved
Criteria Sets").
b) The reports are listed in alphabetical order based on the name of the report. Each
report has an associated report code. The user selects a "Report Code" from the
drop-down list (shown in section "3" in Figure 2 below). This indicates that this
is a new report being requested. Once the report code is selected, the R31 form is
now able to determine the following information:
a. What report outputs are available
b. What fields may be selected upon
c. If any sorting of the selected records is allowed
d. What report options are available
Version 1.0
4-19
-------
AQS Fundamentals
AQS Application
% "vtafirt.-srH D»nnrt f^tona .^oterttno fP»-iiH f
Cntena Set | s • -,r " <; • .: • •• | -
CrtenaSet |
Owner (JONATHAN jMLLffi
RepoilCofe | £J Re
f~ RunOrthe
r SendvnBnal
r Send 10 COX
Report Selection Mode
(f Monitor SetecUottfeMQ
<~ AieaSeitcuen
BSmHHI^^^^^^^^^^^^^^^^^^^^^^^^^^HTial
.'•-„•.- <:.i .<• | ,-.;,-.- | r-;-' . . ,:. .. | Retrieve Reports ] |*~—
d "» !
Type PRIVATE ^_J
XftNttnc 1 _ . __ _ _ _ ,
Report Outputs
r
r k
x]
i^m
•>
1
2
3
4
5
Figure 4-4 - Criteria Set Tab
After the desired report is specified, the pertinent parts of the form are populated and the
appropriate tabs are activated. The following items are filled out on the "Criteria Set"
tab:
• The report name (Section "3" in Figure 4-4)
• The "Run Online" output option is selected by default (Section "3" in Figure 4-4).
There are three options for how the reports are returned to the user
o Run Online - This option indicates that the results of the report will be
returned directly to the screen.
o Send via Email - The results of the report will be sent to the user's email
address as defined in their user profile.
o Send to CDX - The results of the report are sent to your Central Data
Exchange (CDX) account. You are responsible for signing on to CDX
and downloading the resulting file yourself.
• Depending on what elements are selected, additional windows appear in Section
"3" of Figure 4-4. For example, if "REPORT" is a valid output type and it is
checked, then Figure 4-4 Section 3 will look like this:
Version 1.0
4-20
-------
AQS Fundamentals AQS Application
_ — . Report Outputs
-------
AQS Fundamentals AQS Application
Click on "Yes" to continue to use the other selection mode. Click "No" to stay in
your current mode and retain your current information.
• The other appropriate tabs will become active (Section "1" in Figure 4-4)
4.7.5 Monitor Selection and Area Selection Tabs
4.7.5.1 Basic Function
You specify your selection criteria either by "Monitor Selection" or by "Area Selection".
Which tab is active is dependant on which radio button is selected on the "Criteria Set"
tab (Section "4" in Figure 2). The basic functions of these tabs are the same; the only
difference is what fields are available in which to specify your selection criteria. The
general idea of these tabs is to limit or filter the records that are selected from the
database. This is done by specifying geographic, temporal, and / or pollutant information
to select only the sites or areas desired. Which fields are available is dependant on the
selected report. If a field is not appropriate for the report, it will not appear on the screen.
4.7.5.2 How the Selections Work
As you can see in Figure 3, the Monitor Selection tab is broken in to four areas:
• Site - Monitor Criteria - Allows selection by the components of the monitor id.
Specifically these are the Tribal Code, State Code, County Code, Site ID,
Parameter Code, and Parameter Occurrence Code (POC). In addition, you may
specify method code, sample duration code, and the effective date range.
• Global Report Criteria - Allows selection by a category of parameters
("CRITERIA" category selects all of the criteria pollutant parameters), a single
parameter, sample duration, and method code. NOTE: If you have specified any
of these types of elements in the "Site - Monitor Criteria" section, you will not be
allowed to enter them here.
• Global Data Range - Allows selection by the appropriate date range specified
for the given report. NOTE: If you have specified any of the date elements in the
"Site - Monitor Criteria" section, you will not be allowed to enter them here.
• Global Screening Group - Allows selection by owners of the monitors.
Version 1.0 4-22
-------
AQS Fundamentals
AQS Application
Criteria Set [Mcmitor jSeJection]
Sort Order Report Options | Retneve Reports j
Site-Monitor Criteria
Tribal State County Site Parameter Method
Code Code Code Jd Code poc Code
I
Duration
I ]
r~r
Start Date End Date
YYYY MM DD YYVV MM DO
J~I~—rr
Global Report Criteria
Parameter
Pollutant Type code Method Code Duration Code
-
1
1 r
'
!
1
!
Global Date Range
Start Date End Date
YYYY MM DD YYYY MM DD
jj II! j 1
ZJ III II
Global Screening Group
Screening Group Name
i
Generate Report
Figure 4-5 - Monitor Selection Tab
The form allows for multiple rows of selections in each category except for "Global
Screening Group". The following rules are enforced when the program builds the query:
• All data entered into a single row within a given category are linked together with
"AND" conditions.
• Rows within the same category are linked together with "OR" conditions
• Data between the categories are linked together with "AND" conditions.
In figure 4 below the query would read as follows:
Plain English Version:
"Select Sites in Autauga County (County Code "001") Alabama (State Code "01") OR in
Norfolk County (County Code "021") Massachusetts (State Code "25") AND measuring
Criteria pollutants AND operating from January 1, 1999 through December 31,2000."
Version 1.0
4-23
-------
AQS Fundamentals
AQS Application
Cntena Set
Monitor Selection
?2$c <=..-
Sort Order | Report Options | Retneve Reports |
Site-Monitor Criteria
Tribal State Courty Sie Parameter Method
Code Code Code
J1 "-"
Code
POC Code
Duration
Code
Start Date End Date
YYYV MM DO YYYY MM DO
r~
r
r i
PoBulanl Type
Global Report Criteria
Parameter
Code
Method Code
Duration Code
CRITERIA
"D
T
r
LJ
Global Date Range
Start Date End Date
YVYY MM DD YYYY MM DD
^J1999 [01 [01 {2000 [12 |31
i r
Global Screening Group
Screening Group Name
I &
Generate Report
Figure 4-6 - Query Example
Since no filtering criteria were specified on things like Tribal Code, Method Code,
Duration Code, or Screening Group, they do not appear in the language of the query.
That is to say that what records are retrieved will not be limited by these categories.
JV
Version 1.0
4-24
-------
AQS Fundamentals
AQS Application
4.7.5.3 Differences Between the Selection Tabs
Examine Figure 5 below. This is the "Area Selection" tab. Conceptually, it works the
same way as the Monitor Selection tab shown in Figure 3 above. The difference
between them is what elements you may select. The following chart shows all the
available fields and on which tabs they are located. Not all these elements are available
with all reports.
Criteria Set
*
Tribal
Code
^
':
1
{ ~
r
vjt,.ji _c:ec:ui Area Selection Sort Order | Report Options Retneve Reports |
State
Code
FH
I
Fr-
r~:i
County Site
Code Id
LI
j
,
i
Monitor Type
_±J ''1
1 "|
zf J
JL
i- -
|
FT
Geograj
City
Code
—
•hical Criteria
AQCR UAR MSA CM5A EPA Region
Code Code Code Code Code
•*•
1
|
|
I — n
Land Use Type
^J ""*
| i •
^ I *
Protocol Criteria
Parameter Method Duration
Pollutant Type code Code Code
r
(
^
r
i_j
7^-
f
Ul
— ~=
^
L 1 " 1 ^ 1 ^
i 1 j '
i. 1 '1
1 : 'I
L j ^ j :_ j
Reporting Organization
1
1
Zj 1 ^-J
Date Criteria
Start Date End Date
YYYY MM DO YYYY MM DO
^J II 1 II
3 III II
Screening Group
Screening Group Name
Generate Report
Figure 4-7 - Area Selection Tab
Field Name
Tribal Code
State Code
County Code
Site Id
Monitor Single
Parameter
POC
Monitor Method
Monitor Duration
Monitor Date
Monitor
Selection
-------
AQS Fundamentals
AQS Application
Range
City Code
Urban Area Code
CMSA Code
^
^
^
MSA Code
EPA Region
Screening Group
^
^
S
-------
AQS Fundamentals
AQS Application
4.7.5.4 Sort Order Tab
Every report has a defined sort order. Many of the reports allow you to change the sort
order in some manner. However, given the structure of some reports, what may be sorted
is limited. The interface to alter the sort order can be found on the "Sort Order" tab. If
this tab is not active, then sorting is not allowed for the given report.
Criteria Set
Monitor Selection
Sort Order Report Options Retrieve Reports j
Sort Order
Allowed
Order
Column Nome
Lo Hi
A
1
2
3
4
PARAMETER_CODE
TRIBAL_CODE
STATE_CODE
COUNTY.CODE
s ISTEJD
it ii
2 |3
2 |3
4
4
8
8
•f-1 Move highlighted column up
| Move highlighted column down
|4 {8
The tab displays the sortable fields, the order in which the sorting will take place, and the
range of order the field may appear. In this particular example, the report must be sorted
by Parameter Code first (note the Allowed Range is 1). Next you may sort by either:
• Tribal code then State code
• State code then Tribal code
Again, note the allowed order range for these columns. To change the order, highlight
the column that you would like to change its order, and then click the "Move highlighted
column up" arrow or the "Move highlighted column down" arrow. You may not add or
remove columns to the list to be sorted. You may only change the sort order and only
within the range where the "Order" number is between the "Lo" and "Hi" values of the
"Allowed Range".
4.7.5.5 Report Options Tab
With all the reports, there are several options that may be available that will effect what
data is returned to the user. The report options may be placed into one of three
categories:
• Single value report options (See Figure 4-8) - For a given option category, the
user may select one of a known list of options. All available options appear in the
drop-down listing for the option.
• Alternate standards report option (See Figure 4-8) - User may specify the values
at which a given value will be marked as exceeding a given limit. In order to use
this option, the data must be returned in Standard Units. These alternate limits
will only be applied where the data being retrieved matches a given parameter and
duration. The user must specify a parameter, duration, and any of the following:
o Primary Value
o Secondary Value
Version 1.0
4-27
-------
AQS Fundamentals
AQS Application
o Primary and Secondary Value
Multiple value report options (See Figure 4-8) - For a given option category, the
user may select none, one, or multiple choices from the known list of options.
Selections are made by checking the box to the right of the option. A checked
box indicates that the option will be applied to the report. The user may also
utilize the "Check All" and "Uncheck All" buttons to expedite selecting multiple
columns.
A
Criteria Set | Monitor Selection | Ar-:>i ifr >\.tn |
INCLUDE NULLS
DAILY STATISTICS
UMTS
RAW DATA EVENTS
MERGE PDF FLES
IYES
JMAXIMUM
(STANDARD
[INCLUDE EVENTS
|NO
1
1
1
••••^^•^^^^^^^•^^^••^^^^•^^^^^••^•^•^•^•i
Sort Order j JteporUDptigns 11 Retrieve Reports
111
IL]
id
mnnl
I
^ Single Value Report Options
iij
LJ
a
LJ
ALTERNATE STANDARDS
Porstnctcf
Duration
Primary Sid Secondary Std
A
1
r-
.
1
1
1
1
Alternate Standards Report Option
f~~ Generate "Report j
t
i
f
Figure 4-8 - Single Value & Alternate Standards Report Options
Version 1.0
4-28
-------
AQS Fundamentals
AQS Application
•fsSiStandaid Report Criteria Selection (Read Only)
Criteria Set ) Monitor Selection | >Me^
" ACTION INDICATOR flNSERT
AMP500 Tribal Mode
-FP:.:.-- | :iirtc: '*r \ Report Options ]| Retrieve Reports |
SITE MONITOR TRANSACTIONS
Id-
MERGE PDF FILES fhlO ^J
1
1
1
-J
_J
' ]
1 _J
1
3 i
Multiple Value
-J
_J
Report Options
HHDl
SITE BASIC 1*
FANGENT STREETS
17
OPEN PATHS ff
MONITOR BASIC R
MONITOR SAMPLE PERIODS
F
MONITOR TYPES 1*
MONITOR AGENCY ROLES
MONITOR OBJECTIVES
ff
1-
MONITOR SAMPLING SCHEDULES >*
MONITOR STREET INFORMATION W
'ROBE OBSTRUCTIONS V
MONITOR REGULATORY COMPLIANCES I*
MONITOR COLLOCATION PERIODS
MONITOR PROTOCOLS
: " ' crieck AM" " 1 : "uncheck AM "
1*
P
1
[ 'Generate Report
Figure 4-9 - Multiple Value Report Options
4.7.5.5.1 Merge PDF Files Option
A common report option is one labeled "MERGE PDF FILES". It lists either "YES" or
"NO" as the available option values. If this option equals "YES", the report program will
merge the results of the cover sheet with the output of the formatted PDF report. This
option will only be implemented if ALL of the following conditions are true:
1. The "MERGE PDF FILES" option = "YES"
2. The user has selected a "REPORT" type of output from the "Criteria Set" tab
3. The format of the "REPORT" output on the "Criteria Set" tab is set to "PDF"
The option screen does not validate that the combination of these elements before
executing.
4.7.5.5.2 Exceptional Event Processing
Several reports have an option of how to handle exceptional event processing. This
option will primarily affect summary types of reports. When data is submitted to AQS,
the submitting agency may flag the data as being effected by either natural events or
man-made exceptional events that have influenced the level of the sample. These flags
are eventually concurred or not concurred by the local EPA authority as to whether this is
an approvable event.
When AQS summarizes the data, it is summarized multiple times if the dataset contains
any of these natural or exceptional events. The following summaries are computed:
• Include all data whether or not they contain events
• Exclude all events
• Exclude just the exceptional events
Version 1.0
4-29
-------
AQS Fundamentals AQS Application
• Exclude just the natural events
• Exclude any events with EPA Regional concurrence
• Exclude just the exceptional events with EPA Regional concurrence
• Exclude just the natural events with EPA Regional concurrence
4.7.6 Saving Criteria Sets
Once you have successfully created a criteria set, you may save it to be executed again at
a later time. To save a criteria set, first go to the "Criteria Set" tab (See section 2 of
Figure 4-4). Enter the following information:
• Criteria Set Name: A unique name to identify the criteria set
• Criteria Set Description: A brief description of the criteria set
• Owner: This will be your name and filled out automatically
• Type: You can either define the criteria set as "Private" (default) or "Public"
o Private: This option means that only you may access and update this
criteria set
o Public: This option allows any other user to access and update the criteria
set
Once you have filled out the appropriate information, click on the "Save" icon in the
upper left-hand corner of the form.
If you have successfully completed these steps, you will receive the following dialog box:
j i -20054 Your Criteria Set has been saved
4.7.6.1 Using Saved Criteria Sets
After entering the Standard Reports form (See section 2.2.2 for information on how to
enter the R31 form), you may recall any previously saved criteria sets. You will be able
to see any criteria set that you have saved as well as any other criteria set saved by others
labeled "Public".
To see all the criteria sets you have in your private account, select "Private" from the
"Type" field and click on the "Criteria Set" drop-down list. To see the criteria sets
available in the "Public" area, choose "Public" from the "Type" field and click on the
"Criteria Set" drop-down list.
The report that the criteria set was originally run with is returned. Any selections made
on the Monitor / Area Selection tab, Sort Orders, and Report Options are also returned.
However, the elements under the outputs are always reset to the default values for the
report (see section "3" of Figure 4-4). Once the information has been loaded into the
form, the report may be immediately generated or you may make any changes.
Version 1.0 4-30
-------
AQS Fundamentals AQS Application
4.7.6.2 Deleting Saved Criteria Sets
You have the ability to delete previously saved criteria sets that you no longer need or
want in your list of saved criteria set. Since these saved criteria sets are saved within the
database, they are treated like any other record in the database. The method of deleting
them is:
1. Query the Criteria Set into the form.
2. Delete the record using either the form icon or the menu selections.
4.7.6.3 Query the Criteria Set into the Form
To see all the criteria sets you have in your private account, select "Private" from the
"Type" field and click on the "Criteria Set" drop-down list. To see the criteria sets
available in the "Public" area, choose "Public" from the "Type" field and click on the
"Criteria Set" drop-down list. You may delete only "Public" cnteria sets that you have
saved.
4.7.6.4 Delete the Criteria Set
Once the criteria set have been queried, you may delete the record by any of the
following methods:
1. Click on the "Remove Record" icon e:
2. Select "Action" -> "Record" -> "Remove" from the menus.
3. Press the "Shift" key and the "F6" key simultaneously.
Please note that once the record is deleted, the action is automatically committed and
cannot be un-done.
4.7.7 Generate Report IL -Seoecatg Rgpo/t—\
At the bottom of each tab of the R31 form is the "Generate Report" button. Once this
button is pressed, the report takes the information from the tabs of the R31 form and uses
it to generate the report. If the "Send via Email" or "Send to CDX" options were selected
from the "Criteria Set" tab, the report runs in the background. When complete, the report
will be delivered by the selected mechanism. If the "Run Online" option was selected
from the "Criteria Set" tab, then a progress dialog box appears to let the user know the
progress of the report. When complete, the progress dialog box will disappear and the
report output will be delivered to the user on the screen. The "Generate Report" may be
pressed at any point during the selection criteria process. Prior to submitting the report,
the R31 form will check to ensure that all required information has been provided. If it
has not, the user will be shown the part of the form in error and provided with a message
to indicate what additional information is needed.
Version 1.0 4-31
-------
AQS Fundamentals
AQS Application
4.7.7.1 Report Progress Dialog Box
•^Report Pi
Report Progress
100 Percent Complete
Report Stage (Report Pre-Selection
Refresh | ; Cancel Report | . Exit Report Progress |
The Report Progress dialog box provides the user with the following information:
• The current stage of the report. When a report is being generated, it will undergo
a series of different steps. While not all reports take the same steps in generating
a report, the final stage of the report generation process is the "Generating
Output" stage.
• The percent completeness of the particular stage. This provides an estimate of the
amount of progress made from the particular stage. It is not an overall percentage
to generate the entire report. This percent completeness applies only to the
current stage.
• The information on the dialog box automatically refreshes every 15 seconds. If
you would like the information to refresh before the next 15 second interval, press
the "Refresh" button and the information will be updated.
• The "Cancel Report" button will tell the report generator to cancel the currently
running report. The cancellation is not immediate. A message must be sent to the
report server, and the dialog will wait until it receives a response that the report
has been cancelled.
• The "Exit Report Progress" button will not cancel the report, but it will take it out
of the "Run Online" mode. In order to see the results of the report, the user will
have to manually check the progress of the report through the "Retrieve Reports"
tab (See section 4.2).
4.7.8 Retrieving Previously Executed Reports
You may also obtain the results of a previously run report by clicking on the "Retrieve
Reports" tab from the R31 form (See Figure 8 below).
Version 1.0
4-32
-------
AQS Fundamentals
AQS Application
MjainmBUMaaiiiHMHuai
CntenaSet J'iu .u ,.•!.• v..: ji | A
Read Onk'i Tnbal Mode
': O_*JL
Retrieve Reports
User Id
Report Code Request Type Request Date
Report Stage
Complete
_I_
JNZ
JNZ
JNZ
JNZ
JNZ
JNZ
JNZ
JNZ
AMP450 REPORT 105/27/2005 01 28 PI
AMP350 IREPORT (05/27/2005 12 43 PI
AMP350 IREPORT
AMP230
AMP450NC
REPORT
REPORT
AMP450NC (REPORT
AMPSOO IREPORT
AMP350 (BATCH
JNZ |AMP4io IREPORT
05/27/2005 12 42 PI
05/25/2005 02 09 PI
05/24/2005 03 12 PI
05/24/2005 03 08 PI
Completed 1 1 00
Cancelled (100
Completed pod
Completed (100
Completed |1 00
05/20/2005 08 54 At (Completed |1 00
05/1 3/2005 09 4 7 Al
05/1 3/2005 09 45 At
I
I
I
I I
I
I
Cancelled |100
Cancelled 1 100
i Retrieve Report j j Refresh Query j i CancelReport ] i Delete Report
Figure 4-10 - Retrieve Reports Tab
The "Retrieve Reports" tab shows the status of any report that you have run in the last 15
calendar days. It shows the following information:
• Who ran the report (will always be your user ID
• What report was run
• How they requested the report (Online or Batch)
• The date the report request was made
• The stage of the report
• Percent completeness of the stage
4.7.8.1 Retrieve Report Button
The "Retrieve Report" button on the "Retrieve Reports" tab (see Figure 4-10) will obtain
the results of a completed report for the row that is highlighted on the form. The button
will only work successfully if the Stage is "Completed" and the "% Complete" column =
100. Keep in mind that this function returns the results of the report at the time it was
originally run. It does not re-execute the query. So any updates to the data that have
been made from the time the report was originally run will not be reflected in the
retrieved report.
4.7.8.2 Refresh Query Button
The "Refresh Query" button updates the "Stage" and "% Complete" columns for the
reports that you have supplied. The results are refreshed each time you reopen the
Version 1.0
4-33
-------
AQS Fundamentals
AQS Application
"Retrieve Reports" tab, but if you want to see the status as it stands at this moment, press
this button
4.7.8.3 Cancel Report
The "Cancel Report" is used when you would like to cancel a report that you are running
in the background. It will not delete the report from the list of reports. It only changes
the status of the highlighted row to "Cancelled".
4.7.8.4 Delete Report
The "Delete Report" is used when you would like to remove a report from the list of
reports run in the last 15 days. It will not cancel the report. It only removes the
highlighted row from the list of reports for the user.
• Pfjeclslon
Accuracy
Blanks
Summary
Concurrence Ind
Main Menu
CnucalRev Certification Batch
gaw Data
Composite Data
Delete Preproducton Data
4.8 Maintenance - Reviewing, Editing, and
Creating Data
The Maintenance function allows you to browse and edit the
data on-line. The Maintain function uses the standard Oracle
query icons and principals to view the data. However, not all
of the functions are available all of the time. Maintain is one
of the menu functions that will actually change the menu
structure. When you click on "Maintenance", you will need
to choose what kind of data you would like to see (see the menu sample above). Once
you choose the type of data, the menu bar will change to the following:
Action Help §ite Monitor Audit Event SamPle Values PRecision AQcuracy Blanks Summary concurrence Ind Main Menu
So the items that now appear on the list are nearly the same as the displayed list from the
main menu. It is just a quick way to get to the other types of data that are available.
4.8.1 Working as "Read Only" vs. with a "Screening Group"
How "Maintain" will work for you depends greatly on how you established your session
when you signed on to AQS.
• Read Only User - This will allow you to view any data in AQS that is classified
as "Production Ready". You will not be allowed to change any of the data that
you see (even if it is your own).
• Screening Group Access - This mode will allow you to query, view, and edit
only the data that you own for the screening group that you selected. However,
you will get to see any of the data that you own ("Production" and "Pre-
Production data).
So the question becomes, "what do you want to see?", or "what do you want to do with
the data?"
Version 1.0
4-34
-------
AQS Fundamentals
AQS Application
4.8.2 Types of Data Available
•MESsmHEaafl
4.8.2.1 Site Information
Site information contains all of the
data that pertains to where a
monitoring site is located. You will
notice that there are multiple tabs
that may be selected. Each of the
tabs will show you additional
information for the site that is
displayed on the "Basic Site Data"
tab. You will notice in the lower
right comer the "Create Monitor" button. It is grayed out in this example because we are
in "Read Only" mode and cannot alter information in the database. However, if we did
just create a site on-line, you could press this button to begin the process of creating a
monitor for the site.
4.8.2.2 Monitor Information
The Monitor form provides
information about the configuration
of a monitor at a site. As with the
site form, the monitor form has
multiple tabs associated with it; each
containing additional information
about the monitor that is on the
"Monitor Basic" tab. In fact, there
are so many additional tabs,
sometimes the labels get cut off. To
see the name of the label on a tab,
just place your mouse pointer over the tab and the name will appear (see the "Sample
P..." tab with the "Sample Periods" text as an example).
4.8.2.3 Raw Data
As mentioned far too many times
within this guide, there is more raw
data in the system than any other type
of data. In fact, 99.98% of all the
data in AQS is raw data. These are
the hour-by-hour measurements
obtained from the monitoring
stations. As you can see, every data
point has information about the
instrumentation used to collect the
data, any qualifiers associated with a data point and any comments submitted by the user.
Due to the number of records in the table, it is a very good idea to be as specific as
possible when constructing your query.
Version 1.0
4-35
-------
AQS Fundamentals
AQS Application
4.8.2.4 Precision Data
4.8.2.5 Accuracy Data
Accuracy Data
Tribal Code n«to c*£fI|y *£,*„.
f ........ U| gT£j j°39"ll £«>3
AucAudttCtoM
Vw otr Acw
~
otr AcwtcyType
$~ p H
ExpretonCWe AudlScteoUed Zero Spwi Vdua
i r ~~ [™""~
Uonltot Protocol
Precision data reflects the results of the
required precision audits from the air
monitoring program. These are the
individual audit results. There are other
areas and reports within AQS which take
these individual audit results and
summarize them on a per site and per
PQAO (Primary Quality Assurance
Organization) basis.
Accuracy data reflects the results of
the required precision audits from
the air monitoring program. These
are the individual audit results.
There are other areas and reports
within AQS which take these
individual audit results and
summarize them on a per site and per
PQAO (Primary Quality Assurance
Organization) basis.
4.8.2.6 Blanks
Blanks data are used to detect any
contamination which a monitoring
filter may have encountered prior to
being inserted into the instrument.
There are no reports or summaries of
the blanks data, so if you want to see
it, this may be your only method of
obtaining this data. You may notice
that the format of this data is very
similar to the raw data form.
4.8.2.7 Summary
Although you can, technically, report the annual summary data to AQS, most users
submit the raw data and let AQS perform the calculations to create the annual summary
data. All pollutants have similar statistics including the number of observations, average,
minimum value, and top 10 maximum values. If there the regulations specify a special
statistic to be calculated for a particular pollutant, it will also appear in this table.
Version 1.0
4-36
-------
AQS Fundamentals
AQS Application
4.8.2.8 Audit
r JH ^ j _
'j~ ( r"" ll~ULZIIIlZ [_'
- i ' j LZZZZZHZH d
. _ ;-
- - J '—
The "Audit" table contains data about
technical system audits and any of
the findings that an auditor may have
found. You need special permission
to enter this kind of information, but
anyone should be able to get
information on an audit that has been
performed.
4.8.2.9 Event
The Event module is not available to most users. So if you do try to access it, you will
very likely get the following message:
Error You are not authorized to access this module
4.8.3 Executing a Query
The premise of all the forms in the database is that you want to get all of the information
possible. However, keep in mind that AQS is HUGE! So you actually want to be as
specific as possible when querying the data. All of the forms work essentially the same
way. The only difference is that the number of fields that you can filter by changes. The
data is returned to you in descending date order (the most recent data is shown at the
beginning of the list). So we will take an example of one of the more comprehensive
forms and you can apply the same principals to the other forms.
STEP 1: Let the system know you want to enter a query
When you first enter a form, you are automatically in "Enter Query" mode. You
can verify this by looking at the bottom information bar on the screen:
Enter a query; press (Enter-Query |lo cancel
Record 1/1 I Enter-Qu UstofValu
You may also invoke the "Enter Query" mode by pressing the "Enter Query" icon
(% or pressing "F7".
STEP 2: Enter the filter criteria
In general, you can specify any of the fields that appear in the top block of the
form, but not in any of the fields on the lower blocks of a form. If a form has
multiple rows and no top block (like the "Audit" form), then you can specify the
filters in the first row of the form.
Version 1.0
4-37
-------
AQS Fundamentals
AQS Application
$»«»»V*!l».-«»«to,V»J
-------
AQS Fundamentals AQS Application
4.9.1.1 Pipe-Delimited Text Format
Using the pipe-delimited format is by far the most common method of setting up the data
for batch processing. Processing the data in this manner makes the following
assumptions:
1. Each type of data will have its own format. To date, there are 20 different types
of data in AQS (Not coincidently, there are 24 different types of forms on the
Maintenance screens... one for each type of data - Not quite a 1-to-l mapping, but
close!).
2. The type of data that you are working with is specified m the first field
3. You tell AQS that you are done with the field by placing a pipe ("|") symbol
between the fields
The formats for each of the record types can be found at the following web address:
http://www.epa.gov/ttn/airs/airsaqs/manuals/AOS Input Transaction Formats.pdf. In
fact, if you don't have a copy of this sitting at your desk, you should probably get one
before we do the example. Are you ready for an example? Me too... Let's say that
you know the following information about a raw data point that you want to enter into
AQS:
• 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 the sample (the duration): 1 hour
• What kind of instrument was used? Dasibi 1008-AH
• When was the sample collected? June 10, 2006
• What time did the sample begin (local standard time): 1:00 PM
• Sample Concentration: 0.050 parts per million
• Any other qualifiers that you want to apply to the 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
OK... The resulting transaction would look like this:
RD|I|01100119999144201111110071056120060610113:00|0.0501 I I I I I I I I I I I I I I
Looking at it field-by-field, here's what we have:
Version 1.0 4-39
-------
AQS Fundamentals
AQS Application
Data Type "RD" means
•Raw Data'
State Code. County Code, &
Site ID Makes up the AQS
Site ID
^v
V
POCCode
Unit Code •00
Parts per Mil
7' =
on
~Date YYYYMMDD Format
| Sample
1 Value
Collection Frequency
(Used primary for
PMi0&PM2i)
~ Up to 10
Qualifiers Can
be Specified
RDIII 011001199991 4420111)11 007 1056 I 20060610113:00 I 0.050 I I I | I I I I I I I I I
[Met
Method Code 'OSS'
I = Dasibi 1008-AH
Sample Duration
•T = 1 Hour
Pollutant Code
•44201 • = Ozone
Action T means INSERT
Alt MDL Value
-^""uncertainly Value
MP ID (alternate way to
specify method - unit -
duration - Coll Freq
Information)
Null Data Code (Why
data wasn't Collected)
Time HH MM
Format 'HH' is a 24-
hour clock
So where did I get all of these codes? Some, you can find within the application (like the
pollutant, method, unit, and duration codes). Others can be found on the AQS page of the
TTN
4.9.1.2 XML Format
XML stands for "Extensible Markup Language". It provides a structure defined by an
XML Schema in order to allow other programs to do something with the data. All values
for fields are enclosed within tags that look like HTML tags. In this particular case, the
XML Schema tells AQS how to load data into the database. If you look at Figure 4-11,
you can see the hierarchical structure of the AQS XML schema.
Version 1.0
4-40
-------
AQS Fundamentals
AQS Application
'"MMfeaittlMt.
"•<£,-<•
Figure 4-11
So if we take the same example as we used for what the data would look like a in "Pipe-
Delimited" format and apply the AQS XML schema to it, it would look quite different,
but convey the same information. Again, here is the information:
• 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 the sample (the duration): 1 hour
• What kind of instrument was used? Dasibi 1008-AH
• When was the sample collected? June 10, 2006
• What time did the sample begin (local standard time): 1:00 PM
• Sample Concentration: 0.050 parts per million
• Any other qualifiers that you want to apply to the data? No
Version 1.0
4-41
-------
AQS Fundamentals AQS Application
• 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
And here is what this information would look like in the AQS XML format:
OK/StateCode>
OOK/CountyCode>
9999
< MomtorIdentifierDetails>
< ParameterCode>44201
< ParameterOccurrenceCode> 1
!
059
007
20060610
13:00
< RawValueInsertDetails>
0.050
You might notice a couple of things. For starters, this format takes a lot more characters
to convey the information for a single record. However, over time, you really start saving
space because you don't have to repeat things (like the "SiteldentifierDetails) until the
values change (you move on to the next site for example. The other thing that you might
notice is that the file is self-documenting. I don't need to tell you what each field means
because the enclosing tags tell you what that value represents.
The overall moral to the story is that it doesn't really matter which format you use. They
both do the job they are should do; they tell AQS about their data and provide
instructions as to what to do.
Version 1.0 4-42
-------
AQS Fundamentals
AQS Application
Staging
Tables
Edit/Load
Summary
Edit Error
Detail
Correct
Production
Tables -
Preproduction
Status
4.9.2 About the Batch
Process
Now we know that AQS needs to
know about the data that you have and
what do with that data. You do that by
formatting the data in a way that AQS
can recognize. So you have a file
sitting on your PC at your office with
lots and lots of data collected from
your monitoring sites. What do you
do now? That is where the diagram to
the left comes into play. This provides
a picture of how to process your data
through the system. Each of the
square boxes represents a process that
you can initiate through the "Batch"
menu item within the AQS
application. Each of the light blue
objects (|"~~J) represent a report that
you can request through the batch
screen as well. The red rounded-
square objects represents other
processes in the application which
we've already covered.
There are a few things worth noting in
this diagram:
• Although there are three
processes listed (Load, Stat /
CR, and Post), only raw data
types of data need the Stat /
CR and Post steps. All other
data types go straight into
"Production" status.
• The Raw data can have up to 3
different statuses, depending
where in the process it is:
o R = Loaded
successfully
o S = Passed statistical
evaluation
o P = Production status.
This means that the
data is accessible to be
read by anyone in the system.
If data is identified as having errors, the records are sent to the "Staging Tables"
R = Relational
Status
"s"=Stat
Analysi
Scan!
Version 1.0
4-43
-------
AQS Fundamentals
AQS Application
In order to load data into the database through the batch process, you have to have access
to a screening group and you have to have "Update" authority within that screening
group. If both of these are not met, then you will get the following error message when
you click on the "Batch" menu item:
Error: You are not authorized to access this module.
;QK
No need to panic! Just go back to
"Sessions" and select a screening group for
which you have update authority and try
again.
Once you have successfully selected a screening group and select the "Batch" button, you
will be brought to the Batch form.
Figure 4-12
This form is broken down into 3 basic areas (working from top to bottom):
1) List of the files that you have transferred to CDX
2) List of processes that have been performed within this screening group
recently (within the last 2 weeks)
3) The buttons that activate either a process or starts a report
Since the other two sections are populated by using the buttons in the "Available
Actions" section of the form, it is probably a good idea to review what the different
functions are:
• CDX: Clicking this button will send you to the CDX Home Page
(remember it is a different application). Once you transfer the file
from your PC via CDX (see Section 3 if you have questions on
how to do this), you will see the file appear in the "List of Files
Available from CDX" portion of the form.
• Load File: The Load File process will attempt to enter the records in the
file to the AQS Database. The data is verified that it meets all the
business rules necessary. If at least one error in the file is found,
Version 1.0
4-44
-------
AQS Fundamentals
AQS Application
the row with a "Job Type" of "FILE" in the "List of Processes
Recently Performed" will have a session status of "ERROR".
Additionally, the records with errors are copied to the appropriate
"Staging Table", where you will be able to make modification to
the record through the "Correct" process. If no errors are found,
the row in the "List of Processes Recently Performed" will have a
session status of "COMPLETED". Also, the data is entered into
the appropriate AQS Database table. If this is Raw Data ("RC" or
"RD" transactions), then the data is entered with a status of "R"
meaning that it has passed all of the database rules but still needs
to be verified through other processes. All other data types are
entered with a "P" status ("P" status means "Production Ready").
Submit Correct Data: This process load data from the "Staging Tables"
rather than from a file you transfer from CDX. All the other
information mentioned in the "Load" function is identical for this
function except that the data comes from a different source.
Stat / CR: This process will perform a statistical (the "Stat" part of the
name) and identify any data anomalies that fail a "Critical Review"
(the "CR" part of the name) analysis on all the data that is trying to
be processed in the system. In this step, the status of the data is
changed from "R" to "S" meaning that the data has been subjected
to statistical evaluation. This process only applies to Raw Data.
Post: This process will change the raw data in the database for the
screening group from a status of "S" to "P". When you press the
"Post" button, you will be taken to a screen that looks like this:
'5^W-^;\fttofc\C";i;^
rV-s\*" <^' .'iWvt°^'"^!L f'-^' "rv^r; ^ v^°V-' •>"' sX'-° St^tiStifeal'S^t* X o*v^:&'< /X>,; vsvv^-«i^i^-i^&^
x a .>»>i».jft.«wiYi .•?...»•. AH a y..j..ai.jj.i.igu....i.iA.A.-iM ,.>...•.-".-*. M->.-v-"^v*t' v. f» -JJ.^I.MJ»^.I jai. j. AA—.—t x-f"**-1^1—m-Mtofr.--.mM* "*• J^»a.»ii4.M»\ ^>M.I^» .>J&ja»i».M»^-. .iAftja.i*ma
The intent is to provide the user with one last change to approve or
to not approve making this data available to the entire user
community. So if you press the "Post Data to Production" button
. Version 1.0
4-45
-------
AQS Fundamentals AQS Application
at this point, the job is submitted. Tbis process only applies to
Raw Data.
• Edit Load Summary: This process will produce a report that describes
how many records were successfully processed by a LOAD job. You can
only perform this function for.'TILE" job types.
• Edit Error Details: This process will produce a report that will list all
errors found by the loading of the data. You can only perform this
function for "FILE" job types.
• Scan Report: This process will produce statistics about data that you are
trying to make production. You can only perform this function for
"STATCR" job types. This process only applies to Raw Data.
• Stat Evaluation: This process will produce a report about any statistical
errors found during the "Stats CR" Job. You can only perform this
function for "STATCR" job types. This process only applies to Raw Data.
• Raw Data Inventory: This process will produce a report with the number
of data points that were processed during a POST process. You can only
perform this function for "POST" job types. This pioces^. only applies lo
Ra\v Data.
4.9.3 Batch - When Things Go Right
In a perfect world, none of the records in your files will have errors when you process it.
If this is true, then things go very smoothly with the batch process. If you follow along
with the flow chart at the beginning of this section, you can see what I mean. We will
take two scenarios: First we'll look at the easiest case where the file contains no raw data
(it has all precision and accuracy audits for example. Then we will look at what happens
in a "best case" scenario for when a file does have raw data.
4.9.3.1 Clean File Without Raw Data
• Click on the CDX button
• Transfer your file
• Close CDX - This should leave you with the AQS Application open
• Click on the "Refresh" button
• Click on the "Load File" button. You will be told the job was submitted and a
session record will appear with a "Job Type" of "FILE" and a "Session Status" of
"ACTIVE"
• Wait for "Session Status" of the session record to change to "COMPLETED"
4.9.3.2 Clean File With Raw Data
• Click on the CDX button
• Transfer your file
• Close CDX - This should leave you with the AQS Application open
• Click on the "Refresh" button
• Click on the "Load File" button. You will be told the job was submitted and a
session record will appear with a "Job Type" of "FILE" and a "Session Status" of
"ACTIVE"
Version 1.0 4-46
-------
AQS Fundamentals
AQS Application
Wait for "Session Status" of the session record to change to "COMPLETED"
Click on the "Stat / CR" button. A session record will be created with a "Job
Type" of "STATCR" and a "Session Status" of "ACTIVE"
Although it isn't required, it is a good idea to review the reports of the
information generated by the "Stat / CR" analysis. So click on the "Scan" and / or
"Stat Evaluation" buttons to generate the reports.
Wait for "Session Status" of the session record to change to "COMPLETED"
Click on the "POST" button. You will be taken to a screen with all the monitor-
month's data displayed.
Click on the ""Post Data to Production" button. A message will appear saying the
job was submitted
Click on the "Return to Batch Processing" button. A session record will be
created with a "Job Type" of "POST" and a "Session Status" of "ACTIVE"
Wait for "Session Status" of the session record to change to "COMPLETED"
.Edit/Load
Summary
Edit Error
DetaiJ
4.9.4 Batch - When Things Go Wrong
Well, the world isn't perfect. You will very likely
have some errors in your files. It is estimated that
33% of all the files submitted to AQS have some
error associated with it. So it would be good to
know what to do when this happens, eh?!? Again,
refer to the flow diagram at the beginning of this
session. Many of the steps that are performed are
identical when things go wrong. The biggest
difference is that if there are errors, the records are
placed in staging tables until the errors are fixed.
There are 20 different types of staging tables; one
for each type of transaction available in the system.
You use the CORRECT function to correct the
errors in the staging tables. Once you have
changed the records in CORRECT, you then try to
load them again by pressing the "Submit Correct Data" button. This tells the system to
load the data from the staging tables rather than from a file. Just like when loading from
a file, the system will test the records against all of the rules in the AQS database. If they
pass all the checks, they are placed in their proper tables. If they don't pass, they are sent
back to the staging table to be corrected again. You continue this process until all the
errors are addressed. Once your records pass the edits, you proceed on with the process
as outlined when things go right. Now the $1,000,000 question is "How do I use
CORRECT?" Good question! Read on!
Site Data
Monitor Data
Raw
Erecision
Accuracy
Blanks
Composite
ANnual Summary
Delete by Screening Group
Ma|n Menu
Version 1.0
4.10 Working with CORRECT
The Correct function is a bit of a misnomer. It doesn't actually correct
the data... you correct the data. The Correct function merely provides
a mechanism for you to edit the data in the staging tables. As
mentioned earlier, there are 20 different forms that make up the correct
function; one form for each type of data available to be submitted into
4-47
-------
AQS Fundamentals
AQS Application
AQS. Each form works in nearly identical ways. So we will look at one or two examples
and leave you safe in the knowledge that other forms work the same way. But before we
do that, notice that the menu bar has changed once you click on "Correct" from the main
menu:
Action Help Site Data Monitor Data Raw Precision Accuracy Blanks Composite ANnual Summary Batch Main Menu
The menu had changed to give you quick access to each of the different data types
available through Correct. To get back to the main menu, click on the "Main Menu" item
(on the far right).
NOTE: Since we are working with data that you are trying to update, you will need to
select a screening group before you will be allowed to access the Correct module
Here is the Raw Data Correct form:
As you can see, you may have a lot of errors. Each error will have one or more errors
associated with it. The way you query in records is exactly the same as it is for the
"Maintenance" forms (See section 4.8.4 - Executing a Query for more information on
how to execute queries).
The basic premise of what you do in Correct is simple:
• Find the type of data that you need to correct
• Query in the records that you want to look at
• Make the necessary changes
• Save (or Commit) those changes
• Go back to the "Batch" menu item
• Press the "Submit Corrected Data" button
But sometimes you will have hundreds (or even thousands of records that you want to
make the same changes to. Do you have to change each one by hand one at a time? No!
Version 1.0
4-48
-------
AQS Fundamentals
AQS Application
That is where some of the other tools available to Correct come into play. All of the tools
that are mentioned in section 4.3.3.4 (Oracle Form Functions - Record) work exactly the
same here. There is one function that is unique to Correct: Search & Replace.
4.10.1 Search and Replace
We have all used a "Search and Replace" function (we'll call it "S&R" for short) in a
word processor or spreadsheet, right? You look for some text and you want to replace it
with some other text. Well, the S&R in Correct works a little differently. For this
program, S&R will change any value that currently exists in a column to the specified
value. So the proper way to do a search and replace is as follows:
• Query in records that have a value that you would like to replace
• Place your cursor on the
column where you would
like to replace the value
• Click on the S&R Icon
Fill out the "New Value"
field to be the "Replace"
value
Although the form will show you the "Current Value" on the form, it will actually change
the value of any record currently queried into the form.
4.10.2 Delete All Selected
Another button that appears below the records in the given staging table is the "Delete
All Selected" button. This button will delete all records currently queried into the form.
This is a great way to get rid of records that you no longer want rather than deleting them
one at a time using the buttons.
4.10.3 Delete by Screening Group
The last item on the Correct menu is "Delete by Screening Group". Let's say that things
have just turned into a complete mess and you just want to start over. That is where this
function comes into play. It will delete any record that your screening group owns in any
of the staging tables. Keep in mind that it will only delete items in the staging tables. It
will not delete data that may be "pre-production" in the database tables. Only one person
per screening group has the authority to use this function, so you may or may not have
this option available to you.
Version 1.0
4-49
-------
AQS Fundamentals AQS Application
Version 1.0 4-50
-------
Appendix B - AQS Batch Input Transaction Formats AQS Application
5 Appendix A - Initial AQS Setup
This section provides instructions on the one-time setup that is required on each computer
that will interface with AQS. There are two (2) pieces that you will need to install:
Oracle JInitiator and the JavaBean Certificate.
5.1 Oracle JInitiator
To begin setup, click the following URL Link:
https://iasint rtpnc.cpa.gov/foiTns90/f90servlct?config=aqs&form=AOOOfmx
Clicking this link will download approximately 10MB of software, so it may take a few
minutes depending on your connection speed.
If prompted, click "Accept" or "Next" to download this plug-in and follow the default
installation to install it in the following directory:
C:\Program Files\Oracle\JInitiator 1.3.1.18.
If the JInitiator 1.3.1.18 does not install and you have not installed it previously, then you
probably need your network administrator to install new software for you. Do not
proceed until the new JInitiator is installed. Also have your network administrator do
step 2 below as well.
AQS will attempt to start but will fail, probably with a Java error message something like
this:
Java. long. ClassNotFoundException:oracle.forms. engine. Main
Wait for the error message, then close that window and go back to this page.
5.2 JavaBean Certificate
This step updates the security certificate on your PC.
1) Right click on run me after imit install.txt file I=I(35KB dated 1 Mar 06) and
save it to your desktop ("Save Target As" if you are using Internet Explorer).
2) Right click the icon on your desktop for this file and rename it to
run_me_after_jinit_install.Aa/ to continue with this setup.
3) Run (i.e., double click) run_me_afterjinit_install.bat on your desktop and
accept the default file location. (You may see a window with text scrolling by
quickly, then that window will close.)
4) Close your browser and restart it before accessing AQS.
Version 1.0
-------
AQS Fundamentals AQS Application
5) Navigate to http://www.epa.gov/ttnyairs/airsaqs/ and select AQS Web from the
sidebar ~ or ~ go directly to
http://www.epa.gov/ttn/airs/airsaQs/aqsweb/aqswebwammg.htm and click the link
dated 3-Mar-06 "I agree to these terms and want to enter the AQS application."
(You may want to bookmark that address.)
5.3 CDX Registration
New users need to register on CDX using the registration key you received in an email.
Use this link to access CDX Registration. If you are asked to choose a role during the
registration process, select the role of "submitter".
Once you hove completed the CDX registration, close your browser and reopen it before
attempting to use AQS and CDX.
If you need assistance accessing any of the material in AQS, User Support is provided
through the Customer Call Center. The toll free number is 866-411-4EPA (866-411-
4372). Please contact them first with any questions about using the AQS application.
Version 1.0 5-2
------- |