Return to IS Home Page.
 
...      ...  ...

 

 

Managing Queries

Overview

Managing Queries

Distribution

UVA ODS Specialist

Ownership

The Director of the Business Support Center is responsible for ensuring that this document is necessary, reflects actual practice, and supports University policy.

Section Objectives

At the end of this section, you should be able to:

Building and Executing a Query

When you connect to the database, you communicate through the End User Layer, which dispatches your query to the database for execution. The following steps describe this process:

1. When you select business areas, folders, and items, Discoverer reads their definitions from the End User Layer and creates an SQL statement on the client.

2.      When you execute the query to retrieve the new data:

3.      When you modify the results in a worksheet:

Caching Data

Discoverer stores the results of your query in a client-side cubic cache known as the ResultsBase. This enables rapid retrieval without the need to requery the database.

Retrieving Additional Data: When you attempt to retrieve data that the cache does not store, the following steps take place:

Summary Table Redirection

Summary tables hold pre-aggregated and pre-joined data. In other words, they hold the results of frequently run queries that often require long aggregations. Running a query against summary tables is faster than running a query against the database tables. Therefore, when you issue a (potentially long-running) query against detail data, a summary recognition algorithm determines first whether summary tables could satisfy the query request. If the summary tables cannot meet any of the conditions, the algorithm redirects the query to the database tables. Summary tables should be defined by the administrator. Thus, it is important that the end user and the administrator agree on what type of data the summary tables should contain and how often they are refreshed.


Governing Queries

You can govern actions if query execution exceeds a predefined limit or if the data is old. To set the options for running queries, select Tools—>Options from the menu bar.

Query Summarization: The data administrator is responsible for creating and refreshing summarized tables. These tables contain summarized items that users request frequently. Running a query against summary tables is faster than running a query against the database tables. You can set the query summarization parameter to avoid running a query against obsolete data. The summarization parameter controls whether or not summary redirection occurs. Discoverer offers the option of running a query against summary tables when these are available.

Query Governor: As previously discussed, you can cancel a query if you decide that you do not want the results requested or if the query is retrieving more data than you want. Discoverer has a query-governing option that predicts the time needed to run a query. This tool enables you to select among the following options:


Table Row Retrieval: You can set the number of rows retrieved per block of data for optimum performance. See your system administrator to determine the optimal number.

Cancel Value Retrieval: The retrieval of a list of values for a query is canceled if the retrieval time exceeds a preset amount of time.


Setting Default Properties

If you prefer certain layout formats, such as horizontal guidelines or outline crosstab style, you can select default properties for all of your table and crosstab layouts. To display the properties for either layout, select Tools—>Options. If you are currently in a table report, the Options dialog box has a Table tab for you to select. If you are in a crosstab report, the Options dialog box has a Crosstab tab for you to select.

Title: To create a default title, select the Title option in the Show region of the Crosstab or Table tab and then click the Default Title button. When you create a new title by default, the title will not appear in the current workbook, but in the new workbook.

Crosstab Style: Two Style options are available for crosstab layouts: Inline and Outline. You can view a sample of either style by selecting a Style option in the Crosstab dialog box.

Managing Worksheets

Workbooks and Worksheets

Workbooks and worksheets display the results of your queries. A worksheet contains the results of a query. When you modify a query, the same worksheet displays the new data unless you specifically request a new worksheet. A workbook contains one or more worksheets. Worksheets and workbooks provide the following benefits:

Creating Worksheet Titles

You have learned how to create a default title. To create a title for your current worksheet, you can do any of the following:

How to Add a Bitmap to the Title

Renaming a Worksheet

Discoverer provides a default name for each new worksheet. To rename a worksheet, use one of the following methods:

1. Select Sheet—>Rename and enter the new name in the Rename Sheet dialog box.

2. Double-click the tab for the worksheet and enter the new name.

Deleting a Worksheet

Select the worksheet to be deleted and select Sheet—>Delete. You cannot delete a worksheet in a workbook if it is the only worksheet.


