|Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Desktop
11g Release 1 (11.1.1)
Part Number B40106-01
Discoverer Desktop includes several advanced features for working with data. This chapter describes those features and explains how to use them.
The advanced features are:
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.
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.
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 Desktop in conjunction with real-time data. For example, if the Discoverer Desktop 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.
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:
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.
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).
To create a parameter:
Display the worksheet to which you want to apply the parameter.
The New Parameter dialog box appears.
This is where you define new parameters.
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”.
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.
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 edit parameter values: Here you display the Parameter dialog so that you can choose a different value to display on your worksheet(s).
The Parameters dialog box appears.
Select the new value(s) for the Parameter.
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.
You can enter a reserved word into a parameter field, however the reserved word must have the same data type as the parameter (e.g. SYSDATE or TODAY can only be used with a parameter that has the data type DATE). You can enter the following reserved words into a parameter field:
SYSDATE or TODAY
Enter the reserved word SYSDATE or TODAY into a parameter field (must be of data type DATE) to display worksheet data that matches the system or today's date.
Enter the reserved word USER into a parameter field (must be of the same datatype as the datatype of the parameter) to display worksheet data that matches all database users.
Enter the reserved word NULL into a parameter field (must be of data type DATE, STRING or NUMERIC) to display worksheet data where the item that this parameter is based upon has a NULL value.
Note:You must enter reserved words using only capital letters and with no additional text or text symbols. For example, you cannot use NULL% or null or 'NULL'. Discoverer Desktop would treat 'NULL' as a text string and not as a reserved word.
Here's an example:
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.
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 Desktop data, refer to Appendix A, "About the examples in this chapter".
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 Desktop's Analytic Functions LAG and LEAD, (see Appendix A, "Lag/Lead Function Examples").
To create a Calculation:
Display the worksheet on which you want to apply the Calculation.
The Calculations dialog box appears.
To help you create the Calculation formula with a minimum of typing, you can:
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.
Click OK and then on the Calculation dialog box, click OK.
To create an advanced condition:
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.
To change the grouping, click the AND in the expressions, and then click the Or or Not buttons.
Fill in the values for Item, Condition, and Value(s).
To see options for the Value(s), click the drop-down arrow.
The values on the list correspond to the selected item in the condition. Other options are:
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:
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.
If you've already created a worksheet that contains the intermediate value, select it from the first drop-down list on the dialog box.
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.
Click OK. The name of the worksheet appears in the Values portion of the condition on the New Condition dialog box.
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:
Click the Add button on the Edit Subquery dialog box.
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.
The Item column refers to the item on the original worksheet. The Subquery Item column refers to the item on the worksheet used to determine the intermediate value.
Normally you correlate the same item on each worksheet, but you can also correlate two different items. To correlate different items, choose the new item from the Subquery Item drop-down list.
To add additional dimensions to the list of correlated items, click the Add button again and repeat the process.
To remove an item from the correlation list, select it and click the Remove button.
Note:When creating a new workbook, do not create worksheets for sub-queries as part of the new workbook process. Instead, finish creating the new workbook first, and then create worksheets for the sub-queries. This ensures that you can select the proper worksheets for the sub-queries from the Conditions dialog box.
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:
Edit the condition with the subquery.
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.
Update the subquery.
The edited subquery and the changed worksheet now match.
To select options:
The Options dialog box appears.
The tabs across the top of the dialog box list the different option categories. If you accessed the dialog box by clicking the Options button on another dialog box, the tabs across the top may only apply to that dialog box.
Click a tab to see its options. To see additional tabs, click the right or left arrows at the top right side of the dialog box.
General—displays options for viewing different types of files, and for opening workbooks. Click the Viewer check boxes if the worksheets include files with videos, images, and sound (audio). Those viewers open automatically from within Discoverer Desktop to run the files.
Query Governor—displays options for maximizing the efficiency of working with larger worksheets, limiting the amount of time a query runs, and limiting the number of rows retrieved. You can also choose whether to use Summary data to improve the performance of Discoverer Desktop.
Table/Crosstab—displays options for the overall table or crosstab layout; you can add or remove gridlines, column headings, row numbering, and so forth. Removing various table or crosstab features is particularly helpful when printing a worksheet as a report. Tab options differ according to whether your worksheet is a Tabular or Crosstab worksheet.
Formats—displays options for setting the default formats for worksheet headings, data, titles, totals, exceptions, and null values. To change a format, select it and click the Change button. The dialog box for setting the font, color, and backgrounds appears.
Connection—displays options for setting the EUL types that can be used.
Advanced—displays options for configuring Automatic Querying, Fan-trap Detection, and Multiple Join Path Detection. When you open Workbooks, Discoverer Desktop can run queries automatically or not, or can request confirmation before running queries.
To use command-line options, type the command string for starting Discoverer Desktop, following by a command-line option, (see table 8-1 below).
To run a command line option:
From the Windows Start menu, choose Run.
The Run dialog box appears.
<drive>\orant\bin\dis51usr.exe /connect me/mypassword@mydatabase <option>
Where 'me' is your Discoverer Desktop ID, 'mypassword' is your Discoverer Desktop password, and 'mydatabase' is the Oracle database to which you want to connect, (see your Oracle Administrator for connect details). The following table displays some sample command line options.
|/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\bin\dis51usr.exe /connect me/mypassword@mydatabase /open c:\Reports.DIS
To print a file Reports.DIS in your root directory, type:
<drive>\orant\bin\dis51usr.exe /connect me/mypassword@mydatabase /print c:\Reports.DIS
To see a worksheet's SQL statements:
The SQL statements Discoverer Desktop 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.
Click OK to close the SQL Inspector dialog box.
Note:If you are importing an SQL script tha contains join definitions, the joins must have been created first by your Discoverer manager using Discoverer Administrator.
To import SQL and open a workbook:
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.
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 Desktop.
Summary Management is handled automatically by Discoverer, and is transparent to most Discoverer Desktop 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 manager.
Summaries are created by your Discoverer manager to help do your work more quickly and efficiently.
Two types of Summary are used:
To see a worksheet's Execution Plan:
When running Discoverer Desktop against an Oracle Enterprise Edition 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 Desktop sends to the server.
Although the Discoverer manager 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).
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 EUL5_MV<Summary Identifier>.
Discoverer Desktop 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).
To set the default variant, there is registry entry called HKEY_CURRENT_USER\Software\Oracle\Discoverer 10\Database\SQLType. Set this value to configure how SQL is displayed in the SQL Inspector dialog. The valid values for this entry are 0 and 2, used as follows:
0 - Show SQL with no Inline Views (flattened SQL). This is the default.
2 - Show SQL with Inline Views.
You can export Workbook SQL from the command line, without running Discoverer Desktop. Refer to the Command Line Interface section of the Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Discoverer for more details, or contact your Discoverer manager.