3
Working with Workbooks and Worksheets

To get the most out of Oracle9iAS Clickstream Intelligence Analytics (Clickstream Analytics) you should understand the form and function of workbooks and worksheets. In this chapter, you will learn about the following topics:

• What is a Workbook?: Explains the role of workbooks in Clickstream Analytics, and describes how to access workbooks using Oracle9iAS Discoverer Viewer (Discoverer Viewer) and Oracle9iAS Discoverer Plus (Discoverer Plus). This section also provides links to the detailed workbook explanations available in chapter four.

• What is a Worksheet?: Explains the role of worksheets in Clickstream Analytics, and describes how to select the appropriate worksheet for your data analysis needs using Discoverer Viewer and Discoverer Plus.

• Getting the Data You Want from Worksheets: Explains how to customize worksheet query parameters to return useful data from the Oracle9iAS Clickstream Intelligence database.

What is a Workbook?

A workbook is a collection of worksheets available in Clickstream Analytics. These workbooks are accessed with either Discoverer Viewer or Discoverer Plus. A workbook is similar to a folder in a filing cabinet. The only purpose it serves is to store files (worksheets) that contain related information. Each workbook name provides a description of the worksheets it contains.

Unlike a file cabinet folder, however, workbooks can only be opened one at a time. Opening a new workbook in Discoverer Viewer automatically closes the current workbook.

Viewing a Workbook with Discoverer Viewer

To view Oracle9iAS Clickstream Intelligence workbooks, select a workbook from the List of Workbooks page.

Viewing a Workbook with Discoverer Plus

1. Choose File | Open to display the "Open Workbook" dialog.

2. Select the Database radio button to display the "Open Workbook from Database" dialog.

3. Select the workbook that you want to analyze from the list of workbooks.

4. Click Open to display the workbook.

Workbooks Available in Oracle9iAS Clickstream Intelligence

Descriptions of the following workbooks are available in Chapter 4, "Understanding Worksheet Data":

• Activity Workbook

• Activity for New and Returning Users Workbook

• Activity for New and Returning Visitors Workbook

• Browsers and Platforms Workbook

• Connecting Machines Workbook

• Dimensional Analysis Workbook

• Path Analysis Workbook

• Referring URLs Workbook

• Referring URLs by Connecting Machines Workbook

• Referring URLs by Users Workbook

• Search Analysis Workbook

• Site Content Workbook

• Site Status Workbook

• Users Workbook

• User by Connecting Machines Workbook

• User by Pages Workbook

• Visitors Workbook

What is a Worksheet?

Worksheets enable you to answer specific questions about the Web log data you want to analyze. Each worksheet represents a unique query. Every time you open a worksheet in Discoverer Viewer or Discoverer Plus, it queries the database and retrieves the most current data. Worksheets are organized by topic in workbooks. For example, all worksheets relating to how users and visitors navigate through your site are contained in the Path Analysis workbook.

Selecting the Correct Worksheet

Viewing a Worksheet with Discoverer Viewer

To view a worksheet, select the workbook that contains the desired worksheet from the List of Workbooks page. Once the workbook opens, select the worksheet that contains the business intelligence data you need from the Side Navigation bar.

Viewing a Worksheet with Discoverer Plus

1. Choose File | Open to display the "Open Workbook from Database" dialog.

2. Select the Open existing workbook button to display the Database dialog.

3. Select the Database button to display the "Open Workbook from Database" dialog.

4. Select the workbook that you want to analyze from the list of workbooks.

5. Click Open to display the workbook.

6. Select a worksheet from the Worksheet tabs at the bottom of the interface.

Getting the Data You Want from Worksheets

To get the data you want from a worksheet, you must set parameters to control the scope of the query sent to the database. There are two ways to attach parameters to your database queries.

• Set overall parameters for every database query

• Modify the default query parameters attached to every available worksheet.

This section contains the following information on query parameters:

• Options for Modifying Database Queries

• Modifying Query Parameters for Individual Worksheets

Options for Modifying Database Queries

The overall database query is controlled by the settings you enforce through the Preferences page in Discoverer Viewer and through the Options dialog in Discoverer Plus. Option names on the Preferences page in Discoverer Viewer are usually replicated as tabs within the Discoverer Plus Options dialog. In cases where these names differ, both names (Discoverer Viewer/Discoverer Plus) are included in the section headings below.

