University of Alaska Anchorage | University of Alaska ...



QAdhoc

DSD QUERY MENU

(ad hoc component)

Information Technology Services

UA Help Desk and Training

sxtrain@alaska.edu

| | |

|Table of Contents | |

Table of Contents 2

Introduction 2

What is QAdhoc? 2

What QAdhoc is NOT! 2

Keeping Current 2

System Requirements 3

(or “What you need to run QADHOC”) 3

Secure Access 3

Offering Suggestions 3

Logging “ON” to QAdhoc 4

Important Note 4

QMenu/QAdhoc “Tickets” 5

How To Handle Expired Tickets During Active Query Session 5

Starting QAdhoc 6

Logging “OFF” 7

Resetting Your Webcode 8

Important Note: 8

Selecting a View Category 9

Selecting a View 10

Reviewing Online Documentation or “Metadata” 11

User Authorized 12

Object Name, Type and Associated Roles 12

Data Sources 12

Fields Requiring Conditions 12

Description 13

Retrieving Query Parameter Form 14

Building a Simple Query 15

How to tips 16

Extract Results to a File 16

Use Radio Buttons for HEADERS 16

Use RETRIEVE ONLY DISTINCT ROWS Checkbox 17

Use Other Features 17

Additional Detail Selection Criteria 18

Executing a Simple Query 19

Viewing Query Output 21

Important Note: 21

Troubleshooting 22

Creating Aggregate Fields 24

Saving and Printing Your Query Report 27

Printing Query Output 27

Office97 (Excel97) 28

Office2000 (Excel 2000) 28

Learning More About QMenu/QAdhoc Data 29

Data Sources 29

Appendix 30

UAF Access 30

Statewide Access 30

QMenu/QAdhoc Training Support Contact Numbers 30

Problem Reports: Reporting Problems Once You Know How QMenu/QAdhoc Works 31

Requests for New DSD QAdhoc Views 31

QMenu/QAdhoc-Related Bookmarks 31

UA Help Desk and Training Homepage 31

Accessing QMenu & QAdhoc 31

QAdhoc Help 31

Decision Support Database Homepage 31

| | |

|Introduction | |

| | | |

| |What is QAdhoc? |

| | |QAdhoc is the ad hoc query component of the DSD QUERY MENU (QMenu) which is an Oracle Web Application |

| | |Server interface to the University of Alaska Decision Support Database (i.e. 'RPTS', 'data warehouse').|

| | |University of Alaska staff and faculty members use QAdhoc when no preexisting QMenu query matches their|

| | |information needs. |

| | | |

| | |QAdhoc facilitates the retrieval of batches of records meeting user-supplied criteria from predefined |

| | |"views" of administrative information. QAdhoc defaults to batch retrievals of 150 records at a time. |

| | |However you may change the batch value or elect to retrieve all records in a single pass. |

| | | |

| | |WARNING: QAdhoc does not know how much memory is available to your browser or how large a document it |

| | |can successfully load. It is your responsibility to ensure you do not attempt to retrieve more |

| | |information than your browser can handle. Counting rows is a good idea. The number of qualifying rows |

| | |multiplied by the number of characters expected in the selected columns is a rough calculation for the |

| | |size of the resulting html document, which will be returned to your browser. |

| | | |

| | |Individuals needing to retrieve large quantities of records may find QAdhoc inconvenient to use and |

| | |should investigate other ad hoc query tools. Individuals retrieving only a few hundred records will |

| | |not be hampered by your Web browser’s record retrieval limitation. |

| |What QAdhoc is NOT! |

| | |QAdhoc is not a full-featured ad hoc query tool. QAdhoc queries are limited to predefined "views" of |

| | |administrative data that are registered with the QMenu application. Within those views, you have |

| | |limited ability to query, sort and display output. |

| |Keeping Current |

| |Because QMenu and QAdhoc access the Decision Support Database rather than the live production system, it is | | |

| |important to keep appraised of the status of the RPTS database. Data in RPTS is refreshed on a periodic basis | | |

| |and may not be current at the time your query is submitted. Both QMenu and QAdhoc display important online | | |

| |documentation (metadata) describing the selected query or view and indicating the age of data sources used by | | |

| |the query or view. | | |

| | | | |

| |The DSD-L list is the primary medium of communication between QAdhoc developers and its user community. DSD-L | | |

| |list members receive proactive notification of RPTS database status, including changes and additions to QMenu | | |

| |queries and QAdhoc views. Subscribe to DSD-L so that you too can keep current. | | |

| | | | |

| |System Requirements | | |

| |(or “What you need to run QADHOC”) | | |

