-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
1/55
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
2/55
2
Goals for this classoals for this class Introduce Discoverer Plus Finding the data you need Designing your workbooks Sharing your results Helping yourself
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
3/55
3
What is Discoverer?hat is Discoverer? A business intelligence tool for ad hoc
queries, reporting, analysis, and Webpublishing from Oracle Corporation A retrieval only tool that works on arelational database whether its designedfor online transaction processing (OLAP)or as a data warehouse
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
4/55
4
Itts the ad hoc query tool for AQS!the ad hoc query tool for AQS!How many AQS
sites are there inCalifornia?
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
5/55
5
Itts the ad hoc query tool for AQS!the ad hoc query tool for AQS!
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
6/55
6
How many active criteria monitors
are there in Region 9 states?
Discoverer Exported to Excel
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
7/55
7
How does this work?ow does this work?End User LayerEnd User Layer
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
8/55
8
Key Terminologyey TerminologyTables FoldersColumns ItemsRows RecordsQueries Worksheets
Interface to database = End User Layer
Grouping of Tables = Business AreaGroup of worksheets = Workbook
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
9/55
9
What you needhat you need AQS userid and password Java-enabled Web browser Java Virtual Machine (JVM) such as Oracle
JInitiator Latest security certificate (pokeCertdb.bat) url:https://iasint.rtpnc.epa.gov/discoverer/plus
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
10/55
10
https://iasint.rtpnc.epa.gov/discoverer/plushttps://iasint.rtpnc.epa.gov/discoverer/plus
Starting Discoverer PlusStarting Discoverer Plus
https://iasint.rtpnc.epa.gov/discoverer/plushttps://iasint.rtpnc.epa.gov/discoverer/plushttps://iasint.rtpnc.epa.gov/discoverer/plushttps://iasint.rtpnc.epa.gov/discoverer/plushttps://iasint.rtpnc.epa.gov/discoverer/plushttps://iasint.rtpnc.epa.gov/discoverer/plus -
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
11/55
11
Create a Connectionreate a Connection
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
12/55
12
Connection CreatedConnection Created
Use this
connection
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
13/55
13
Connection CreatedConnection Created
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
14/55
14
Successful connectionuccessful connection
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
15/55
15
Successful connectionuccessful connection
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
16/55
16
Create/Open a Workbook?Create/Open a Workbook?
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
17/55
17
AQSDatabase
ERD
Monitors
Sites
AnnualSummaries
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
18/55
18
Your Business Areasour Business Areas Folders are composed primarily of views(i.e., combinations of tables) in the
aqsprod database as described in theData Dictionary Reference tables (e.g., States,
Parameters, Units) Raw data and other additional folders inAQSprod 9i
AQS basicQS basic AQSprod 9iQSprod 9i
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
19/55
19
Data Model for AQSata Model for AQS
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
20/55
20
Data Model for AQS, contata Model for AQS, contd
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
21/55
21
Data Model for AQS, contata Model for AQS, contd
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
22/55
22
Data Model for AQS, contata Model for AQS, contd
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
23/55
23
Create/Open a Workbookreate/Open a Workbook
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
24/55
24
Creating a New Workbookreating a New Workbook Use the
WorkbookWizard Start with a
simple goal inmind e.g.,
Find the statesin yourregion
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
25/55
25
Selecting Itemselecting Items AQS basic is the
simplest BusinessArea
Scroll throughlist of folders Expand a folder
to see its items
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
26/55
26
Selecting Itemselecting Items AQS basic is the
simplest BusinessArea
Scroll throughlist of folders Expand a folder
to see its items
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
27/55
27
Highlightitems andmovethem to
the Selectedside
Selecting Items, contelecting Items, contd
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
28/55
28
Table Layoutable LayoutClick and drag headings
to desired order
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
29/55
29
Formattingormatting Format data and
headings for items Edit heading for anitem
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
30/55
30
Conditionsonditions
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
31/55
31
Sorts and Calculationsorts and Calculations
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
32/55
32
Percentages and Totalsercentages and Totals
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
33/55
33
Parameters and Resultsarameters and Results
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
34/55
34
1. Launch a web browser2. Go to https://iasint.rtpnc.epa.gov/discoverer/plus3. Create your connection and Connect to aqsprod4. Create a new workbook using the AQS basic business area to
show all states within your EPA region5. Adjust the table layout so that EPA Region is in column 1,
State Abbreviation in column 2, State Name in column 3,State Code in column 4
6. Center headings for all columns (Format)7. Sort by EPA Region, then State Abbreviation. Change to a
group sort on EPA Region.8. Add a title
Exercise 1xercise 1
https://iasint.rtpnc.epa.gov/discoverer/plushttps://iasint.rtpnc.epa.gov/discoverer/plus -
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
35/55
35
Exercise 1: Sample Resultsxercise 1: Sample Results
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
36/55
36
Did you notice?id you notice?
List of Values (LOV) Format multiple headings
Page Items Conditions may be inactive
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
37/55
37
Exercise 2xercise 21. List all the parameters (pollutants)measured at site 06-001-00072. Modify #1 to include only parameters
being sampled since 20033. Modify #1 to show sampling end dates
No Cheating!
Exercise 2 Results
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
38/55
38
All parameters at site All parms since 2003 All parms w/end date
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
39/55
39
1. List all sites in your state that have beenterminated. For each row, show the state code,county code, site ID, date site created and datesite terminated.
2. Add a title.3. Include a count of the number of sites that havebeen terminated for each county and a grandtotal at the bottom. (Hint: Use Totals underTools menu or .)
Exercise 3xercise 3
Exercise 3 sample results
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
40/55
40
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
41/55
41
Crosstabrosstabther Views -
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
42/55
42
add a graph.dd a graph.
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
43/55
43
Output Optionsutput Options - Excelxcel
utput Options TML
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
44/55
44
Output Options - HTML
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
45/55
45
Other Output Optionsther Output Options
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
46/55
46
Using More Featuressing More Features1. Find all PM 2.5 monitors with sampling periodsbeginning in 20052. Sort the results by state abbreviation, county, site,and POC3. Display start date and end date (if any) for samplingperiods4. Display count of monitors in each state.5. Add an appropriate title6. Save the workbook on the database7. Share your workbook with your neighbor
Goal:
Desired ResultsDesired Results
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
47/55
47
Live DemoLive Demo
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
48/55
48
Live Demo . . . . . . . . . . . . . . . . . . .Live Demo . . . . . . . . . . . . . . . . . . .
Post presentation note:
During conference session, the worksheet
showing the desired results on the previouspage was shown.
Then the worksheets shown in the next 4 pageswere shown.
All of these workbooks have been shared w ith allusers. Look for them with names beginning w ith
BOS. Users can use them as they are, or editthem and save them under their own userid.
Monthly Max Values with Graphonthly Max Values with Graph
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
49/55
49
Sample Monthly Max, Min, Mean by Monitorample Monthly Max, Min, Mean by Monitor
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
50/55
50
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
51/55
Sample Quarterly Completeness Reportample Quarterly Completeness Report
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
52/55
52
Hints and Tips and Other Good Stuffints and Tips and Other Good Stuff
-
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
53/55
53
AQSprod is the database instance name Discoverer is accessing the live production database (AQSprod same as the AQS
application) Your Discoverer userid and password are the same as your AQS application userid
and password Only registered AQS users can accessAQSprod via Discoverer If you delete your cookies, youll have to recreate your connection Your Connection must be created on each different PC you use There is one EUL for AQS; multiple Business Areas AQS basic has the most commonly used tables
AQSprod 9i includes all the tables inAQS basic plus raw data current and a fewother related tables Online Help is pretty good! Any workbook you share with others will have your userid appended to the
beginning of the workbook name You can get to Discoverer from the AQS Web Application link on the EPA TTN
AQS page - http://www.epa.gov/ttn/airs/airsaqs/ You can find answers to some of the Frequently Asked Questions from a link on
the same page
Many of the reference tables are viewable fromhttp://www.epa.gov/ttn/airs/airsaqs/manuals/codedescs.htm
Hints and Tips and Other Good Stuff, contints and Tips and Other Good Stuff, contd
http://www.epa.gov/ttn/airs/airsaqs/http://www.epa.gov/ttn/airs/airsaqs/http://www.epa.gov/ttn/airs/airsaqs/manuals/codedescs.htmhttp://www.epa.gov/ttn/airs/airsaqs/manuals/codedescs.htmhttp://www.epa.gov/ttn/airs/airsaqs/manuals/codedescs.htmhttp://www.epa.gov/ttn/airs/airsaqs/ -
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
54/55
54
Max time for Discoverer queries = 60mins; Max number of rows= 100,000 Many of the date fields are defined as YYYYMonDD HH:MM e.g.,2005Jun11 01:22 Many date fields are also defined for just the YYYY, or Q, Mon, or DD, or
HH, or MM
If you cant get a total within Discoverer, you may want to export to aspreadsheet and perform your totals (and other calculations) there. Java is secure with the Verisign Security Certificate updated annually Your browser must allow downloads of signed Active X controls to install
the JInitiator (Java Initiator) from Oracle either by identifying Oracle as atrusted site or prompting. As of 6/11/05, were using version 1.3.1.18 of theJInitiator. (Use Windows Control Panel to see JInitiator Control Panel)
You can change the default directory for exported worksheets Set your defaults to show your conditions and date/time in the title
Oracle Technology Network is a good source for information freeregistration look for Discoverer with Data Warehousing and BusinessIntelligence(http://www.oracle.com/technology/products/discoverer/index.html)
The next version will always be better . . . Or not.
Theres lots to do --
http://www.oracle.com/technology/products/discoverer/index.htmlhttp://www.oracle.com/technology/products/discoverer/index.html -
8/6/2019 Discoverer Web Training in San Diego-Spreadoutpages
55/55
55
There s lots to do
Discover the datawith DISCOVERERISCOVERER!