aqs discoverer
Download
Skip this Video
Download Presentation
AQS Discoverer

Loading in 2 Seconds...

play fullscreen
1 / 68

AQS Discoverer - PowerPoint PPT Presentation


  • 207 Views
  • Uploaded on

AQS Discoverer. In This Section We Will Talk About. What Is Discoverer? What can Discoverer do for you? What is needed to use Discoverer? How to Build a Query? Put it into Practice: Exercises 1 -5. What is Discoverer?.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'AQS Discoverer' - gwyn


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
in this section we will talk about
In This Section We Will Talk About
  • What Is Discoverer?
  • What can Discoverer do for you?
  • What is needed to use Discoverer?
  • How to Build a Query?
  • Put it into Practice: Exercises 1 -5
what is discoverer
What is Discoverer?
  • A business intelligence tool for customizable queries, reporting, analysis, and Web publishing from Oracle Corporation
  • A retrieval only tool that works on a relational database
  • Advantages
    • Manages the complexities of the database for you
    • User Interface (UI) guides you through collecting data from the AQS database
    • Provides the ability to create, share and export your queries
slide4
Annual Summaries

Sites

Monitors

slide5
AQS

The End User Layer = bridge

End User Layer

what can discoverer provide
What can Discoverer provide?
  • Data Listing Reports
  • Cross-Tabulation Reports (Pivot Tables)
  • Graphs & Charts
  • Data extractions

(Excel, HTML, and CSV)

slide7
Key Terminology

Interface to database = End User Layer

Grouping of Table = Business Area

Group of worksheets = Workbook

what you need
What you need
  • To access Discoverer
    • AQS user ID and password
    • Java-enabled browser
    • URL: https://oasext.epa.gov/discoverer/plus
  • To Build a Good Query
    • FORM A GOOD QUESTION!
    • Understand the Data You Need to Answer the Question
    • For assistance with Discoverer, send an email to [email protected]
building a query
Building a query
  • Create a Connection
  • Choose a Layout
  • Select Items
    • To Display
    • To Filter By
  • Specify Sort (Optional)
  • Specify Totals (Optional)
  • Specify run-time Parameters (Optional)
  • Run Report
  • FORM A GOOD QUESTION!
put it into practice exercises 1 5
Put it into practice: Exercises 1 - 5
  • Exercise 1: “What are all the States in a given EPA Region?”
    • Learn how to Order columns
    • Learn how to Sort
    • Learn how to Create a Parameter
  • Exercise 2: “What are all the Agencies in a given State?”
    • Add a Title
    • Learn how to Group Sort
  • Exercise 3: “What are all the active monitors in a given EPA Region?”
    • Add a Condition (Formula)
  • Exercise 4: “What Exceptional Events occurred in an area?”
      • Explore a folder
  • Exercise 5: Open an Existing Query
  • Example: Share a query
discoverer exercise 1 states in a region
Discoverer Exercise 1 (States in a Region)
  • Question: “What are all the States in a given EPA Region?”
  • Goals for this Beginner query:
    • Learn how to Order columns
    • Learn how to Sort
    • Learn how to Create a Parameter
  • Start Discoverer and login
  • Go to “Workbook Wizard Step 1 of 5”screen
  • Select:
    • Title
    • Table
  • Unselect :
    • Page Items
    • Crosstab
    • Graph
    • Text Area
discoverer exercise 1 states in a region1
Discoverer Exercise 1 (States in a Region)
  • Your screen should look like this:
discoverer exercise 1 states in a region2
Discoverer Exercise 1 (States in a Region)
  • Click the “Next“ button
  • Scroll down until you see the “States” folder
  • Click the “+” button to expand the folder
  • Select the following from the list:
    • State Code
    • State Abbr
    • State Name
    • EPA Region Code
discoverer exercise 1 states in a region3
Discoverer Exercise 1 (States in a Region)
  • Your screen should look like:
discoverer exercise 1 states in a region4
Discoverer Exercise 1 (States in a Region)
  • Click the “Next” button
  • You will see the following screen:
discoverer exercise 1 states in a region5
Discoverer Exercise 1 (States in a Region)
  • Order the columns by dragging them to the desired location:
    • Click on the “EPA Region Code” column
      • While holding down the left mouse button drag the column to be the first one
    • Click on the “State Code” column
      • While holding down the left mouse button drag the column to be the second one
    • Click on the “State Abbr” column
      • While holding down the left mouse button drag the column to be the third one
    • Click on the “State Name” column
      • While holding down the left mouse button drag the column to be the fourth one

*** Note ***

Depending on how you added the columns you may not need to reorder any of the columns