• To access the Preferences page in Discoverer Viewer, click the Preferences button. It is located in the Global Buttons area at the top right of the interface.

• To access the Discoverer Plus Options dialog, Choose Tools | Options.

The parameters described in the following sections are ordered by their placement on the Preferences page in Discoverer Viewer. For more information on the parameters below, see the Oracle9iAS Discoverer Plus User's Guide.

Query Governor

Use this setting to limit query run times, data retrieval times, and the amount of data returned by a query. Table 3-1, "Query Governor Settings" describes the available options.

Table 3-1 Query Governor Settings

Parameter	Description
Warn me if predicted query time exceeds:	Enter the number of seconds a query can run before the system warns you of the query's estimated time to complete. The default is 15 seconds. To enable this parameter, select the associated check box.
Prevent queries from running longer than:	Enter the number of seconds a query can run before you want the system to warn you that the query cannot run because it will take too long to return data from the database. The default is 1800 seconds. To enable this parameter, select the associated check box.
Limit retrieved query data to:	Enter the maximum number of rows a database query can return to you in its report. The default is 10000 rows. To enable this parameter, select the associated check box.
Retrieve data incrementally in groups of:	Enter the maximum number of rows of data per group returned by a query of the database. The default is 500 rows.
Cancel list-of-values retrieval after:	Enter the length of time it can take for a list of values to be returned from the database before the request is cancelled. The default is 60 seconds.
Run query automatically:	This option enables Discoverer Viewer worksheet queries to run automatically after specific worksheet query parameters are set by the user. This is the default setting. In Discoverer Plus, this setting is found on the General tab of the Options dialog box. To enable this parameter, select the associated radio button.
Ask for confirmation:	This option enables the system to return a page to the user that asks if the database query is necessary. In Discoverer Plus, this setting is found on the General tab of the Options dialog box. To enable this parameter, select the associated radio button.
Don't run query:	This option suspends all database queries. In Discoverer Plus, this setting is found on the General tab of the Options dialog box. To enable this parameter, select the associated radio button.

Worksheets/Sheet Format

Use this setting to configure the way in which a worksheet query operates. Table 3-2, "Worksheet Settings" describes the available options.

Table 3-2 Worksheet Settings

Parameter	Description
Number of rows per page:	Enter the maximum number of rows of data, per worksheet page, a database query should display. The default is 25 rows.
Show null values as:	Enter the value the database should return when NULL appears in the data requested by a user. The default is NULL.
Show values that cannot be aggregated as:	Enter the value that the database should return when values that cannot be aggregated exist in the data requested by a user. There is no default.

Summary Data

Use this option to set how worksheet queries get data from available summary tables within the database. Choose an option by selecting its radio button. In Discoverer Plus, this setting is found on the Query Governor tab of the Options dialog box. The three available settings are listed below:

• Always, when available. This option makes sure that all queries first access the summary tables available within the database

• Only when summary data is not out of date (stale). This option makes sure that all queries first access the summary tables when data within those tables are current. If a summary table contains non-current data, that table is not queried.

• Never. This option makes sure that all queries ignores available summary tables and access data directly from the database.

Fan Trap Detection/Advanced

Use this option to enable or disable fan trap detection. A fan trap is a database error. Fan traps occur when data items in two folders have an indirect relationship based on their connections to data items in a third folder and a query of the data items produces unintentional results. Fan trap detection is selected by default. It enables Oracle9iAS Discoverer to detect and resolve queries that return ambiguous results. If a fan trap error cannot be resolved by Oracle9iAS Discoverer, the worksheet query does not complete and an error message displays. Do not disable this feature without first discussing it with the Oracle9iAS Discoverer administrator.

End User Layer Access/EUL

Use this option to select a default End User Layer (EUL) for Oracle9iAS Clickstream Intelligence to use each time you connect to the database that stores your Web log data. An EUL is a collection of tables in the database that controls your ability to use workbooks. Your ability to view EULs is controlled by the Oracle9iAS Clickstream Intelligence Database Administrator (DBA) or System Administrator. Select an EUL from those available in the drop-down list. For more information on EULs, see the Oracle9iAS Discoverer User's Guide or the Oracle9i Discoverer Administrator Administration Guide.

Locale Selection

