|
Oracle® Business Intelligence Discoverer Desktop User's Guide
10g Release 2 (10.1.2.1) for Windows B13917-03 |
|
 Previous |
 Next |
|
Oracle® Business Intelligence Discoverer Desktop User's Guide
10g Release 2 (10.1.2.1) for Windows B13917-03 |
|
|
Previous |
Next |
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.
To override that setting you can retrieve all the rows at once instead of incrementally. Retrieving all rows applies only to tabular style reports.
To retrieve all the rows at once instead of incrementally, choose Sheet | Retrieve All Rows. The table includes all the 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.
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.
To refresh a worksheet, choose Sheet | Refresh Sheet. Discoverer Desktop displays the worksheet results based on the updated data.
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).
Figure 8-2 Sample Parameters for Video Sales
To create a parameter:
Display the worksheet to which you want to apply the parameter.
The Parameters dialog box opens. It shows the parameters already created.
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.
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).
Choose Sheet | Edit Parameter Values.
The Parameters dialog box appears.
Select the new value(s) for the Parameter.
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.
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.
USER
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.
NULL
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.
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:
Discoverer Desktop Calculations are used to analyze the data in your worksheets. Discoverer Desktop 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 Desktop data, refer to Appendix A, "About the examples in this chapter". |
Here is a sample of the results of a simple Calculation on a table:
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 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.
This dialog box shows Calculations already created for the worksheet. Selected Calculations (where the check box is selected) are active and apply to the worksheet.
Click New.
The Edit Calculation dialog box for creating a new Calculation appears.
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.
Type the new Calculation directly into the Calculation box.
Mix typing with pasting and clicking the operators.
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
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.
The advanced condition option allows you to build complex conditions for filtering your worksheet data.
To create an advanced condition:
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).
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 New 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.
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.
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.
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.
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.
Figure 8-14 Correlated Items for a Subquery
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.
Click OK.
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.
The Options dialog box offers a wide variety of options for setting operating features, formatting, and other aspects of your worksheets.
To select options:
Choose Tools | Options, or click the Options button available on several dialog boxes.
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.
Cache—displays options for the memory cache, disk cache, and cache directory for Discoverer Desktop. Generally these options should not be changed without assistance from the database administrator.
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.
EUL—displays the default EUL setting. Use the option on this tab to select a default EUL (End User Layer). Check with your database administrator for the correct EUL to select.
You can run Discoverer Desktop 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 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.
Type:
<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.
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
If you are familiar with SQL, you can analyze the SQL statements that Discoverer Desktop executes against the database. You can also open workbooks with your own SQL programming statements.
To see a worksheet's SQL statements:
The SQL Inspector dialog box appears. It shows the SQL statements used to create your current worksheet.
Click Copy to copy the statements and paste them to another SQL program.
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 Export to export the statements to another file for use later with another SQL program.
Click OK to close the SQL Inspector dialog box.
If you have written an SQL program to open a workbook, importing the program executes the SQL statement and opens the Discoverer Desktop workbook.
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.
To see a worksheet's Execution Plan:
Click the Plan tab.
Figure 8-18 SQL Inspector Dialog - Plan Tab
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.
Figure 8-19 SQL Inspector tab displaying an SQL statement
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).
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 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.
To configure the SQLType registry setting:
From the Windows Start menu, choose Run.
Navigate to the registry setting in: HKEY_CURRENT_USER\Software\Oracle\Discoverer 10\Database\SQLType
Figure 8-21 Setting the SQLType Registry Setting
Double click on SQLType, change the setting as required to either 0, 1, or 2, (see notes above), then click OK.