| | |You will need the following in order to access and use QAdhoc: Internet browser software such as |

| | |Netscape Navigator (version 3.01 or greater) or another Web browser that supports HTML frames and |

| | |Javascript to the same degree as Netscape Navigator version 3.01, is required to access QMenu. The |

| | |number of browser windows that may be open concurrently and the size of the HTML documents displayable |

| | |by the browser are limited by the amount of memory available to the browser software—more memory is |

| | |better. |

| | | |

| | |QAdhoc uses the Internet browser’s network connection when submitting query requests; therefore the |

| | |computer must be Internet-connected. In addition, users must have a University of Alaska RPTS database|

| | |account and QAdhoc web access code. |

| |Secure Access |

| | |To access QAdhoc, login to QMenu. This requires a Userid and Webcode. Userid is an Oracle RPTS |

| | |database account id. Webcode is a special code used only during QMenu login; it is not a RPTS |

| | |password. Request access through your University of Alaska Administrative Account Maintenance |

| | |Coordinator. |

| |Offering Suggestions |

| | |A SUGGESTIONS BOX button is available on the request page that follows a successful logon. You are |

| | |encouraged to click on this button whenever you wish to send suggestions for changes or improvements to|

| | |QMenu or QAdhoc. |

| | | |

| | | |

| | |

|Logging “ON” to QAdhoc | |

| | | |

| | |Since QAdhoc is a component of the DSD Query Menu (QMenu), you must login to QMenu in order to use |

| | |QAdhoc. Start your Internet browser and load the following URL: |

| | | |

|Shortcut: | | |

|To add QMenu to your personal | |[pic] |

|toolbar: | | |

|Save QMenu URL as a bookmark. | | |

|Select Bookmarks from the | | |

|Communicator pulldown list. | | |

|Select Edit bookmarks. | | |

|Click and drag the QMenu URL | | |

|to your personal toolbar | | |

|folder. | | |

|Select close from the file | | |

|pulldown menu. | | |

|Click the DSD Query Menu(RPTS)| | |

|icon on your toolbar. | | |

| | | |

| | |1. Click in the Userid field located in the login frame. |

| | |2. Enter your RPTS database user ID. |

| | |3. Press TAB to move to the Webcode field. |

| | |4. Enter your QMenu Webcode (not your RPTS database password). |

| | |5. Click the LOGIN button. |

| | | |

| | |Important Note |

| | |Initial QMenu Webcode is the letter ‘S’ followed by the 9 digits of your SSN (e.g. S123456789). |

| | | |

| | |QMenu responds with its standard display featuring pick boxes for “Categories” of QMenu reports, a |

| | |“Queries” pick box to display the Queries associated with each of the QMenu categories, and a line |

| | |specifying when the current QMenu session will time out. |

| | |[pic] |

| | |

| |QMenu/QAdhoc “Tickets” |

| | | |

| | |The QMenu/QAdhoc web interface to the Decision Support Database (DSD, RPTS) incorporates the use of |

| | |session “tickets”. When you login to QMenu, you will be granted a session “ticket,” which is valid for|

| | |one hour. When your “ticket” expires, you will be required to reconnect before you can continue with |

| | |your query activity. This feature of QMenu security is intended to help insure that only authorized |

| | |users are accessing University of Alaska data via the web. |

| | |

| |How To Handle Expired Tickets During Active Query Session |

| | |If your ticket “expires” after you’ve clicked the FETCH MORE RECORDS button from the output window of |

| | |active query, locate the main window for QMenu/QAdhoc and click the RECONNECT button. After logging in,|

| | |return the output window of your active query and click the BACK button on the tool bar. Once the |

| | |output from your last successful query is displayed, click the FETCH MORE RECORDS button. Although the |

| | |main QMenu window no longer displays your query form and input parameters, the output window from your |

| | |active query will still be able to FETCH MORE RECORDS. |

| | |

| |Starting QAdhoc |

| | | |

| | |The QMenu selection frame includes a QAdhoc button that activates QMenu’s ad hoc component. |

| | | |

| | |Click the QAdhoc button to load QAdhoc. |

| | |[pic] |

| | | |

| | | |

| | |When QAdhoc is loaded, the QMenu icon is replaced with a QAdhoc icon and the Queries selection list |

| | |becomes a Views selection list. These changes are visual indications that you are now in the ad hoc |

| | |component of QMenu. Notice that the QAdhoc button has been replaced with a QMenu button, allowing you to|

| | |toggle between QAdhoc and QMenu. |

| | | |

| |

|Logging “OFF” |

| | | |

