1 / 68

AQS Discoverer

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

gwyn
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. AQS Discoverer

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

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

  4. Annual Summaries Sites Monitors

  5. AQS The End User Layer = bridge End User Layer

  6. What can Discoverer provide? • Data Listing Reports • Cross-Tabulation Reports (Pivot Tables) • Graphs & Charts • Data extractions (Excel, HTML, and CSV)

  7. Key Terminology Interface to database = End User Layer Grouping of Table = Business Area Group of worksheets = Workbook

  8. 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 AQSTeam@epa.gov

  9. 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!

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

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

  12. Discoverer Exercise 1 (States in a Region) • Your screen should look like this:

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

  14. Discoverer Exercise 1 (States in a Region) • Your screen should look like:

  15. Discoverer Exercise 1 (States in a Region) • Click the “Next” button • You will see the following screen:

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

  17. Discoverer Exercise 1 (States in a Region) • Your screen should look like:

  18. 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:

  19. 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”

  20. 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:

  21. Discoverer Exercise 1 (States in a Region) • Click the “Next” button, you will see the following screen (Click the “New” button):

  22. 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:

  23. Discoverer Exercise 1 (States in a Region) • After clicking the “OK” button your screen will look like the following:

  24. 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:

  25. 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:

  26. 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:

  27. 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:

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

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

  30. Discoverer Exercise 2 (Agencies in a State) • Your screen should look like the following: • Click the “Next” button

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

  32. Discoverer Exercise 2 (Agencies in a State) • Your screen should look like the following:

  33. 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:

  34. 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:

  35. Discoverer Exercise 2 (Agencies in a State) • Now Click the Conditions tab • Your screen should look like the following:

  36. 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)

  37. Discoverer Exercise 2 (Agencies in a State) • Your screen should look like the following:

  38. Discoverer Exercise 2 (Agencies in a State) • Click the “Next” button • Your screen should look like the following:

  39. 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:

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

  41. Discoverer Exercise 2 (Agencies in a State) • Your screen should look similar to the following:

  42. 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:

  43. 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”

  44. Discoverer Exercise 2 (Agencies in a State) • Your screen should look like the following:

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

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

  47. 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:

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

  49. Discoverer Exercise 3 (Active Monitors) • Your screen should look like the following:

  50. 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:

More Related