Creating a New Worksheet: Click New Worksheet on the toolbar to launch the wizard for a new worksheet in the current workbook.

Duplicating a Worksheet: You can duplicate a worksheet as the same or a different output type. The duplicated worksheet appears as a new worksheet in the workbook. To duplicate a worksheet, follow these steps:

1. Select the worksheet to duplicate.

2. Click the Duplicate as Table or Duplicate as Crosstab button on the toolbar. Alternatively, select Sheet—>Duplicate as Table or select
Sheet—>Duplicate as Crosstab.

The wizard opens in a tabbed format so that you can modify the new layout.

Refreshing a Worksheet: Click the Refresh button on the toolbar to rerun the query for the current worksheet.

Counting Rows in a Sheet: Select Sheet—>Count All Rows to get the number of rows returned by the query for the current sheet.

Managing Workbooks


Saving a Workbook

Select File—>Save to save the workbook to the file system or database with a name of your choice. Select Save As to save it with a different name. Discoverer saves the query and the formatting, but not the results of the query. The files will be saved with the extension .Dis.  User-defined queries are saved on the pc, unless special permission has been obtained to save to the database.  If you have created a query that would benefit a large population of the UVA community, please contact your DSC to begin the application process to save to the database.

Opening Saved Workbooks

1. Select File—>Open. The Open Workbook dialog box is displayed.

2. Click the My Computer or Database icon. The Open Workbook dialog box is displayed.

3. Select a workbook and then click Open.

Note: You can select to open a workbook from the most recently used workbook list. The workbook opens with the last worksheet active when the workbook was saved, and a dialog box prompts you for permission to execute the worksheet or to open the worksheet without executing the query.

4. To modify the query before executing it, click No.

Note: If you open a workbook that belongs to another user, a dialog box is displayed asking you to select one of the following options:

Rearranging Worksheets

Rearranging Worksheets: Select the worksheet tab and drag the sheet to the desired location.

Copying or Moving a Worksheet to a Different Workbook: To copy a worksheet to a different workbook, open the worksheet.  Hold down the [Ctrl] key, and drag the worksheet to the worksheet tab section, until a black triangle appears in the desired workbook.

Note:  You must have both workbooks open and sized so you can see both on your desktop at the same time.

To move a worksheet, simply drag it to the worksheet tab section in the desired workbook.

Modifying File Properties: Select File—> Manage Workbooks—>Properties to display the file properties of the active workbooks. These properties include the owner and location of the saved workbook. To include a description of the workbook, enter it in the Description field and then click OK.

You can select when to run a query after opening a workbook. You have three options:

Discoverer lets you know which database was queried for a particular workbook. When you select this option, a message is displayed with the database account.


Default Formats

To open the Options window, select Tools—>Options. In this window, you can select the format for headings, datasheets, totals, and exceptions. Totals and exceptions are explained in later lessons. You can select a default format from this menu as well as from the wizard’s Options window. The format settings you select will affect the display of all new worksheets, but they will not affect existing worksheets.

Setting More Options

Modifying the Wizard Window

You can disable the wizard graphics. Doing so makes the wizard windows larger so that you can view more information. Disabling wizard graphics is particularly helpful when you log on remotely, because the windows pop up faster.

Multiple-Path Joins

Joins are a basic relational construct for combining information between folders, and they are specified by an expression that uses items in both the folders to be joined. Usually joins are “one to many”: for example, “in each department there are several employees.” Joins are defined by the administrator. You can have more than one join path between two folders. For example, there can be two joins between the Department folder and the Employees folder:

Multiple Join Path Detection: If you disable multiple-path detection, Discoverer will not check for multiple joins and will use all joins defined between the folders. This may result in incorrect data.

If you are an advanced user who understands the underlying database model, you might want to use this feature. A multiple join path occurs when two folders have more than one join relationship defined between them. 

Summary

In this lesson you should have learned how to: