680 likes | 928 Vues
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?.
E N D
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? • 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
Annual Summaries Sites Monitors
AQS The End User Layer = bridge End User Layer
What can Discoverer provide? • Data Listing Reports • Cross-Tabulation Reports (Pivot Tables) • Graphs & Charts • Data extractions (Excel, HTML, and CSV)
Key Terminology Interface to database = End User Layer Grouping of Table = Business Area Group of worksheets = Workbook
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
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 • <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) • 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 Region) • Your screen should look like this:
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 Region) • Your screen should look like:
Discoverer Exercise 1 (States in a Region) • Click the “Next” button • You will see the following screen:
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 Region) • Your screen should look like:
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 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 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 Region) • Click the “Next” button, you will see the following screen (Click the “New” button):
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 Region) • After clicking the “OK” button your screen will look like the following:
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 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 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 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 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) • 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 State) • Your screen should look like the following: • Click the “Next” button
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 State) • Your screen should look like the following:
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 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 State) • Now Click the Conditions tab • Your screen should look like the following:
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 State) • Your screen should look like the following:
Discoverer Exercise 2 (Agencies in a State) • Click the “Next” button • Your screen should look like the following:
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 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 State) • Your screen should look similar to the following:
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 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 State) • Your screen should look like the following:
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) • 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 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 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 Monitors) • Your screen should look like the following:
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: