18
Advanced Discoverer Plus Features

This chapter explains how to use some of Discoverer's advanced features, and includes the following topics:

• "Using scheduled workbooks"

• "Using lists of values (LOVs)"

• "Changing default settings"

• "Using SQL"

Using scheduled workbooks

This section describes how to improve productivity using Discoverer's scheduling facilities, and includes the following topics:

• "What are scheduled workbooks?"

• "When do I need to use scheduled workbooks?"

• "About scheduled workbooks"

• "About accessing scheduled workbook results"

• "About how scheduled workbooks are processed?"

• "How to schedule the currently opened workbook"

• "How to schedule unopened workbooks"

• "How to change the properties of a scheduled workbook"

• "How to copy a scheduled workbook"

• "How to unschedule a workbook"

• "How to delete scheduled workbook results"

What are scheduled workbooks?

Scheduled workbooks are Discoverer workbooks that are processed at a specified time and frequency in the database. For example, regular weekly reports or complex reports that must be processed at off-peak times.

Note: You must have the required privileges to schedule workbooks. Contact the Discoverer manager for more details.

When scheduled workbooks are processed, scheduled workbook results are produced. Scheduled workbook results can then be analyzed just like ordinary Discoverer workbooks. For example, to produce reports and graphs.

You do not have to be connected to Discoverer to process scheduled workbooks.

For more information about how workbooks are processed, see "About how scheduled workbooks are processed?"

The figure below illustrates how a scheduled workbook produces regular scheduled workbook results at a specified time.

Figure 18-1 Scheduled workbooks and results

Text description of the illustration sc_dg2.gif

Key to figure:

1. Produce a daily/weekly/monthly report at the scheduled time.

When do I need to use scheduled workbooks?

Typically you schedule workbooks in any of the following circumstances:

• A workbook will take a long time to process. Scheduling a workbook to be processed at off-peak times avoids overburdening the server during peak times.

• You process a large or complex workbook or worksheet that exceeds the time limit set by the Discoverer manager. When this happens, a Schedule button appears informing you that the workbook/worksheet query time exceeds the time limit. You can then click the Schedule button and schedule the workbook to process the workbook at off-peak times that are not restricted by the time limit.

• You want to process a workbook at regular intervals, such as a weekly report of sales figures or a monthly cash-flow analysis.

• The Discoverer manager has specified that you can only open scheduled workbooks results. You are not allowed to open standard workbooks arbitrarily from the database.

About scheduled workbooks

Scheduled workbooks have the following characteristics:

• You can only schedule existing workbooks.

• A set of scheduled workbook results is created each time a scheduled workbook is run. For example, a weekly scheduled workbook produces one set of results each week.

  In the figure below, a workbook is scheduled to run at 1.00 a.m. on January 1, 2003, and then once a week at the same time.

  Figure 18-2 Scheduling a workbook in Discoverer

  
  
  Text description of the illustration sw2.gif

• You can only edit scheduled workbooks that you create yourself (using your current Discoverer user name).

• You manage scheduled workbooks using the Scheduling Manager (see figure below).

  Figure 18-3 The Discoverer Scheduling Manager

  
  
  Text description of the illustration sc_dg1.gif

Key to figure:

1. A scheduled workbook called 'Ad hoc analysis report'.

2. The expandable list below each workbook contains details of each set of results produced by each scheduled workbook. In this example, there is one set of results for 'Ad hoc analysis report', which is ready to open.

3. The + symbol next to each scheduled workbook is used to expand lists of results. The Status field shows whether sets of results are ready to use. In this example, the scheduled workbook 'Performance analysis' is pending (i.e. not yet processed).

About accessing scheduled workbook results

Scheduled workbook results can be viewed in Discoverer Plus or Discoverer Viewer.

You can open scheduled workbook results at any time when connected to Discoverer.

When you connect to Discoverer, you are alerted when scheduled workbook results have been processed and are ready to open.

About how scheduled workbooks are processed?

Discoverer processes scheduled workbooks as follows:

• When workbooks are scheduled, the workbook processing is done automatically in the database.

• You do not need to have Discoverer Plus running or connected to process scheduled workbooks.

• Scheduled workbook results are saved in the database and are available when you start Discoverer.

How to schedule the currently opened workbook

