Oracle Discoverer Plus User's Guide
Release 4.1

A86732-01

Library

Contents

Index

Prev Next

8
Advanced Discoverer Plus Features

Discoverer Plus includes several advanced features for working with data. This chapter describes those features and explains how to use them.

The advanced features are:

8.1 Retrieving All Rows and Counting the Number of Rows

Rows for tables are fetched from the database incrementally in groups. The number of rows in each group is specified in the Options dialog box based on the value set in the option "Retrieve data incrementally in groups of." Click the Query Governor tab on the Options dialog box to see that option.

To override that setting you can retrieve all the rows at once instead of incrementally. Retrieving all rows applies only to tabular style reports.

Figure 8-1 Number of Rows


The message shows the total number of rows that will be returned by the query. The number of rows may be greater than the actual number of rows currently displayed in the worksheet.

8.1.1 Refreshing the Worksheet

Refreshing a worksheet re-queries the database and displays the worksheet's data based on any new data. A primary use of refreshing a worksheet is when you're using Discoverer in conjunction with real-time data. For example, if the Discoverer worksheets query a database used for on-line transaction processing, you need to refresh the worksheet periodically to update the worksheet data with the latest transaction results.

To refresh a worksheet, choose Sheet | Refresh Sheet. Discoverer displays the worksheet results based on the updated data.

8.2 Creating Parameters

Parameters are placeholders used instead of specific values in the definition of a condition. Unlike regular conditions that find the same data each time they are applied, parameters offer choices at the time the data loads.

You can create Parameters at two levels:

    1. Workbook level - Here, the Parameter applies to all worksheets in your workbook. Changes to the Parameter in any worksheet cascade to all worksheets in the workbook.

    2. Worksheet level - Here, the Parameter applies to the current worksheet only.

For example, suppose one of your routine analysis jobs is to compare sales performance by evaluating sales figures for various products from different groups of cities. By creating two parameters--one for products and the other for cities--you can select the specific grouping of data for the analysis at the time the worksheet loads.

Parameters are particularly helpful if several people use the same workbook or worksheet. Each person can select the parameters that load only the data of interest on the worksheet.

Parameter selections appear when loading a workbook or worksheet. Here's an example that shows a parameter for limiting the available data to a specific year, (2000).

Figure 8-2 Sample Parameters for Video Sales


Key to Figure 8-2.

    1. This dialog box appears when loading a workbook or worksheet.

    2. Enter a Parameter in the text box, or select a value from the pull-down list by clicking on the down-arrow to the right of the text box and select a value.

    3. Choose Finish to load the Worksheet for the selected values.

    4. The Worksheet displays only data matching the value entered for the specified criteria.

To create a parameter:

  1. Display the worksheet to which you want to apply the parameter.

  2. Choose Tools | Parameters.

    The Parameters dialog box opens. It shows the parameters already created.

Figure 8-3 Parameters Dialog Box


  1. Click New.

    The New Parameter dialog box appears.

    Note: You can also access this dialog box when creating advanced conditions. Select New Parameter from Value(s) drop-down list on the New Conditions dialog box.

Figure 8-4 New Parameter Dialog Box


This is where you define new parameters.

For Item--select the item for the parameter from the drop-down list. The list shows the items currently available to the worksheet.

Name--the name of the Parameter, which appears on the list of available Parameters dialog.

Create Condition with Operator--creates a condition with an operator. You can select the operator from the drop-down list. For example, select equals (=) to create a condition with the formula "For Item" = "Parameter's Name".

Prompt--this text appears on the dialog box that opens prior to loading the worksheet; enter text that tells the person what to select.

Description--this text also appears on the dialog box that opens prior to loading the worksheet; it explains the parameter.

Let User Enter Multiple Values--select this option if you want the person using the worksheet to be able to select multiple values for the parameter when loading the worksheet. If this option is not selected, the person can choose only one value for the parameter.

Default Value--the pre-selected value for the parameter. Click the drop-down arrow and select a value from the list, or type the default value directly into the box.

