Release 4.1
A86731-01
 Contents |
 Index |
| Discoverer 4i Release 4.1 A86731-01 |
|
Discoverer's default Graphical User Interface settings determine how Discoverer works, looks and feels. Using the Discoverer Options dialog, you can change the default options to suit your preferences and requirements.
Default options apply when you start working with Discoverer. Changes to defaults do not affect previous work. For example, if you use the Options dialog to change the formats for new worksheets, the formatting on previous worksheets is not affected.
NOTE: In addition to opening the Options dialog from the menus, you can also open it by clicking the Options button if available in other dialogs. In that case the options may apply only to the features offered in that dialog.
Click to learn more about:
Setting General Options
Setting Query Governor Options
Setting Sheet Format Options
Setting Default Format Options
Setting Advanced Options
Setting EUL Options
Using SQL
The General options are for opening workbooks and displaying wizard graphics.
Run query automatically--Discoverer automatically retrieves the data specified by the worksheet in the workbook. Select this option to retrieve data for the worksheet as soon as you open a workbook or click on the tab of a worksheet.
Don't run query (leave sheet empty)--Opens the workbook and worksheet, but does not retrieve any data from the database. That is, the worksheet opens but does not contain any data. A typical reason for selecting this option is to see a different worksheet than the one that opens by default without waiting for the query results.
Ask for confirmation--This is the default selection. After the workbook opens, a dialog asks if you want to run the query for the first worksheet.
Show wizard graphics--Several Discoverer dialogs include artistic graphics (bitmaps). Deselect this option if you don't want to see the graphics in the dialogs.
See also:
Setting Query Governor Options
Setting Sheet Format Options
Setting Default Format Options
Setting Advanced Options
Setting EUL Options
Using SQL
The Query Governor options help reduce the amount of time it takes to display data. You can set defaults for Summary Data and for Queries.
Using Summary Data loads data more quickly for the work you do most often. When you request data for a worksheet, Discoverer first checks Summary Tables set up by the Discoverer Administrator to see if their saved data satisfies your request and, if it does, loads the appropriate data quickly. If the Summary Tables' data does not satisfy your request, Discoverer then redirects the request to the detail data.
CAUTION: Do not use Summary Tables if you normally work with the most current data in the database. The saved data in the Summary Tables remains constant until updated with new data. Summary Tables should be updated periodically to incorporate new data.
The Discoverer Administrator creates Summary Tables based on the type of data and the type of queries you use most often. Normally, Summary Tables are for the queries that take a long time to aggregate and display data. Other queries that require less time to aggregate and join data might not need Summary Tables. See your Discoverer Administrator if you want to use Summary Tables.
The Query Governor options help you set limits on the amount of time a query should take to complete. Use these options to limit the time you wait for Discoverer to run a query. The options set time and size limits on data as it is being retrieved from the database.
Note: Your Discoverer Administrator determines the upper limits for the Query Governor options. For example, your Discoverer Administrator may determine that queries cannot run longer than 30 minutes. If you set the limit for 60 minutes, the numbers will change back to 30 automatically.
Always, when available--Select this option if time-sensitive data is not important to data analysis. Discoverer retrieves and displays saved data from the Summary Tables regardless of whether the data is current.
When summary data is more recent than--Select this option when time-sensitive data is necessary for worksheets. Click the up and down arrows to specify the number of days from the last data update. For example, if you're analyzing monthly data using Summary Tables, you want to make sure the data is less than thirty days old. If the Summary Tables' data has not been updated within the specified time, Discoverer does not use the Summary Tables to fulfill your query request. Instead Discoverer redirects your query request to the detail data and uses the latest data for the worksheet.
NOTE: Summary Data options can vary according to which Oracle database version you are using. If you are connected to an Oracle 8.1.6 database or later, these options relate to Materialized Views. See your Discoverer Administrator for more details.
Warn me if predicted query time exceeds--When requesting data for a worksheet, Discoverer estimates the time required to complete the query. Select this option if you want a message to warn that the query will take a long time to complete. The message appears only if the estimated completion time exceeds the period you specify, in MM:SS format, for this option.
Prevent queries from running longer than--Select this option to limit the time a query runs before it is cancelled. A warning message informs you if the query exceeds the set time, then Discoverer cancels the query. Normally, this option is selected if server performance is an issue because long running queries might affect server performance.
Limit retrieved query data to--This option sets the maximum number of rows to retrieve for a query. If the query returns more rows than the value you set here, a message informs you that not all data is retrieved and, consequently, the displayed data might not be complete.
Retrieve data incrementally in groups of--Set this option when the database contains large tables with many rows that might take a long time to retrieve. With this option selected, Discoverer retrieves rows of data in increments rather than all at the same time. The smaller the number of rows to retrieve as a group, the faster the initial retrieval. The default size of the data group is 250 rows, which equates to the first 10 pages of data at 25 rows per page. You can set the number of rows per page using the Rows per Page option on the Sheet Format tab.
Cancel list-of-values retrieval after--Some dialogs have a convenient drop-down list from which you can select a value for an option instead of manually typing the value. This is called a list of values. For example, when creating a condition for analyzing monthly sales data, you could either choose the value, July, from a list of months, or manually type the name "July" as part of the condition statement. But some large lists of values take a long time to retrieve from the database, such as a list of 20,000 part numbers. If you don't want to wait for Discoverer to retrieve these larger lists of values, click the up and down arrows to set the maximum amount of time you would wait for this list to appear. This option does not cancel Discoverer's retrieval of the actual data for a query. In the case of the part numbers, for example, all the data about the parts in stock, price per part, sales figures and so on, is displayed in the appropriate tables. Only the drop-down list of part numbers in various dialogs would not be available.
See also:
Setting General Options
Setting Sheet Format Options
Setting Default Format Options
Setting Advanced Options
Setting EUL Options
Using SQL
This tab in the Options dialog is for setting the display format of the table or crosstab.
NOTE: Changes to these settings affect the current sheet as well as any new sheets created later.
Title--Displays a title if one was created earlier.
Horizontal and Vertical Gridlines--Lines that separate rows and columns. The display example on the dialog shows a representation of your choices.
Null values as--A cell that contains a null value does not contain any data. Select the text to use to designate a null value from the drop-down list or type a value in the box.
CAUTION: If you select the 0 (zero) symbol as the null value, it may appear to the person looking at the table or crosstab that zero is the actual data. For example, in a cell specifying amount owed, zero means nothing is owed, whereas a null value in the same cell might mean that no information is available about the amount owed. Therefore, using the zero symbol to indicate null values might be misleading to others unless you explicitly state that 0 is equivalent to no data.
Values that cannot be aggregated as--Numbers that cannot be aggregated are formatted one of the values in the pull down list.
Row Numbers (Table only)--Sequential numbers of each row in the table, shown on the left side of the table.
Inline/Outline (Crosstabs only)--Arrangement of the side axis data items. As you select one of the options the example icon represents the arrangement.
Rows per screen page--The number of data rows on each page of the worksheet. Click the up and down arrow buttons to select the number.
See also:
Setting Query Governor Options
Setting General Options
Setting Default Format Options
Setting Advanced Options
Setting EUL Options
Using SQL
The Default Format options are for setting the font style, text color and background color of a worksheet's data, column headings, and totals.
Size--Choose a size for the font from the drop-down list.
Style--Click a button to display the text in a boldface, italic, underline, or strikethrough. Click the appropriate button to remove the style if it is already in effect.
Color--Click the button to apply a color to either the text or the background. A palette of colors appears. Click the one you want.
Alignment--Click an alignment option. The options display the data in the top, middle or bottom of the appropriate cell on the table or crosstab.
Actual font size--Select this option to display the data in the sample in the font size that you choose from the size drop-down list.
See also:
Setting Query Governor Options
Setting Sheet Format Options
Setting General Options
Setting Advanced Options
Setting EUL Options
Using SQL
Sometimes, after changing several aspects of a format you want to change it back to the original Discoverer default settings. Instead of changing each format setting individually, you can click the Reset button.
Resetting applies only to the selected format. Thus, you can reset one format but keep your changes made to the others.
The Advanced options are for turning on/off automatic querying and for catching join errors that relate to database relationships.
CAUTION: Do not change these options without assistance from the Discoverer Administrator.
Automatic Querying--When you make a change to a worksheet that affects the data results (as opposed to formatting changes), Discoverer automatically re-queries the database to display the appropriate results based on your changes. However, you can use this option to disable the automatic query feature in case you want to make changes to the worksheet but not have Discoverer update the data. See "About Automatic Querying" for more details.
Fan-Trap Detection--When this check box is NOT selected, Discoverer automatically detects and resolves fan trap and chasm trap queries into multiple SQL statements to obtain normal expected results. If you disable Fan Trap detection, this may result in these queries generating cartesian products, with potentially misleading results.
Select this check box if you want to prevent Discoverer from checking for fan traps. We recommend that you DO NOT select this check box unless advised to do so by your Discoverer Administrator. See "About Fan-Traps" for more details.
Multiple Join Detection--Check this option to turn off Discoverer's automatic detection and prevention of worksheet arrangements that have potential multiple join paths. See "About Multiple Join Paths" for more details.
While working with a worksheet, you can make changes that affect the data being displayed. For example, if you add a new data item or change a calculation that produces a data column, the displayed data may not reflect the change until Discoverer re-queries the database. With automatic querying, Discoverer automatically re-queries the database to get the updated data to display. In some cases, however, you may not want Discoverer to automatically re-query the database. For example, if you intend to make several changes that affect the data, then you don't want Discoverer to re-query the database until you're finished with the changes.
Using the Advanced Options dialog you can turn on and off the automatic re-querying feature. If it is turned off and you want Discoverer to re-query the database, choose Sheet | Refresh Sheet from the menu.
A fan-trap occurs when the data items in two folders are not directly related (such as with a Customer ID), but do have a relationship based on the data items in a third folder.
As illustrated in Figure 5-1, the database contains three folders--Departments, Employees, and Locations. Each employee is associated with a single department because each employee works in only one department. The departments, however, are associated with multiple locations because departments can have offices in different cities. Consequently, because of the mutual association of employees and locations with the Departments folder, employees become unintentionally associated with multiple locations. This, of course, is incorrect because employees can only be in one location. A query to count the number of employees at each location and department, for example, produces an incorrect result. The same employee is counted at multiple locations because the departments are at multiple locations. In the example below, the real number of employees is four, but the query produces a count of eight employees--Clark, Miller, and Scott are counted for both London and Tokyo, and King is counted for both San Francisco and Amsterdam.
As you're creating a new worksheet, Discoverer automatically detects and warns if the data items selected for the worksheet can possibly lead to a fan-trap. Note that Discoverer merely warns of a potential for a fan-trap and does not automatically prevent a fan-trap situation from occurring. Sometimes a Discoverer Administrator intentionally associates folders in a way that looks like a fan-trap. For example, the association of a single employee to multiple locations can be useful for counting the number of employees who might travel to a location.
Normally, however, fan-traps are not intentional in a database. If Discoverer warns of a fan-trap, please contact your Discoverer Administrator who can determine if the fan-trap association is intentional or if the database's organization needs to be modified.
Discoverer automatically detects fan-traps only if the Disable Fan-Trap Detection option is deselected in the Options dialog. If the option is not selected, Discoverer builds new worksheets without checking for, or warning about, potential fan-traps.
When you create new worksheets, the data items in the worksheets are often stored in multiple folders in the database. Discoverer checks to make sure that these multiple folders have a clear, unambiguous relationship between them, and therefore, that the relationships among the data items is also clear and unambiguous.
For example, suppose a database contains two folders--one for information about sales orders and another for information about customers. Both folders contain the data item "Customer ID" because each sales order is for a customer specified by the Customer ID, and each customer is identified in the Customer folder by an ID number. In this case, if you run a query about sales order details and also want to see customer details, such as first and last name, Discoverer can clearly determine which customers are associated with each sale by relating the Customer ID to the sales details and customer details.
However, some databases organize information so the relationships between items in different folders is ambiguous. This means that data items can be associated with each other in multiple ways, which is a situation known as a "multiple join path." As you are creating new worksheets, Discoverer can automatically detect and warn you if the potential for multiple join paths exists because, if it does, Discoverer might associate the items in a way you did not expect or intend. Thus, when you query the database, the results might not be what you intend either.
The warning that a multiple join path situation exists is not an error message; the warning merely advises you that the database contains relationships among data items that you might not know exist. If Discoverer detects and warns you of a multiple join path situation, please contact your Discoverer Administrator who can determine if the database's organization needs to be modified.
Discoverer automatically detects multiple join paths only if the Disable Multiple Join Path Detection option is deselected on the Options dialog. If that option is not selected, Discoverer builds your new worksheets without checking for or warning you about multiple join paths.
See also:
Setting Query Governor Options
Setting Sheet Format Options
Setting Default Format Options
Setting General Options
Setting EUL Options
Using SQL
One of the Discoverer Administrator's jobs is the creation of an End User Layer (EUL) that contains the data you need for work. Sometimes, the Discoverer Administrator designs several EULs that you can access. Use the EUL Options dialog to select the default EUL for Discoverer to use when you connect to the database.
You will need to reconnect to Discoverer to use the new EUL.
See also:
Setting Query Governor Options
Setting Sheet Format Options
Setting Default Format Options
Setting Advanced Options
Setting General Options
Using SQL
If you are familiar with SQL, you can analyze the SQL statements that Discoverer executes against the database.
To see a worksheet's SQL statements:
The SQL Inspector dialog box appears. It shows the SQL statements used to create your current worksheet.
The SQL statements Discoverer uses to open a workbook or worksheet involve complex programming. Therefore, you cannot simply copy a worksheet's SQL and use it to open another workbook or worksheet.
The Plan tab displays the Execution Plan chosen by the Oracle Server for the query request. The Execution Plan defines the sequence of operations that the Oracle Server performs to execute the SQL statement.
You can look at an Execution Plan to see how a SQL statement is being executed. For example, when using Summaries, you may wish to check that a query is using a Summary or Materialized View created by your Discoverer Administrator.
A Summary Folder is how Discoverer represents an underlying Summary table or Materialized view.
Summaries are created by your Discoverer Administrator to improve the performance of Discoverer, to help do your work more quickly and efficiently.
Both Summary tables and Materialized Views precompute and store aggregated data.
|
NOTE: For more information on Summaries and Materialized Views, see Oracle8i Data Warehousing Guide Release 2 (8.1.6) (Part Number A76994-01). |
A Summary Folder contains one or more Items (i.e.columns from a Summary table or Materialized View). You can combine items in a Summary folder into Summary Combinations. From a Summary Combination, Discoverer creates one of the following depending on the version of the database:
To see a worksheet's Execution Plan:
When running Discoverer against an Oracle 8.1.6+ database, the server controls query redirection by rewriting the SQL to use a Materialized View. If a server rewrite occurs, the server Execution Plan indicates the Materialized View name.
You can use the Execution Plan tab in the SQL Inspector dialog to see the SQL statement that Discoverer sends to the server.
In the screenshot above, the worksheet contains the Items: City and Profit SUM. Although the Discoverer Administrator has created a Summary for these items, the SQL statement displayed in the SQL Inspector SQL tab does not indicate that a Summary, (in this case a Materialized View) is being used.
In the screenshot above, you can see from the SQL Inspector Plan tab that a Materialized View Summary is being used by the database, identified by the table name EUL4_MV101264.
NOTE: Materialized View names are prefixed with MV.
|
 Copyright © 2000 Oracle Corporation. All Rights Reserved. |
|