When you create a new workbook, you schedule it when you want to process the workbook at a particular time and frequency. For example, to process a daily sales workbook at 1.00 a.m. every morning (because that is when the network has spare capacity).

To schedule the currently opened workbook:

1. Choose File | Schedule to display the "Schedule Wizard dialog".

   
   
   Text description of the illustration sw1.gif

2. Use the "Schedule Wizard: General dialog" page to define a name and description for the scheduled workbook, and specify which worksheets you want to schedule.

3. (optional) If parameters are required for the worksheets that you specify, use the "Schedule Wizard: Parameter Values dialog" page(s) to enter required parameter values.

   
   
   Text description of the illustration sw9.gif

4. Use the "Schedule Wizard: Schedule dialog" page to specify when the scheduled workbook results are produced and how frequently they are produced.

   
   
   Text description of the illustration sw2.gif

5. Click Finish to create the scheduled workbook.

   Hint: To monitor the progress of scheduled workbooks, use the "Scheduling Manager dialog". Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog"

Notes

• For more information about how workbooks are processed, see "About how scheduled workbooks are processed?"

How to schedule unopened workbooks

You schedule unopened workbooks using the Scheduling Manager. For example, you might want to schedule a workbook previously created by another Discoverer user.

To schedule a workbook:

1. Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog".

   
   
   Text description of the illustration sw3.gif

2. Click Schedule to display the "Select Workbook from Database dialog (to schedule)", which displays a list of workbooks to which you have access.

3. Select the workbook that you want to schedule and click Select to display the "Schedule Wizard dialog".

4. Use the "Schedule Wizard: General dialog" page to define a name and description for the scheduled workbook, and specify which worksheets you want to schedule.

5. (optional) If parameters are required for the worksheets that you specify, use the "Schedule Wizard: Parameter Values dialog" page(s) to enter required parameter values.

6. Use the "Schedule Wizard: Schedule dialog" page to specify when the scheduled workbook results are produced and how frequently they are produced.

7. Click Finish to create the scheduled workbook.

   Hint: To monitor the progress of scheduled workbooks, use the "Scheduling Manager dialog". Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog"

Notes

• You must have the required privileges to schedule workbooks. Contact the Discoverer manager for more details.

• For more information about how workbooks are processed, see "About how scheduled workbooks are processed?"

How to change the properties of a scheduled workbook

You change the properties of a scheduled workbook when you want to change how it is configured. For example, to change when a workbook is processed.

To edit a scheduled workbook:

1. Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog".

2. Select the scheduled workbook that you want to edit from the Scheduled Workbook list.

3. Click Edit to display the "Edit Scheduled Workbook dialog".

   
   
   Text description of the illustration sw4.gif

4. Use the "Edit Scheduled Workbook: General tab" to define a description for the scheduled workbook, and view which worksheets are scheduled.

5. (optional) If the workbook has active parameters, use the "Edit Scheduled Workbook: Parameter values tab" to enter required parameters.

6. Use the "Edit Scheduled Workbook: Schedule tab" to specify when the scheduled workbook results are produced, how frequently they are produced, and which results are saved on the server.

7. Click OK to save changes to the scheduled workbook.

   Hint: To monitor the progress of scheduled workbooks, use the "Scheduling Manager dialog". Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog"

How to copy a scheduled workbook

You copy a scheduled workbook when you want to quickly create a new scheduled workbook based on an existing scheduled workbook. For example, when a similar scheduled workbook already exists that you want to use as a template to quickly schedule a workbook.

To copy a scheduled workbook:

1. Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog".

2. Select the scheduled workbook that you want to copy from the Scheduled Workbook list.

3. Click Copy to display the "Edit Scheduled Workbook dialog".

   
   
   Text description of the illustration sw6.gif

4. Use the "Edit Scheduled Workbook: General tab" to a specify a name and description for the scheduled workbook, and view which worksheets are scheduled.

   Note: Enter a unique name here for the new scheduled workbook. If you do not enter a unique name, Discoverer generates a unique name for you and prompts you to verify the name.

5. (optional) If the workbook has active parameters, use the "Edit Scheduled Workbook: Parameter values tab" to enter required parameters.

6. Use the "Edit Scheduled Workbook: Schedule tab" to specify when the scheduled workbook results are produced and how frequently they are produced.

7. Click OK to save changes to the scheduled workbook.

   The new scheduled workbook is displayed in the "Scheduling Manager dialog".

   Hint: To monitor the progress of scheduled workbooks, use the "Scheduling Manager dialog". Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog"

Notes:

• When you copy an existing scheduled workbook, the original scheduled workbook is unchanged, unless you overwrite the original with the same scheduled workbook name.

How to unschedule a workbook

You unschedule a workbook when you want to stop a scheduled workbook being processed. For example, when you no longer need to produce a monthly report. You can choose either to keep all results for this scheduled workbook or delete all results.

To unschedule a scheduled workbook:

1. Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog".

   
   
   Text description of the illustration sw3.gif

2. Select the scheduled workbook that you want to stop from the Scheduled Workbook list.

3. Click Unschedule to display the Confirm Unschedule dialog.

4. Click OK to unschedule the workbook.

   Changes to the scheduled workbook are reflected in the "Scheduling Manager dialog".

   Hint: To monitor the progress of scheduled workbooks, use the "Scheduling Manager dialog". Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog"

How to delete scheduled workbook results

You delete scheduled workbook results when you no longer need to use results generated by scheduled workbooks. For example, you might have monthly reports that have accumulated over the previous year that you want to delete permanently.

When you delete scheduled workbook results, you have the following options:

• You can choose to remove results but keep the scheduled workbook so that the scheduled workbook continues to generate results.

• You can choose to remove results and stop (or unschedule) the scheduled workbook so that it no longer generates results.

To delete a scheduled workbook:

1. Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog".

   
   
   Text description of the illustration sw3b.gif

2. Select the scheduled workbooks and results that you want to delete from the Scheduled Workbook list.

   Note: You can select more than one list item by pressing the Ctrl key and clicking another list item.

3. Click Delete to display a confirmation dialog.

4. Click OK to delete the scheduled workbook(s).

   Changes to the scheduled workbook are reflected in the "Scheduling Manager dialog".

   Hint: To monitor the progress of scheduled workbooks, use the "Scheduling Manager dialog". Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog"

Notes

• If the Delete results after <n> days option is used, scheduled workbook results are deleted automatically. This option is set in the following ways:
  • when you create a scheduled workbook using the "Schedule Wizard: Schedule dialog" page

  • when you edit a scheduled workbook using the "Edit Scheduled Workbook: Schedule tab"

Using lists of values (LOVs)

This section describes how to improve productivity using Lists of Values (LOVs) in Discoverer, and includes the following topics:

• "What are LOVs?"

• "LOV examples"

• "About using long LOVs"

• "How to select single values from long LOVs"

• "How to select multiple values from long LOVs"

What are LOVs?

LOVs contain a list of valid values for an item. For example, a LOV for a year item might contain the values 1998, 1999, and 2000 (see figure below).

Figure 18-4 A LOV on a year item, containing 1998, 1999, and 2000

Text description of the illustration lov4.gif

You use LOVs in:

• parameters

• conditions

• the Discoverer item navigator

• the export wizard

  
  

LOVs are used in the following way:

• When used in parameters, conditions, and export, LOVs enable you to select predefined values rather than enter arbitrary values in a text field.

• Because LOVs contain only predefined values in the database, you know that you will always enter a valid value.

• When used in the Discoverer item navigator, LOVs enable you to apply conditions to worksheets without defining conditions criteria. For example, choosing 2000 from a LOV in the Discoverer item navigator filters a worksheet to display only data for 2000.

• LOVs work differently with parameters and conditions:
  • With parameters, the Discoverer user that creates the workbook specifies whether single or multiple values are allowed. For example, when setting a parameter, a user might choose 1999 and 2000 from a LOV (for more information, see "A LOV used to specify worksheet parameters").

  • With conditions, the condition type determines whether you can select single or multiple values. Only the following condition types allow multiple values:
    • Like

    • Not like

    • In

    • Not in

    • Is null

    • Is not null

    • Between

LOV examples

This section contains examples of using LOVs.

A LOV used to specify worksheet parameters

In the figure below, a LOV has been created on the department item, containing the departments Video Rental, Video Sale etc. If a LOV was not defined on Department, you might enter 'Video Hire' here, which would result in an empty worksheet because the database does not contain this department. The figure below shows the value Video Rental being selected from a LOV in a parameter dialog.

Figure 18-5 A LOV being used to specify worksheet parameters

Text description of the illustration param5a.gif

A LOV used in a condition

LOVs are also used when you create conditions. For example, in the figure below, the LOV containing months is used to choose values against which to match worksheet data. The figure below shows the value Feb (i.e. February) being selected from a LOV in a condition dialog

Figure 18-6 A LOV being used in a condition

Text description of the illustration lov2.gif

A LOV used in the Discoverer item navigator

LOVs are also used in the Discoverer item navigator. For example, in the figure below, the LOV values Video Sales and Video Rentals are selected for display on a worksheet. In other words, the LOV values are used to filter the worksheet. The figure below shows the values Video Sale and Video Rental being selected in the Discoverer item navigator.

Note: LOVs in the Discoverer item navigator might be turned off. Contact the Discoverer manager for more details.

Figure 18-7 A LOV used in the Discoverer item navigator

Text description of the illustration lov3.gif

About using long LOVs

When LOVs contain a large number of values, Discoverer displays a dialog that enables you to search LOV values and select the values that you want. For example, if a LOV contains hundreds of values, you can select only values that begin with the letter 'A', or select only values that contain 'CPM'.

Note: For more information about Discoverer dialogs used to search LOVs, see "Select Value dialog" and "Select Values dialog".

When using long LOVs, the following rules apply:

• When LOVs are used with parameters, you can use the Select Multiple Values option to display a dialog that enables you to search and select LOV values.

• When LOVs are used with conditions, you can use the Select values option to display a dialog that enables you to search and select LOV values.

• The Discoverer manager might configure Discoverer Plus to always display a search dialog for selecting LOV values, even when the LOV will fit on screen.

For more information about selecting values from long LOVs, see "How to select single values from long LOVs" and "How to select multiple values from long LOVs".

• LOV values are displayed in groups. The size of groups is configured by the Discoverer manager (maximum 100).

How to select single values from long LOVs

When LOVs contain a large number of values, you select single LOV values using the "Select Value dialog". For example, to analyze data from the year 2000, you select 2000 from the LOV.

To select single values from long LOVs:

1. Display the "Select Value dialog".
   • from the "Edit Parameter dialog" or "New Parameter dialog", click the down arrow next to the What default value do you want to give this parameter? field, then choose Select values.

   • from the "Edit Condition dialog" or "New Condition dialog", choose Select values from the Values drop down list.

     
     
     Text description of the illustration sel_val.gif

2. If the Displayed values list contains the value that you want, select the value from the Displayed values list.

3. (optional) If you cannot see the value that you want in the Displayed values list, do one of the following:
   • use the scroll bar to navigate up and down the values in the current group

   • use the Next and Previous buttons to display the next or previous group of values in the LOV

4. (optional) Limit the values in the Displayed values list using the Search by and Search for fields, as follows:
   1. Use the Search by drop down list to specify how you want to match LOV values.

      For example, Starts with or Equals.

   2. Enter a search term in the Search for field.

      For example, if you choose 'Starts with', type A to find LOV values that begin with A.

   3. (optional) Select the Case sensitive check box to match upper and lower case letters exactly. For example, when selected the value 'CPM' would not find details containing 'Cpm' or 'cpm'.

      Note: For quicker searches, select the Case-sensitive check box.

   4. Click Go to start the search.

      Values that match the search criteria are displayed in the Displayed values list. Values are displayed in groups. For example, groups of 50 or groups of 100.

   5. Select the value that you want from the Selected values list.

5. Click OK to choose the selected LOV value and close the dialog.

The LOV value that you specify is selected.

How to select multiple values from long LOVs

When LOVs contain a large number of values, you select multiple LOV values using the "Select Values dialog". For example, if you want to analyze data from the values beginning with 'CPM', you select CPM from the LOV.

To select multiple values from long LOVs:

1. Display the "Select Values dialog".
   • from the "Edit Parameter dialog" or "New Parameter dialog", click the down arrow next to the What default value do you want to give this parameter? field

   • from the "Edit Condition dialog" or "New Condition dialog", choose Select multiple values from the Values drop down list

     
     
     Text description of the illustration sel_vals.gif

2. If the Displayed values list contains the values that you want, move the values from the Displayed values list to the Selected values list.

3. (optional) If you cannot see the values that you want in the Displayed values list, do one of the following:
   • use the scroll bar to navigate up and down the values in the current group

   • use the Next and Previous buttons to display the next or previous group of values in the LOV

4. (optional) Limit the values in the Displayed values list using the Search by and Search for fields, as follows:
   1. Use the Search by drop down list to specify how you want to match LOV values.

      For example, Starts with or Equals.

   2. Enter a search term in the Search for field.

      For example, if you choose 'Starts with', type A to find LOV values that begin with A.

   3. (optional) Select the Case-sensitive check box to match upper and lower case letters exactly. For example, when selected the value 'CPM' would not find details containing 'Cpm' or 'cpm'.

      Note: For quicker searches, select the Case-sensitive check box.

   4. Click Go to start the search.

      Values that match the search criteria are displayed in the Displayed values list. Values are displayed in groups. For example, groups of 50 or groups of 100.

   5. To select the values that you want in the Displayed values list, move LOV values from the Displayed values list to the Selected values list.

5. Click OK to choose the selected LOV values and close the dialog.

The LOV values that you choose are selected.

Notes

• To deselect LOV values, move LOV values from the Selected list to the Displayed list.

Changing default settings

This section explains how to use Discoverer's default settings, and contains the following topics:

• "About default settings"

• "How to change default settings"

• "How to revert to the default format settings"

• "Notes on setting Advanced options"

About default settings

Discoverer's default Graphical User Interface (GUI) settings determine Discoverer's appearance and behavior. You can change the default options to suit your preferences and requirements. For example, you might want new worksheets to have a grey background and blue text. Or you might want to limit the amount of data returned by a query so that worksheets are not too large.

You are advised to only change your default settings in one of the following circumstances:

• you want to change only the default color and style of headings and data in your worksheets

• you are an experienced Discoverer user

• you have been asked to change your default settings by the Discoverer manager

Default settings are applied in the following manner:

• Default options apply when you start working with Discoverer.

• Changes to default settings apply to all subsequent workbooks.

• Changes to default settings do not affect workbooks created previously.

• You can override default settings in worksheets using the Format Sheet options. This applies a new style to worksheet elements but does not change the default settings.

Note: In addition to opening the Options dialog from the menus, you can also open the Options dialog by clicking the Options button in other dialogs (where available). In this case the options might apply only to worksheet components configured by that dialog.

How to change default settings

You change default settings to change Discoverer's appearance and behavior. For example, you might want to:

• change the default background color for worksheets to blue

• turn on automatic querying so that worksheets are refreshed with up-to-date data when you change a worksheet layout

To change default settings:

1. Choose Tools | Options to display the Options dialog at the "Options dialog: General tab".

2. Display the tab for the area that you want to change, as follows:

   Tab	Use to
   "Options dialog: General tab"	Specify how Discoverer displays data when worksheets are first opened
   "Options dialog: Query Governor tab"	Specify how Discoverer uses summaries, and how Discoverer manages queries
   "Options dialog: Sheet Format tabs"	Specify how Discoverer displays worksheets, such as with titles, grid lines, row numbers, and the number of rows per screen
   "Options dialog: Default Formats tab"	Specify how Discoverer displays new worksheet headings, data, and totals
   "Options dialog: Advanced tab"	Specify how Discoverer uses automatic querying, fan trap detection and resolution, and multiple join paths (for more information on these options, see "Notes on setting Advanced options")
   "Options dialog: EUL tab"	Specify a default End User Layer (EUL). The Discoverer manager may have given you access to more than one EUL

3. Click OK at any time to save any changes that you have made and close the Options dialog.

4. Click Cancel at any time to cancel any changes that you have made and close the Options dialog.

Changes to default options that you save take effect when you close the dialog.

Notes

• After changing Default Format options, you can revert to the original default format settings using the Reset option (see "How to revert to the default format settings").

• Do not change options on the Advanced tab or EUL tab setting unless asked to do so by the Discoverer manager.

How to revert to the default format settings

Sometimes, after changing the default formats for a workbook, you might want to revert to the original default format settings. For example, if you change default text fonts to produce a particular style of report, you might want to set the text fonts back to their original default style.

Instead of changing each format setting individually, you can use the Reset facility.

Note: Default formats apply to all worksheets in a workbook.

To revert to original default format settings:

1. Choose Tools | Options to display the Options dialog.

