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
- Choose File | Open to display the "Open Workbook" dialog.
- Select the Database radio button to display the "Open Workbook from Database" dialog.
- Select the workbook that you want to analyze from the list of workbooks.
- 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":
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
- Choose File | Open to display the "Open Workbook from Database" dialog.
- Select the Open existing workbook button to display the Database dialog.
- Select the Database button to display the "Open Workbook from Database" dialog.
- Select the workbook that you want to analyze from the list of workbooks.
- Click Open to display the workbook.
- 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
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
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
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.
Note:
The Locale Selection tab is not available in the Discoverer Plus Options dialog.
|
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
- 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.
- 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.
- Click Apply Parameters. The Query Progress page displays until data is returned from the database.
Setting Worksheet Query Parameters with Discoverer Plus
- 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.
- 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.
- Click OK. A progress bar displays until data is returned from the database.
To Edit an Existing Worksheet Query Parameter with Discoverer Viewer
- Click the Edit Parameters link at the bottom of the parameters section of the worksheet report.
- Select a site to analyze from the Site drop-down list.
- 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.
- 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
- Select Edit Parameter Values from the Sheet menu.
- 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.
- 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.
- 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
- Select the Table Layout link. The Table Layout page appears.
- Order the report elements in the Page Items drop-down list as you want them to appear in the report.
- 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.
- Click Apply. A confirmation message appears.
Note:
The color setting for the rows and columns within a table conforms to Oracle standards and cannot be modified.
|
How to Modify Table Layout in Discoverer Plus
- Select Sheet | Table Layout
- Click the column you want to move and drag it to its destination within the worksheet.
- 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
- Click the Presentation Options link. The Presentation Options page appears.
- Select the page elements you want to appear on the page by clicking the check box associated with those items.
- Select the report elements you want to appear or disappear by selecting the appropriate radio button.
- Click Apply. The browser returns the last worksheet you selected.
Note:
Discoverer Plus does not have a Presentation Settings page. Interface formatting is set through several tabs in the Options dialog box and other dialog boxes.
|
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
- 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.
- Click your Internet browser's Print button or select File | Print.
To Print Worksheet Data in Discoverer Plus
- Select File | Print to launch the Print Wizard, or click the printer icon in the toolbar to launch your computer's Print dialog box.
- 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
Note:
Some formats may not be supported by your environment. If this is the case, an error message may appear indicating that your file format is not supported by your current environment.
|
To Export Worksheet Data from Discoverer Viewer
- Click the Export Worksheet Data link. The Export Format page appears.
- 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
- Select File | Save As to save the data.
- Enter a name for the file in the text field of the Save As window.
- Click Save. The Download Complete window appears.
- Click Open, Open Folder, or Close. The Download Complete window disappears.
To Export Worksheet Data from Discoverer Plus
- Select File | Export. The Export Wizard appears.
- 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
- 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.
- Select the text link or radio button of the level of detail you want to display.