| | |To terminate QAdhoc yet keep your Internet browser session open, click the RECONNECT button. |

| | | |

| | |[pic] |

| | |

| | | |

| | | |

| | | |

| | | |

| | | |

| | | |

| |Suggestions | | |

| | | | |

| |Note the SUGGESTIONS BOX button. You are encouraged to click on this button whenever you wish to send | | |

| |suggestions for changes or improvements to QMenu or QAdhoc. | | |

| | | | |

| | |

|Resetting Your Webcode | |

| | | |

| | |Reset your Webcode after logging into QMenu for the first time. QMenu Webcode should not be set to be |

| | |identical to your Banner production password. |

| | |[pic] |

| | | |

|Important Note: | |Click the WEBCODE RESET button located in the top frame next to the logout button. |

|Case is critical when keying | |Complete the “Webcode Reset Parameter Form,” supplying the following: |

|in a new web code and | | |

|confirming the new web code | |User ID for RPTS |

|you just keyed in. It is | |Old QMenu Web Code |

|essential that you type the | |New QMenu Web Code |

|new web code and the | |Confirmation of New QMenu Web Code |

|confirmation exactly the same | | |

|way. | |Click the WEBCODE RESET button in the parameter form. The following Java alert message is displayed: |

| | | |

| | | |

| | |[pic] |

| | | |

| | |Click OK or CANCEL to continue or cancel this request. |

| | |

|Selecting a View Category | |

| | | |

| | |The QADHOC interface consists of a two-section (frame) window. The top frame of QADHOC is used for |

| | |query selection. Changing the query “category” causes QADHOC to repaint the window with refreshed |

| | |category and query lists. |

| | | |

| | |Click in the Categories picklist and highlight an entry to select a query category. |

| | |[pic] |

| | | |

| | |

|Selecting a View | |

| | | |

| | |A view is a logical representation of one or more columns from one or more data tables. By querying a |

| | |view, you are not required to know which of many data tables contain your desired data elements. Also |

| | |you do not need to understand the complex relationships between multiple data sources. |

| | | |

| | |Click in the Views picklist and highlight an entry to select a view. |

| | | |

| | |[pic] |

| |

|Reviewing Online Documentation or “Metadata” |

| | | |

| | |The bottom frame of the QAdhoc window displays view “metadata” and parameter forms. Changing your view|

| | |selection causes QAdhoc to display metadata for the newly selected view. The resulting display |

| | |includes the names and ages of the data sources used in the view and other information about the view. |

| | |The metadata display for a selected view contains valuable information. Do not ignore this screen! |

| | |[pic] |

| | | |

| | |

| |User Authorized |

| | |The User Authorized section of the metadata indicates whether you can successfully execute a query |

| | |against the view to retrieve data. Although policy allows us to display metadata to any data warehouse |

| | |user, access to the information delivered by a query is subject to the "need to know" test. If you are |

| | |authorized to retrieve data via the selected view, a button labeled "Get Adhoc Form" is displayed. If |

| | |you are not authorized, the words "ACCESS DENIED: see ‘Associated Roles’ below." are returned. |

| | |

| |Object Name, Type and Associated Roles |

| | |Displays the actual name of the query (which may not be the same as the title displayed in the |

| | |parameter form). States that the object is a “view.” Lists associated Oracle security role(s) that |

| | |constrain who may query the view. |

| |Data Sources |

| | | |

| | |The list of data sources provides important information about the age of data, which will be used to |

| | |generate the query output. In a data warehouse (or data store) the information is most likely not as |

| | |current as the production system. Just how old that data is should play a role in how much you depend |

| | |on the output from the query. |

| | | |

| | |

| |Fields Requiring Conditions |

|Note: | | |

|If required selection criteria| |Lists the names of fields in the view for which you must provide selection criteria. By requiring |

|are missing, QADHOC will | |selection criteria, QAdhoc helps insure that your queries use underlying table indexes and so run |

|display this Java Alert | |reasonably efficiently. |

|message: Form contains fields| | |

|requiring conditions. | | |

|. | | |

| | |

| |Description |

| | |Displays a brief description and statement of purpose for the view. As the descriptive text is |

| | |improved over time, this section of the metadata may contain information about the types of data found |

| | |in various fields of the view. |

| | |[pic] |

| | | |

| | |A vertical scroll bar is displayed in the lower frame of the QAdhoc interface, if metadata can not be |

| | |fully displayed. Click within the scroll bar to reposition the metadata display. |

| |

|Retrieving Query Parameter Form |

| | | |

| | |Before a query may be submitted, the associated query parameter form must be retrieved and completed. |