2. Display the "Options dialog: Default Formats tab".

3. Select one or more default formats that you want to reset.

   For example, Date Format or Heading Format.

4. Click the Reset button to revert back to the default settings for the selected default formats.

5. Click OK to close the Options dialog.

If you reset the default formats, these original default formats apply when you close the Options dialog.

Notes on setting Advanced options

If you are a Discoverer manager, or an experienced Discoverer user, the following topics are relevant to the "Options dialog: Advanced tab".

About automatic querying

When automatic querying is turned on, Discoverer automatically re-queries the database to get the up-to-date data every time a worksheet is changed to display different data. For example, when you add or remove an item, or pivot items.

In some circumstances however, you might not want Discoverer to automatically re-query the database every time you change the worksheet layout. For example, you might want to make several changes at once and not perform a re-query until you have finished.

About fan traps

Fan traps occur when the data items in two folders are not directly related but do have a relationship based on the data items in a third folder.

For example, a database contains three tables:

• Departments

• Employees

• Locations

The figure below shows that the Department table is a master table to the Employees table and the Locations table in a relational one to many (1:M) relationship.

Figure 18-8 A database with three tables

Text description of the illustration fantrap1.gif

Each employee is associated with a single department because each employee works in only one department. In addition, each employee can only be based in one location. However, departments 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 table, employees become unintentionally associated with multiple locations. This is incorrect because employees can only be in one location.

For example, a query to count the number of employees at each location and department 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 Amsterdam and San Francisco. The figure below shows how a query to return the number of employees returns eight rows in a fan trap relationship instead of four rows.

Figure 18-9 Fan trap query results

Text description of the illustration fantrap2.gif

Key to figure:

1. Query to count the number of employees returns this data

When you create a worksheet, Discoverer automatically detects and resolves fan traps. If the fan trap is unresolvable, Discoverer disallows the worksheet and displays an error message.

For more information about enabling and disabling fan trap detection in Discoverer , see the "Options dialog: Advanced tab".

About multiple join paths

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:

• multiple folders have an unambiguous relationship between them

• the relationships between data items is also unambiguous

Multiple join paths occur when two tables can be linked in more than one way. For example, a sales order database might be linked to a customer database on the Customer ID field, because both tables contain the field Customer ID. If both tables also contain a field called Location, this provides an alternative join path for the two tables. This is known as a multiple join path.

Multiple join paths occur when databases are organized so that the relationships between items in different tables are ambiguous. When you are create new worksheets, Discoverer can automatically detect and warn you if potential multiple join paths exist. This ensures that you always get the results that you expect, because you do not associate items in a way that you did not intend.

Multiple join path warnings are not error messages. The warnings merely advises you that the database contains ambiguous relationships. If warnings occur, contact the Discoverer manager who can determine if the database's organization needs to be modified.

Note: To detect multiple join paths, make sure that the Disable Multiple Join Path Detection option is not selected on the "Options dialog: Advanced tab".

Using SQL

This section is aimed at experienced Discoverer users who are familiar with SQL (Structured Query Language) and who are interested in Discoverer's advanced facilities. Discoverer managers will also find this section useful.

This section contains the following topics:

• "What is SQL"

• "Why should I be interested in SQL?"

• "About the Discoverer execution plan"

• "About viewing the SQL and execution plan with an Oracle 8 and later database"

• "How to view SQL"

• "How to view a SQL execution plan"

• "SQL Examples"

• "Looking at an execution plan when using an Oracle 8 and later database"

What is SQL

SQL is a generic programming language used to extract and manipulate data in a database. In other words, SQL enables you to ask a question (known as a query) of the database that the database answers by displaying data.

For example, you might use SQL to ask the question 'Which products sell more than 10,000 per year?'. The database uses SQL to return a list of products that sell more than 10,000, and may also perform other analysis such as sorting, grouping, and totalling of the data.

SQL is a powerful language, but is difficult to learn and use. Although Discoverer itself uses SQL to display and analyze worksheet data, Discoverer users are shielded from underlying SQL.

Why should I be interested in SQL?

Because Discoverer shields Discoverer users from underlying SQL, they do not need to know how SQL works. This enables users with no technical database experience and no knowledge of underlying database structures to perform sophisticated data analysis.