What is the value of this parameter if it is used in more than one sheet?--allows you to create the Parameter either at Workbook level or Worksheet level.
Click 'Allow only one value for all Sheets' to make the parameter value cascade across all worksheets in the workbook. Click 'Allow a different value in each Sheet' to make the parameter value apply to the current worksheet only.

  • Click OK. The new parameter now appears on the Parameters dialog box.

    Moving the parameters up and down on the Parameters dialog box changes their position on the dialog box that appears when loading a worksheet. To add a picture to that dialog box, click Bitmap and choose Set Bitmap.

  • Click OK on the Parameters dialog box. Those parameters are now in force for the next time the worksheet is loaded.

    Parameters are activated when used in an active condition. If you check the option "Create condition with operator" in the New Parameter dialog box, a new condition is created and activated, therefore the parameters are also activated.

    To deactivate a parameter, deactivate the condition. Deleting the condition or deleting the parameter also deactivates the parameter.

    To edit parameter values:
    Here you display the Parameter dialog so that you can choose a different value to display on your worksheet(s).

    1. Choose Sheet | Edit Parameter Values.

      The Parameters dialog box appears.

    2. Select the new value(s) for the Parameter.

    3. Click OK.

    The data on the worksheet is revised to meet the Parameter conditions.

    NOTE: If you want to change the Parameter value for your worksheet, you can also use the Refresh option to display the Parameter dialog for active Parameters.

    8.2.1 Loading Multiple Values

    If the option Let User Enter Multiple Values is selected for a worksheet's parameters, the person opening the worksheet can select multiple values for the parameter.

    Here's an example:

    Figure 8-5 Choosing Multiple Parameters


    Key to Figure 8-5.

      1. When you can load multiple values, the drop down list includes the option to Select Multiple Values.

      2. The Values dialog box displays a list of values that can be selected. Selected values are marked with a selected check box. Click Select All to select all values in the list.

    8.3 Creating Calculations

    Discoverer Calculations are used to analyze the data in your worksheets. Discoverer provides a comprehensive range of pre-defined functions for use in your Calculations.

    Simple Calculations based on the data in a worksheet can produce typical business answers, such as sales commissions per salesperson, royalty fees paid to a supplier, and so on. Complex Calculations can find the answers to more complicated questions including "what if" scenarios.

    In other words, instead of merely viewing your data to find trends and answers, you can use Calculations to rigorously analyze the data using mathematical techniques.

    The results of Calculations are displayed as new columns on a worksheet, or the Calculations can be part of other Calculations.

    You can also pivot Calculation Items to the page axis, just like other Items.


    NOTE: To see examples of how you can use Calculations to analyze your Discoverer data, refer to Appendix , "This appendix contains the following sections:"


    Here is a sample of the results of a simple Calculation on a table:

    Figure 8-6 Sample Calculation


    Key to Figure 8-6.

      1. This is the dialog box that you use to define Calculations. The Calculation box is where you create the formula for the Calculation.

      2. After creating a Calculation, a new Item column is displayed on your Worksheet showing the calculated value. In this example, it shows the Profit SUM increased by 20%, (Profit SUM * 1.2).

    Calculations are based on items, not on individual data points. For example, you can multiply Profit SUM (an item) by a percentage to find a result for each product.

    However, you cannot, for example, subtract Year 2000 profits from Year 1999 profits because 2000 and 1999 are data points in the item, Year. To calculate results based on individual data points, use Discoverer's Analytic Functions LAG and LEAD, (see Appendix A.5.8, "Lag/Lead Function Examples").

    To create a Calculation:

    1. Display the worksheet on which you want to apply the Calculation.

    2. Choose Tools | Calculations.

    The Calculations dialog box appears.

    Figure 8-7 Calculations Dialog Box


    Figure 8-8 Edit Calculation Dialog Box


    Key to Figure 8-8.

      1. Calculation Box.

      2. Function Categories - you can build calculations using a pre-defined set of functions arranged into the categories.

      3. You can use these operators to build calculations.

      To help you create the Calculation formula with a minimum of typing, you can:

      • Paste expressions from the box on the left to the box on the right.

      • Add operators with the operator buttons.

      • Type the new Calculation directly into the Calculation box.

      • Mix typing with pasting and clicking the operators.

    1. Click the Show button to see the different expressions.

      Items--lists the items available to the worksheet. This is helpful because you don't have to remember the name of an item in order to include it in a formula. Parameters are also listed, and you can use the parameters in a formula as well.

      Functions--lists a wide range of functions that you can apply to your formula.

    Figure 8-9 Calculations in the Edit Calculation Dialog Box


    Key to Figure 8-9.

      1. Select an Item then click Paste to copy the Item into the Calculation box.

      2. Click the Functions button to show a list of function folders. To display a list of functions, click the plus symbol (+) next to each function folder. Use the Paste button to copy the selected Function into the Calculation box.

    1. Click OK and then on the Calculation dialog box, click OK.

      The Calculation is applied to the worksheet and results appear in a new column.


      Calculations use Oracle's standard syntax. See the Oracle SQL Reference Language Reference Manual for a complete description of the syntax. 


    8.4 Creating Advanced Conditions

    The advanced condition option allows you to build complex conditions for filtering your worksheet data.

    To create an advanced condition:

    1. On the Edit Condition dialog box, click the Advanced button.

      The dialog box expands to show buttons to add and delete lines to the condition, and to include the Boolean operators (AND, OR, NOT).

    Figure 8-10 New Condition Dialog Box


    The Item drop-down list shows the items currently available to the worksheet.

    Other options from the list are:

    Create Calculation--click to open the New Calculation dialog box. When you finish creating the Calculation, it appears in the Item section of the condition.

    For example, if you create a Calculation that computes a Royalty Fee, that Calculation is listed in the Item portion of the condition, and you can then filter the worksheet's data by the Calculation results.

    Select Condition--displays a dialog box that lists the conditions currently defined for the worksheet. Select a condition to become the first part of the advanced conditional expression. With this option you can filter the data using several sequential conditions. That is, condition 1 filters the data, and then based on the results from condition 1, condition 2 filters the data.

    Copy Condition--displays a dialog box that lists the conditions currently defined for the worksheet. Copying a condition inserts it on the line in the New Condition dialog box. You can then edit it, or add other features.

  • To add another line to the condition, click Add.

    By default, the two conditional expressions are grouped with the Boolean AND.

  • To change the grouping, click the AND in the expressions, and then click the Or or Not buttons.

    Figure 8-11 Edit Condition Dialog Box with AND Conditions


    Note: As you create the condition formula, the box at the bottom of the dialog box shows its SQL syntax.

  • Fill in the values for Item, Condition, and Value(s).

    To see options for the Value(s), click the drop-down arrow.

    Figure 8-12 New Condition Dialog Box with Values Displayed


    The values on the list correspond to the selected item in the condition. Other options are:

    Select Multiple Values--displays a list of the values for the item. Click multiple values to include them in the values portion of the condition.

    Create Calculation--click to open the Calculation dialog box. When you finish creating the Calculation, it appears in the Value(s) portion of the condition.

    Select Item--displays a dialog box that lists the items currently defined for the worksheet. Select an item to become the value of the advanced conditional expression.

    Select Parameter--displays a dialog box that lists the parameters currently defined for the worksheet. Select an parameter to become the value of the advanced conditional expression.

    New Parameter--displays the Parameter dialog box, and you can create a parameter to be the value of the condition.

    Create Subquery--displays a dialog box for creating a subquery as the value portion of the condition. See the next section for details.

    Edit Subquery--this option only appears if you're using a subquery to determine the value; the Edit Subquery dialog box appears so you can edit the previous selections for the subquery.

  • Click OK when the advanced condition is complete.

    8.4.1 Creating Subqueries

    A subquery for a condition uses a value that requires an intermediate step to determine the value. For example, suppose you want to create a condition that finds all the sales profits that exceed the median profit amount. The intermediate step is to find the median profit amount. Often, you create a separate worksheet to determine the intermediate value.

    The subquery then identifies the intermediate value on its worksheet as the value for the condition.

    To create a subquery:

    1. Choose Create Subquery from the Values drop-down list on the New Condition dialog box. See Figure 8-12 above.

      The Create Subquery dialog box appears.

    Figure 8-13 Create Subquery Dialog Box


    1. If you've already created a worksheet that contains the intermediate value, select it from the first drop-down list on the dialog box.

      If you need to create a new worksheet that calculates the intermediate value, click the New button. The New Sheet dialog box appears so you can create a new worksheet for the value.

    2. In the second drop-down list, select the original item to be used for the condition. For example, when finding sales profits that exceed the median, the item to select in the second drop-down list is profits.

      See the next section for a description of correlated items.

    3. Click OK. The name of the worksheet appears in the Values portion of the condition on the New Condition dialog box.

    8.4.1.1 Using Correlated Items

    Correlated items add another dimension to a subquery. For example, suppose you want to find all the sales profits that exceed the median profit amount by department. The "by department" portion of the value is the new dimension to the subquery.

    The items to correlate usually appear on both the original worksheet, and the worksheet used to create the intermediate value. For example, on the original worksheet you can find profit data for each department. On the worksheet for calculating the intermediate value, you can find a median value for each department. Correlating the two items matches them so each department median value corresponds to each department profit value.

    Correlating items is also necessary when the worksheet for determining the intermediate value has additional values on it. Correlating makes sure the condition uses the correct intermediate values for the items.

    As stated on the dialog box, you don't need to use the features for correlated items if the subquery does not include the extra dimension provided by items that correlate to one another.


    NOTE: Correlated Subqueries cannot contain filters using Oracle Analytical Functions. For example, if you restrict the values returned in a sheet to the top twenty items (assigned using a Rank function), you cannot use this sheet in a correlated subquery. 


    To use correlated items:

    1. Click the Add button on the Edit Subquery dialog box.

    2. Choose the item from the Add drop-down list.

      The following figure shows an example.

      The item then appears in the box for correlated items.

    Figure 8-14 Correlated Items for a Subquery


    8.4.2 Editing a Subquery

    If you change the worksheet that is used to generate the intermediate value for a subquery, the subquery condition is not automatically updated. You must edit the condition with the subquery first, and then update subquery to match the changed worksheet.

    To edit a subquery:

    1. Edit the condition with the subquery.

    2. Choose Edit Subquery from the Values drop-down list on the Edit Condition dialog box.

      A prompt asks if you want to update the subquery to match the changed worksheet.

    3. Update the subquery.

      The edited subquery and the changed worksheet now match.

    8.5 Setting Options

    The Options dialog box offers a wide variety of options for setting operating features, formatting, and other aspects of your worksheets.

    To select options:

    1. Choose Tools | Options, or click the Options button available on several dialog boxes.

      The Options dialog box appears.

    Figure 8-15 Options Dialog Box


    8.6 Using Command-Line Options

    You can run Discoverer from the command line and perform a limited number of tasks automatically, for example, opening or printing a Workbook.
    To use command-line options, type the command string for starting Discoverer, following by a command-line option, (see table 8-1 below).

    To run a command line option:

    1. From the Windows Start menu, choose Run.

      The Run dialog box appears.

    2. Type:

      <drive>\orant\discvr4\dis4usr.exe /connect me/mypassword@mydatabase 
      <option>
      

      Where 'me' is your Discoverer ID, 'mypassword' is your Discoverer password, and 'mydatabase' is the Oracle database to which you want to connect, (see your Oracle Administrator for connect details).

      Table 8-1 Sample Command Line Options
      Option  Action  Effects  Notes 

      /open <file> 

      Open a .DIS file 

      Opens a workbook from a file. 

      Skips the Connect dialog box and open the workbook. 

      /p <file> 

      Print a Workbook 

      Prints workbook from the file to the default printer. 

      Print options are saved as part of the workbook. 

      /sheet ALL 

      Activate all sheets 

      Runs queries for all sheets in the workbook. 

      When the workbook opens all queries on all sheets are already run. 

    For example, to open a file Reports.DIS in your root directory, type:

    <drive>\orant\discvr4\dis4usr.exe /connect me/mypassword@mydatabase /open c:\Reports.DIS

    To print a file Reports.DIS in your root directory, type:

    <drive>\orant\discvr4\dis4usr.exe /connect me/mypassword@mydatabase /print c:\Reports.DIS

    8.7 Using SQL

    If you are familiar with SQL, you can analyze the SQL statements that Discoverer executes against the database. You can also open workbooks with your own SQL programming statements.

    8.7.1 Looking at the SQL Statements for Worksheets

    To see a worksheet's SQL statements:

    1. Choose View | SQL Inspector.

      The SQL Inspector dialog box appears. It shows the SQL statements used to create your current worksheet.

    Figure 8-16 SQL Inspector Dialog


    1. Click Copy to copy the statements and paste them to another SQL program.

      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. You must write your own programs.

    2. Click Export to export the statements to another file for use later with another SQL program.

    3. Click OK to close the SQL Inspector dialog box.

    8.7.2 Importing SQL

    If you have written an SQL program to open a workbook, importing the program executes the SQL statement and opens the Discoverer workbook.


    NOTE: If you are importing an SQL script tha contains join definitions, the joins must have been created first by your Discoverer Administrator using the Discoverer Administration Edition. 


    To import SQL and open a workbook:

    1. Choose File | Import SQL.

      The Open dialog box appears.

    Figure 8-17 Open SQL File Dialog


    1. Locate and select the file that contains the SQL statement, then click Open.

      A new workbook is then created from the query defined by the SQL statement.

    8.7.3 Using the Discoverer Execution Plan

    The Plan tab displays the Oracle Server Execution Plan chosen by the Oracle Server for a query request. The Execution Plan defines the sequence of operations that the Oracle Server performs to execute the SQL statement. This facility is useful when using Summary tables and Materialized Views.

    8.7.3.1 About Summaries

    Summary tables and Materialized Views store precomputed aggregated data, which is used where possible instead of data retrieved directly from the database. Because Summary tables and Materialized Views are much quicker to access, this enhances the performance of Discoverer.

    Summary Management is handled automatically by Discoverer, and is transparent to most Discoverer users. However, you may wish to use the SQL Inspector feature to look at SQL statements being generated. 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.

    8.7.3.2 Types of Summary

    Summaries are created by your Discoverer Administrator to help do your work more quickly and efficiently.

    Two types of Summary are used:


    NOTE: For more information on Summaries and Materialized Views, see Oracle8i Data Warehousing Guide Release 2 (8.1.6) (Part Number A76994-01). 


    8.7.4 Looking at an SQL Execution Plan

    To see a worksheet's Execution Plan:

    1. Choose View | SQL Inspector.

      The SQL Inspector dialog appears.

    2. Click the Plan tab.

    Figure 8-18 SQL Inspector Dialog - Plan Tab


    8.7.5 Viewing the SQL and Execution Plan with an 8.1.6+ database

    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 Plan tab in the SQL Inspector dialog to see the SQL statement that Discoverer sends to the server.

    Figure 8-19 SQL Inspector tab displaying an SQL statement


    Although the Discoverer Administrator has created a Summary for the Items City, Region, and Profit SUM, 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, see Figure 8-19 above).

    Figure 8-20 Plan tab displaying the Execution Plan (using a Materialized View)


    In Figure 8-20, 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_MV<Summary Identifier>.

    8.7.6 Viewing SQL with a pre-8.1.6 database (not using a Materialized View)

    When running against a pre-8.1.6 database Discoverer controls redirection to a summary table. The SQL can be viewed at the SQL tab and the server Execution Plan can be viewed on the Plan tab in the SQL Inspector dialog in Discoverer .

    Figure 8-21 shows a crosstab worksheet of items from the Video Analysis folder (created as part of the Video Stores and the resulting SQL statement in the SQL Inspector dialog. The SQL statement shows that the summary table EUL4_SUM100750 is referenced.

    Figure 8-21 Summary Redirection in Progress

    Discoverer automatically chooses the most appropriate Summary Table to process your query efficiently.

    Figure 8-22 shows the same worksheet as before after the user has drilled down from Year to Month. Notice that Discoverer has redirected the second part of the query to EUL4_SUM100774 instead of EUL4_SUM100750.

    Figure 8-22 Summary Redirection in Progress

    Figure 8-23 shows the same worksheet again. This time the user has drilled down from Region to City. Again, Discoverer automatically chooses the most efficient Summary Tables for each part of the query.

    Figure 8-23 Summary Redirection in Progress

    8.7.7 Configuring the SQL type used

    Discoverer uses Inline Views in its SQL generation. This SQL has Inline Views removed to make external editing easier - this process is also known as 'flattening'. When you use the SQL Inspector dialog, typically you are looking at SQL with the Inline Views removed, (or flattened SQL).

    If you are using an ODBC connection, ODBC SQL is created. To set the default variant, there is registry entry called HKEY_CURRENT_USER\Software\Oracle\Discoverer 4\Database\SQLType.
    Set this value to configure how SQL is displayed in the SQL Inspector dialog. The valid values for this entry are 0, 1 and 2, used as follows:

    To configure the SQLType registry setting:

    1. From the Windows Start menu, choose Run.

    2. Type regedit then choose OK.

    3. Navigate to the registry setting in:
      HKEY_CURRENT_USER\Software\Oracle\Discoverer 4\Database\SQLType

    Figure 8-24 Setting the SQLType Registry Setting



    1. Double click on SQLType, change the setting as required to either 0, 1, or 2, (see notes above), then click OK.

    8.7.8 Exporting SQL without running a Workbook

    You can export Workbook SQL from the command line, without running Discoverer. Refer to the Command Line Interface section of the Discoverer Administration Edition Administration Guide for more details, or contact your Discoverer Administrator.


  • Prev Next
    Oracle
    Copyright © 2000 Oracle Corporation.

    All Rights Reserved.

    Library

    Contents

    Index