discoverer exercise 1 states in a region6
Discoverer Exercise 1 (States in a Region)
  • Your screen should look like:
discoverer exercise 1 states in a region7
Discoverer Exercise 1 (States in a Region)
  • Click the “Next” button
  • Now sort the results by the following:
    • EPA Region Code
    • State Code
    • State Abbr
  • Click the “Add” button to add a new sort criteria:
discoverer exercise 1 states in a region8
Discoverer Exercise 1 (States in a Region)
  • After clicking the “Add” button you will see the following screen:
    • Make sure the first column to sort by is “State Code”
discoverer exercise 1 states in a region9
Discoverer Exercise 1 (States in a Region)
  • Now click the “Add” and add the remaining columns to sort by. Your screen should look like the following:
discoverer exercise 1 states in a region10
Discoverer Exercise 1 (States in a Region)
  • Click the “Next” button, you will see the following screen (Click the “New” button):
discoverer exercise 1 states in a region11
Discoverer Exercise 1 (States in a Region)
  • You will see the following screen (Enter the data marked by the “red” arrows and click the “OK” button:
discoverer exercise 1 states in a region12
Discoverer Exercise 1 (States in a Region)
  • After clicking the “OK” button your screen will look like the following:
discoverer exercise 1 states in a region13
Discoverer Exercise 1 (States in a Region)
  • Now click the “Finish” button
  • Discoverer will now run the query and prompt you to supply the “Region Code” parameter that you specified.
  • Your screen will look like the following:
discoverer exercise 1 states in a region14
Discoverer Exercise 1 (States in a Region)
  • At the prompt enter the “Region Code” and click the “OK” button
  • Discoverer will execute the query based on the “Region Code” that you specified. Your output should look similar to the following:
discoverer exercise 1 states in a region15
Discoverer Exercise 1 (States in a Region)
  • Now let’s change the title to reflect what our query is really doing.
    • Double click the text that says: “Double click to edit Title”
    • Change the text to read: “States in a Region”
    • Click the “OK” button
    • Your screen should now look like the following:
discoverer exercise 1 states in a region16
Discoverer Exercise 1 (States in a Region)
  • The final step is to save our query so we can use it at a later date
    • Click the save workbook icon on the main menu:
    • After click the save workbook icon you will see the following screen:
discoverer exercise 1 states in a region17
Discoverer Exercise 1 (States in a Region)
  • In the “New Name” field enter the name you want to save this query as.
  • That’s it, you’re done with our first example
discoverer exercise 2 agencies in a state
Discoverer Exercise 2 (Agencies in a State)
  • Question: “What are all the Agencies in a given State?
  • Start Discoverer and login
  • Go to “Workbook Wizard Step 1 of 5”screen
  • Select:
    • Title
    • Table
  • Unselect :
    • Page Items
    • Crosstab
    • Graph
    • Text Area
discoverer exercise 2 agencies in a state1
Discoverer Exercise 2 (Agencies in a State)
  • Your screen should look like the following:
  • Click the “Next” button
discoverer exercise 2 agencies in a state2
Discoverer Exercise 2 (Agencies in a State)
  • Scroll down until you see the “States” folder
  • Click the “+” button to expand the folder
  • Select the following from the list (Select the columns in the following order, that way you don’t have to rearrange the columns later):
    • EPA Region Code
    • State Code
    • State Abbr
    • State Name
discoverer exercise 2 agencies in a state3
Discoverer Exercise 2 (Agencies in a State)
  • Your screen should look like the following:
discoverer exercise 2 agencies in a state4
Discoverer Exercise 2 (Agencies in a State)
  • Now scroll up to the State Agencies folder
  • Expand the State Agencies folder
  • Add the “Agency Code” column to the selected list
  • Your screen should now look like the following:
discoverer exercise 2 agencies in a state5
Discoverer Exercise 2 (Agencies in a State)
  • Now scroll up to the Agencies folder and Expand the Agencies folder
  • Add the following columns to the selected list:
    • Agency Desc
    • Agency Type
    • Agency Type Desc
    • Status Ind
  • Your screen should look like the following:
discoverer exercise 2 agencies in a state6
Discoverer Exercise 2 (Agencies in a State)
  • Now Click the Conditions tab
  • Your screen should look like the following:
discoverer exercise 2 agencies in a state7
Discoverer Exercise 2 (Agencies in a State)
  • Now enter the following information:
    • What description would you like to give your condition?
      • State Code
    • Item (States.State Code)
    • Condition (=)
    • Values (Enter the State Code you wish to run the query for)
discoverer exercise 2 agencies in a state8
Discoverer Exercise 2 (Agencies in a State)
  • Your screen should look like the following:
discoverer exercise 2 agencies in a state9
Discoverer Exercise 2 (Agencies in a State)
  • Click the “Next” button
  • Your screen should look like the following:
discoverer exercise 2 agencies in a state10
Discoverer Exercise 2 (Agencies in a State)
  • We aren’t going to rearrange the columns this time, we took care of the order when we added the columns to the selected list
  • Click the “Next” button
  • Add the following as the sort criteria:
    • EPA Region Code
    • State Code
    • Agency Code
  • Your screen should look like the following:
discoverer exercise 2 agencies in a state11
Discoverer Exercise 2 (Agencies in a State)
  • Discoverer will now execute the query
  • Since we did not create a parameter for the State Code Discoverer will use the State Code we entered as a condition to run the query
  • Your screen should look like the following:
  • Click the “Finish” button and the query will execute
discoverer exercise 2 agencies in a state12
Discoverer Exercise 2 (Agencies in a State)
  • Your screen should look similar to the following:
discoverer exercise 2 agencies in a state13
Discoverer Exercise 2 (Agencies in a State)
  • Let’s change the title
    • Double click the top are of the screen that says:
      • “Double-click here to edit the title”
    • Enter the following for the title:
      • “Region / States / Agencies”
    • Your screen should look similar to the following:
discoverer exercise 2 agencies in a state14
Discoverer Exercise 2 (Agencies in a State)
  • Next we’re going to add “Group” sorting to a couple of the columns
    • Group sorting is a way of sorting the results which only displays the first unique value. It does not eliminate values, it merely makes the output cleaner and easier to follow
  • Let’s get started:
    • Click the “LEFT” mouse button on the column header for EPA Region Code
      • The entire column will be highlighted when you click the header column
    • Click the “RIGHT” mouse button and you will get a popup menu
      • From the popup menu select “Group Sort”
discoverer exercise 2 agencies in a state15
Discoverer Exercise 2 (Agencies in a State)
  • Your screen should look like the following:
discoverer exercise 2 agencies in a state16
Discoverer Exercise 2 (Agencies in a State)
  • Repeat this process for the following columns:
    • State Code
    • State Abbr
    • State Name
    • Agency Code
  • Your screen should look similar to the following:
  • You’re done – Don’t forget to save your query.
discoverer exercise 3 active monitors
Discoverer Exercise 3 (Active Monitors)
  • Question: “What are all the active Monitors in a given EPA Region?”
  • Start a new query. Your screen should look like the following:
  • Click the “Next” button
discoverer exercise 3 active monitors1
Discoverer Exercise 3 (Active Monitors)
  • Scroll down and expand the “States” folder
    • Add “EPA Region Code” to the selected list
  • Your screen should look like the following:
discoverer exercise 3 active monitors2
Discoverer Exercise 3 (Active Monitors)
  • Now scroll up and expand the “Monitors V” folder
  • Add the following columns:
    • State Code
    • County Code
    • Site ID
    • Parameter Code
    • POC
    • Monitor ID
    • Tribal Monitor ID
  • Do not use “Close Date” – Ask me why . .
discoverer exercise 3 active monitors3
Discoverer Exercise 3 (Active Monitors)
  • Your screen should look like the following:
discoverer exercise 3 active monitors4
Discoverer Exercise 3 (Active Monitors)
  • Now scroll down and expand the “Sample Period V” folder
  • Add the following column
    • Date Sampling Ended
  • Your screen should look like the following:
discoverer exercise 3 active monitors5
Discoverer Exercise 3 (Active Monitors)
  • We need to add a condition for this query
    • Click on the “Conditions” tab at the top of this dialog
    • Your screen should look like the following:
discoverer exercise 3 active monitors6
Discoverer Exercise 3 (Active Monitors)
  • Right click your mouse on the “My Conditions” link
    • You will get a popup menu
    • Select “New Condition” from the popup menu
discoverer exercise 3 active monitors7
Discoverer Exercise 3 (Active Monitors)
  • We need to answer the following questions:
    • What would you like to name your condition?
    • What description would you like to give your condition?
    • Formula
  • Uncheck “Generate name automatically”
    • This will enable us to enter the name of the condition
    • If you don’t uncheck this box Discoverer will generate a name for you
  • Populate the fields with the following information:
    • What would you like to name your condition?
      • Active Monitors
    • What description would you like to give your condition?
      • Active Montiors
discoverer exercise 3 active monitors8
Discoverer Exercise 3 (Active Monitors)
  • Formula
    • Item
      • Sample Periods V Date Sampling Ended
    • Condition
      • <>
    • Values
      • NULL
  • Your screen should look like the following:
discoverer exercise 3 active monitors9
Discoverer Exercise 3 (Active Monitors)
  • Right click your mouse on the “My Conditions” link
    • You will get a popup menu
    • Select “New Condition” from the popup menu
discoverer exercise 3 active monitors10
Discoverer Exercise 3 (Active Monitors)
  • We need to answer the following questions:
    • What would you like to name your condition?
    • What description would you like to give your condition?
    • Formula
  • Uncheck “Generate name automatically”
    • This will enable us to enter the name of the condition
    • If you don’t uncheck this box Discoverer will generate a name for you
  • Populate the fields with the following information:
    • What would you like to name your condition?
      • Region
    • What description would you like to give your condition?
      • Region Code
discoverer exercise 3 active monitors11
Discoverer Exercise 3 (Active Monitors)
  • Formula
    • Item
      • States EPA Region Code
    • Condition
      • =
    • Values
      • 04 (The region you want to filter by)
  • Your screen should look like the following:
discoverer exercise 3 active monitors12
Discoverer Exercise 3 (Active Monitors)
  • Click the “Next” button
  • You should now be at the screen “Step 4 of 5: Sort”
  • Add the following for your sort criteria:
    • State Code
    • County Code
    • Parameter Code
  • Your screen should look like the following:
discoverer exercise 3 active monitors13
Discoverer Exercise 3 (Active Monitors)
  • Click the “OK” button
  • Your Parameters screen should now look like the following:
  • We don’t have any parameters, so click the “Finish” button to run the query
  • Remember to change the title of your query
discoverer exercise 3 active monitors14
Discoverer Exercise 3 (Active Monitors)
  • Click the “Finish” button
  • Discoverer will now run your query and prompt you for the Region Code
  • Your results should look similar to the following screen:
  • Save your query and you’ve completed the “Active Monitors” query
discoverer exercise 4 exceptional events
Discoverer Exercise 4 (Exceptional Events)

Question: “What Exceptional Events occurred in an area?”

Due to the time constraints of the training class we are narrowing the result set down so the query will run relatively fast.

  • Start a “New” workbook
  • Select the following at “Step 1 of 5: Worksheet Layout”
    • Title
    • Table
  • Unselect the following at “Step 1 of 5: Worksheet Layout”
    • Page Items
    • Graph
    • Text Area
  • Your screen should look like the following:
discoverer exercise 4 exceptional events1
Discoverer Exercise 4 (Exceptional Events)
  • Click the “Next” button
  • Expand the “Raw Data Concurrences” folder and add the following items to the selected list:
    • State Code
    • County Code
    • Site ID
    • Parameter Code
    • POC
    • Event Code
    • Event Code Description
    • Event Concurrence Indicator
    • Event Concurrence Date
    • Event Concurrence Comment
    • Sampling Begin Datetime: YYYY
    • Sampling Begin Datetime: Mon
discoverer exercise 4 exceptional events2
Discoverer Exercise 4 (Exceptional Events)
  • Your screen should look like:
  • We aren’t going to change the layout of the results, so click the “Next” button
  • Add the following sort criteria:
    • State Code
    • County Code
    • Parameter Code
    • Poc
    • Event Code
discoverer exercise 4 exceptional events3
Discoverer Exercise 4 (Exceptional Events)
  • Add the parameters for this query

After the class is over you can change the parameters to customize the query.

  • Add the following parameters:
    • State Code – use “36”
    • County Code – use “013”
    • Site ID – use “0011”
    • Parameter – use “88101”
  • Your completed parameter screen:
  • Click the “Finish” button to run the query
discoverer exercise 4 exceptional events4
Discoverer Exercise 4 (Exceptional Events)
  • The query could take a couple of minutes. . .
  • Once complete, your screen should look similar to this:
  • Add a title
  • Save the query
slide66
Discoverer Exercise 5 (Open an Existing Query)
  • In Workbook Wizard  “Open an existing workbook”
  • OR from Menu bar: File  Open
  • Next “Open Workbook from Database”
  • Scroll to “Method info per parameter”
  • Click “Open”
  • Change the Conditions to look at PM2.5 (88101) methods
discoverer example sharing a query
Discoverer Example (Sharing a Query)
  • Share your query with another user – you’ll need to know their AQS user ID
  • Click “File” and then “Share” from the main menu
slide68
Discoverer Example (Sharing a Query)
  • Discoverer only shows 100 users at a time, click the next button to see the next 100 users
  • Once you find the user you wish to share the query with do the following:
    • Highlight the user
    • Drag the user to the “Shared” list
    • Click the “OK” button
  • Congrats, you’ve now shared your query
ad