However, in some circumstances, you may want to look at SQL being used by Discoverer. For example, to improve Discoverer performance you might need to look at underlying SQL to ensure that queries are being run efficiently.

What are summaries?

Summaries are database tables that contain commonly accessed, pre-processed data, which gives the following benefits:

• because data is pre-joined and pre-aggregated, Discoverer can access this data more quickly than by using ad hoc query

• this also means that the data is processed once and accessed many times, rather than re-processed every time it is needed

What are summary folders

A summary folder is how Discoverer represents an underlying summary or materialized view. Summaries and materialized views pre-compute and store aggregated data for use in SQL queries.

Summaries are created by the Discoverer manager to improve the performance of Discoverer, to help do your work more quickly and efficiently. Summary tables and materialized views are created as follows:

• a summary table is a table that Discoverer creates.

• a materialized view is the Oracle 8 (and later) server's own summary mechanism.

Note: For more information on summaries and materialized views, refer to the Oracle8i Data Warehousing Guide.

What is an execution plan?

An execution plan is a sequence of operations that the Oracle Server performs to execute a SQL statement.

About the Discoverer execution plan

When looking at the underlying SQL that Discoverer is using, use the Discoverer execution plan tab to look the underlying execution plan being used.

You can look at an execution plan to see how a SQL statement is being executed. For example, when using Summaries, you may want to check that a query is using a summary or materialized view created by the Discoverer manager.

About viewing the SQL and execution plan with an Oracle 8 and later database

When running Discoverer against an Oracle 8 and later database, the server controls query redirection by rewriting the SQL to use materialized views. If a server rewrite occurs, the server Execution Plan tells you the name of the materialized view being used.

For information about materialized views and server rewrites, refer to the Discoverer Administrator Manager's Guide. Contact the Discoverer manager for more information.

Note: With a pre-8.1.6 database, you can look in the SQL tab on the SQL Inspector dialog to see the name of the summary being used.

How to view SQL

You view SQL created by Discoverer when you want to see the underlying SQL instructions that Discoverer is using to display the current worksheet.

To view SQL created by Discoverer:

1. Choose Sheet | Show SQL to display the SQL Inspector dialog.

2. Display the SQL Inspector: SQL tab to look at the underlying SQL.

   
   
   Text description of the illustration view_sql.gif

3. (optional) To copy the SQL text into memory, click Copy.

   You can then switch to a different application and paste in the text. For example, you might want to paste this text into a text editor, edit the text, then save in a SQL file to that you can execute the file using SQL*Plus.

4. Click OK to close the SQL Inspector dialog and return to the worksheet.

Note:

• The SQL Inspector dialog might show a shortened version of the SQL that Discoverer sends to the RDBMS. Depending on how Discoverer is configured, inline views might be removed to make the SQL statement more legible. Contact the Discoverer manager for more details about how the SQL Inspector dialog is configured.

How to view a SQL execution plan

You view a SQL Execution Plan when you want to see the underlying instructions that Discoverer is sending to the server.

To view the execution plan used by Discoverer:

1. Choose Sheet | Plan to display the SQL Inspector dialog.

2. Display the SQL Inspector: Plan tab to look at the underlying execution plan.

   
   
   Text description of the illustration view_pln.gif

3. (optional) To copy the execution plan text into memory, click Copy.

   You can then switch to a different application and paste in the text.

4. Click OK to close the SQL Inspector dialog and return to the worksheet.

SQL Examples

Looking at an execution plan when using an Oracle 8 and later database

You can use the Execution Plan tab in the SQL Inspector dialog to see the SQL statement that Discoverer sends to the server.

Figure 18-10 SQL Inspector: SQL tab

Text description of the illustration mv1.gif

In the figure above, the worksheet contains the City and Profit SUM items. Although the Discoverer manager has created a Summary for these items, the SQL statement displayed in the SQL Inspector: SQL tab does not indicate whether a summary or materialized view is being used. To find out, look at the SQL Inspector: Plan tab (see figure below).

Figure 18-11 SQL Inspector: Plan tab

Text description of the illustration mv2.gif

In the figure above, the SQL Inspector Plan: Plan tab shows that a materialized view (called EUL4_MV101264) is being use to retrieve information from the database.

Hint: Names of Materialized views created by Discoverer are prefixed with the EUL name followed by 'MV' and the materialized view ID.