| | |Parameter forms allow limited query customization so that only records meeting query criteria are |

| | |displayed in the query output. |

| | | |

| | |If you are authorized to query the view, click the GET ADHOC FORM button in the metadata display to |

| | |retrieve the query parameter form. |

| | |[pic] |

| | |

|Building a Simple Query | |

| | | |

| | |QAdhoc parameter forms allow you to customize a report from the information available in the view. The|

| | |parameter form controls which fields will be displayed, what criteria are used to select records, and |

| | |what fields are used to sort the output. |

| | |[pic] |

| | | |

| | |This parameter form contains two useful links. One is QAdhoc Help, which brings up details on using |

| | |QAdhoc. The other, datasource, allows you to see the table and column (field) descriptions associated |

| | |with the view. This is a powerful new addition, although all views do not yet have table and column |

| | |comments attached. |

| | | |

| | |A PREVIEW BATCHES OF radio button allows you to change the maximum number of rows (or records) that |

| | |each resulting web page contains. The default is 150 rows, if you choose not to use this option. |

| | |WARNING: If you enter a large number, such as 2000, and your web browser does not have enough memory |

| | |to contain the resulting Web page, your application may terminate. |

| | | |

| | |An EXTRACT ALL RECORDS radio button allows you to export the query results to a file. This will export |

| | |to a file all records that match the conditions you enter. |

| | |

|How to tips | |

| | |Extract Results to a File |

| | | |

| | |An EXTRACT ALL RECORDS radio button allows you to export the query results to a file. This will|

| | |export to a file all records that match the conditions you enter. This is used in conjunction |

| | |with Excel. You may need to change your preferences to take advantage of the ability to |

| | |retrieve all records. Your browser needs to associate the MIME type text/csv correctly with a |

| | |helper application in Excel. |

| | | |

| | |Follow these steps to set up your Web browser to save exported data as an Excel file. |

| | | |

| | |Go to the Edit menu in Netscape. |

| | |Select preferences |

| | |Expand the Navigator category, and |

| | |Choose the Application tab. |

| | |In the Description list, select Microsoft Excel Separated Values File. |

| | |If anything is in the MIME type block other than “CSV”, click on edit and remove any extension |

| | |listed. |

| | |Exit the dialog box by clicking “OK”. |

| | |1st time running the Export, you will get the Unknown File Type window which will give you the |

| | |four options: “More Info”, “Pick App”, “Save File”, and “Cancel”; select “Pick App”. |

| | |When the Configure External Viewer window comes back up select “Browse” |

| | |Find the executable file for Microsoft Excel and double click on the file. This will bring you |

|Note: | |back to the main Configure External Viewer Screen. You should see the path to the Excel |

|If after changing MIME type | |executable file. |

|as in Step 5, you still are | |Click on “OK” to save your set up. |

|unable to export your file | |You will get the “Warning. There is a possible Security Hazard here.” screen. Click in the |

|successfully into Excel | |“Open it” screen to export your information into Excel. |

|please contact us via e-mail| |Use Radio Buttons for HEADERS |

|at sxtrain@alaska.edu. | |The FIELD NAME HEADERS Radio Button allows you to select what the column headings are in your |

| | |resulting report. This is the default it will return the column heading as listed under the |

| | |FIELD section of the Parameter Form. |

| | |The SELECT SEQUENCE HEADERS Radio Button allows you to show the column headings in your resulting | |

| | |report as a numbered field; the number corresponds to the order in which you selected the fields for | |

| | |display. | |

| | | | |

| | |The NO HEADERS Radio Button allows you to choose not to have column headings on your resulting report. | |

| | |Use RETRIEVE ONLY DISTINCT ROWS Checkbox | |

| | |The RETRIEVE ONLY DISTINCT ROWS checkbox allows you to remove all duplicates from your report. | |

| | |Use Other Features | |

| | |The RESET FORM button clears the form, returning it to its original state. | |

| | |The SUBMIT button submits the query for execution after the user's input has been validated. | |

| | | | |

| | |The next section of the Parameter Form is where you build your question. You create your own report by | |

| | |entering information into the SELECTION CRITERIA for the specific FIELDS you wish to query on, and | |

| | |choosing the information to DISPLAY on your report and the way you want it SORTED. | |

| | |The FIELD column lists the available fields to use in your report. | |

| | | | |

| | |SELECTION CRITERIA: Made up of “operator” picklists and “value” input fields. The combination of field| |

| | |name, operator and value are used to build selection criteria that limit records returned by your | |

| | |query. | |

| | | | |

| | |Operators like =, >, ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download