Use this option to set the language type used within the Discoverer Viewer interface. The default is set to the official, or accepted, language of your country. To change the language type, select a locale from the drop-down list. Twenty-eight locales are available.

Modifying Query Parameters for Individual Worksheets

Each worksheet has a specific set of parameters you can modify.

• In Discoverer Viewer, these parameters appear in the Content area after you select a worksheet from the Side Navigation bar.

• In Discoverer Plus, these parameters appear in the Edit Parameter Values dialog box after you select a workbook from the Open Workbook from Database dialog box.

The type of data returned from the database depends entirely on the query parameters you set. For more information, see the Oracle9iAS Discoverer Plus User's Guide.

Setting Worksheet Query Parameters with Discoverer Viewer

1. Select a site to analyze from the Site drop-down list. These sites are created by your DBA with the Oracle9iAS Clickstream Intelligence Runtime Administrator.

2. Select a start date and an end date for your analysis in the Date text fields by clicking the flashlight icon and then selecting the appropriate dates from the searchable pop-up list. Certain worksheets require a start date only. In these cases, the end date field is not displayed.

3. Click Apply Parameters. The Query Progress page displays until data is returned from the database.

Setting Worksheet Query Parameters with Discoverer Plus

1. Select a site to analyze from the Site drop-down list in the Edit Parameter Values dialog box. These sites are created by your DBA with the Oracle9iAS Clickstream Intelligence Runtime Administrator. If you do not know the site name, select the downward arrow button to the right of the text field to display a pop-up list of all available sites.

2. Select a start date and an end date for your analysis from the Date text fields in the Edit Parameter Values dialog box. Use the format DD-MON-YY. In certain cases, a single Date text field displays. If you do not know what dates to enter, select the downward arrow button to the right of the text field to display a searchable pop-up list of all available dates.

3. Click OK. A progress bar displays until data is returned from the database.

To Edit an Existing Worksheet Query Parameter with Discoverer Viewer

1. Click the Edit Parameters link at the bottom of the parameters section of the worksheet report.

2. Select a site to analyze from the Site drop-down list.

3. Select a start date and an end date for your analysis in the Date text fields by clicking the flashlight icon and then selecting the appropriate dates from the searchable pop-up list. Certain worksheets require a start date only. In these cases, the end date field is not displayed.

4. Click Apply Parameters. The Query Progress page displays until data is returned from the database.

To Edit an Existing Worksheet Query Parameter with Discoverer Plus

1. Select Edit Parameter Values from the Sheet menu.

2. Select a site to analyze from the Site drop-down list in the Edit Parameter Values dialog box. If you do not know the site name, select the downward arrow button to the right of the text field to display a pop-up list of all available sites.

3. Select a start date and an end date for your analysis from the Date text fields in the Edit Parameter Values dialog box. Use the format DD-MON-YY. In certain cases, a single Date text field displays. If you do not know what dates to enter, select the downward arrow button to the right of the text field to display a searchable pop-up list of all available dates.

4. Click OK. A progress bar displays until data is returned from the database.

Making a Worksheet Look The Way You Want

After your query completes, the worksheet appears in the Content area in Discoverer Viewer or the Work area in Discoverer Plus.

• In Discoverer Viewer: The layout of data in worksheets can be modified through two sets of formatting links. The first set appears above the report data. These links redirect you to pages that enable you to control how data is formatted before it displays on your browser. The second set of formatting links appears to the left of each report element. These links determine if a report element is displayed or hidden from view.

• In Discoverer Plus: The layout of worksheet data is configured through dialogs and Wizards that control specific formatting options. For example, the Export dialog enables you to export worksheet data in the file type you specify. You can access all formatting dialogs from the Menu bar. The following formatting options for both Discoverer Viewer and Discoverer Plus are available:

• Table Layout

• Presentation Options: Available only in Discoverer Viewer.

• Printing Worksheet Data

• Exporting Worksheet Data

For a complete description of how to modify and format worksheet data in Discoverer Viewer and Discoverer Plus, see the Oracle9iAS Discoverer Plus User's Guide.

Table Layout

Use a worksheet's Table Layout option to modify the way in which data returned by a database query is displayed.

How to Modify Table Layout in Discoverer Viewer

1. Select the Table Layout link. The Table Layout page appears.

2. Order the report elements in the Page Items drop-down list as you want them to appear in the report.

