Aqs discoverer
This presentation is the property of its rightful owner.
Sponsored Links
1 / 68

AQS Discoverer PowerPoint PPT Presentation


  • 143 Views
  • Uploaded on
  • Presentation posted in: General

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?.

Download Presentation

AQS Discoverer

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


Aqs discoverer

AQS Discoverer


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


Aqs discoverer

Annual Summaries

Sites

Monitors


Aqs discoverer

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)


Aqs discoverer

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

  • <Beginner>

  • 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

    • <Intermediate>

  • 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


    Aqs discoverer

    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


    Aqs discoverer

    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


  • Login