3. Select an axis point from the Top Axis drop-down list. This step is optional. If you do not want an axis point, leave the field blank.

4. Click Apply. A confirmation message appears.

How to Modify Table Layout in Discoverer Plus

1. Select Sheet | Table Layout

2. Click the column you want to move and drag it to its destination within the worksheet.

3. Click OK.

Presentation Options

The Presentation Options settings in Discoverer Viewer control the items that appear in the user interface. These items are not workbook or worksheet specific. If you make a change using the Presentation Options page, that change will be reflected throughout Discoverer Viewer.

To Modify the Presentation of the Discoverer Viewer Interface

1. Click the Presentation Options link. The Presentation Options page appears.

2. Select the page elements you want to appear on the page by clicking the check box associated with those items.

3. Select the report elements you want to appear or disappear by selecting the appropriate radio button.

4. Click Apply. The browser returns the last worksheet you selected.

Printing Worksheet Data

The Printable Page button in Discoverer Viewer and the Print Wizard in Discoverer Plus enables you to create a printer-friendly version of the data returned by a database query.

To Print Worksheet Data in Discoverer Viewer

1. Click the Printable Page button located at the bottom of the Web page. A printer-friendly version of the table you wish to print appears in a new browser window.

2. Click your Internet browser's Print button or select File | Print.

To Print Worksheet Data in Discoverer Plus

1. Select File | Print to launch the Print Wizard, or click the printer icon in the toolbar to launch your computer's Print dialog box.

2. Follow the steps in the Print Wizard or Print dialog box to format and print your worksheet(s).

Exporting Worksheet Data

The Exporting Worksheet Data link in Discoverer Viewer and the Export Wizard in Discoverer Plus enable you to export the data returned from your database query and save it as a file. The following file types are available:

• Microsoft Excel Worksheet (.xls)

• Text - tab delimited (.txt)

• Hypertext Markup Language document (.htm)

• CSV - comma delimited (.csv)

• Formatted Text (.prn)

• Express Format (.dcs)

• Data Interchange Format (.dif)

• Symbolic Link (.slk)

• Lotus 1-2-3 (.wks)

• XML (.xml) - XML is available only with Discoverer Plus

To Export Worksheet Data from Discoverer Viewer

1. Click the Export Worksheet Data link. The Export Format page appears.

2. Select the file format appropriate to your needs from the drop-down list. One of the following may occur:
   • the worksheet opens in an application embedded in the browser

   • the worksheet opens in an application

   • a dialog displays that enables you to save the worksheet in the exported format

3. Select File | Save As to save the data.

4. Enter a name for the file in the text field of the Save As window.

5. Click Save. The Download Complete window appears.

6. Click Open, Open Folder, or Close. The Download Complete window disappears.

To Export Worksheet Data from Discoverer Plus

1. Select File | Export. The Export Wizard appears.

2. Follow the steps in the Export Wizard to create and save a new file from the selected worksheet(s).

Drilling into Worksheet Data

Both Discoverer Plus and Discoverer Viewer provide a way to look at Clickstream Analytics worksheet data in greater or lesser detail. All worksheet columns that contain multiple levels of data in Discoverer Plus are marked with an right arrow icon. When you select the drill icon a pop-up list displays all levels of data available in the column as well as the current level of data displayed. In Discoverer Viewer, all worksheet columns that contain multiple levels of data are marked with a plus sign (+) in a right arrow or a minus sign (-) in a downward arrow. When you click the drill icon a pop-up list displays the levels of data available in the column and highlights the current level.

There are two types of drilling - drilling down, and drilling up. Drilling down displays data in more detail. For example, in the Top Browsers worksheet, you can drill down into the All Browsers column to see information about the type of operating system used to run the browsers that accessed your Web site, or information about the specific types of browsers that accessed your Web site. Drilling up collapses the information displayed in a column to the level of data you select. For example, you can collapse the Client Name column of the Top Browsers worksheet to either Client Type or All Browsers. Each of these columns contains less specific data than the Client Name column.

To Drill Into Columns Containing Multiple Data Levels

1. Click the drill icon in Discoverer Viewer or the right arrow icon in Discoverer Plus to display the data available within the hierarchy.
   • For example, clicking the drill icon or right arrow icon in the All Statuses column displays a pop-up list containing Description, Status Name, Status Type, and Status Code.

2. Select the text link or radio button of the level of detail you want to display.