9
Scheduled Workbooks

This chapter consists of the following sections:

• 9.1 Introduction

• 9.2 What Happens When a Workbook is Scheduled?

• 9.3 Enabling Scheduled Workbooks for a User or Role

• 9.4 Viewing Information About a Scheduled Workbook

• 9.5 Viewing an Error Message incurred by a Scheduled Workbook

• 9.6 Editing a Scheduled Workbook

• 9.7 Deleting a Scheduled Workbook's Result Set from the Database

• 9.8 Removing a Scheduled Workbook from the Process Queue

9.1 Introduction

Workbook scheduling is useful in the following situations:

• The user has created a report that could take a long time to run - they can submit the report to run overnight and view the result in the morning.

• The user wants to create a report that needs to be updated at regular intervals.

To schedule a workbook, there are several prerequisites that must be met. For more information, see Section 9.3, "Enabling Scheduled Workbooks for a User or Role."

When a Workbook is scheduled, the user selects a particular workbook, sheet, or set of sheets submitted as a report, and requests that the report be processed at a particular date, time, and frequency. The output (or result set) is stored in the database until such time as the user requires it (or until it expires). The user can then load it into the workbook associated with the processing.

Discoverer supports workbook processing from both the client and server sides.

• Server-side workbook processing enables the user to schedule a workbook, which is then sent to the server and processed there. This enables the user to switch off the client machine and view the results of the scheduled workbook request at any time.

• Client-side workbook processing enables the user to run a workbook or sheet in the background and print or export results directly. To perform client-side workbook processing, you use the command line interface.

  NOTE: Scheduled workbooks are not exported when you export a business area.

9.2 What Happens When a Workbook is Scheduled?

When a scheduled workbook is requested, the following actions occur:

1. Discoverer Plus ensures that scheduling the workbook would not result in the user exceeding their limit on the number of scheduled workbooks.

   This limit is set by the Discoverer Administrator and restricts the number of scheduled workbooks that can be maintained at any one time. For more information, see Section 8.5, "Specifying Scheduled Workbook Limits."

   This limit is separate from the job_queue_processes value in the Oracle initialization file, which controls the maximum number of jobs that can be run at any one time on the server.

   If the user exceeds their limit Discoverer Plus displays a message and the workbook is not scheduled.

2. The scheduled workbook request is stored in the DBMS_JOBS table within the Oracle kernel.

3. The Job Queue process wakes up and submits the next job in the queue.

   The length of time that the Job Queue process sleeps for is specified using the job_queue_interval value in the Oracle initialization file. For more information, see Section 2.1.3, "Setting the Start Time for Workbook Processing."

   The scheduled workbook is processed entirely on the server.

4. A result set table is created in the database and the table is populated with the result set of the scheduled workbook.

   The result set is stored under the schema specified by the Discoverer Administrator. For more information, see Section 8.5, "Specifying Scheduled Workbook Limits."

5. When the process is complete, the user views the result set.

6. The user can then delete the result set (if it is no longer required) at which point the table is dropped.

   The Discoverer Administrator can also specify how long the results of a user's scheduled workbook are allowed to exist in the database before they are automatically deleted. For more information, see Section 8.5, "Specifying Scheduled Workbook Limits."

   NOTE: If the elements of the EUL, used by a scheduled workbook, are changed between the time when the workbook is scheduled and the time when the display of the result set, the scheduled workbook's status is set to "EUL has changed".

9.3 Enabling Scheduled Workbooks for a User or Role

This section describes how to enable a user to schedule workbooks.

In order for a user to be able to schedule a workbook, the following prerequisites must be met:

1. Ensure that the Scheduled Workbooks feature is enabled.

   For more information, see Section 2.1, "Scheduled Workbooks."

2. Grant the Schedule Workbooks privilege to the user.

   For more information, see Section 8.5, "Specifying Scheduled Workbook Limits."

3. The schema, that is to own the scheduled workbook's result set, must be granted the following database privileges:
   • Create Procedure

   • Create Table

   • Create View

   For more information, see Section 2.1.2, "Specifying Result Set Storage."

9.4 Viewing Information About a Scheduled Workbook

This section describes how to view information about a scheduled workbook. Such information includes:

• The user ID of the person who scheduled the workbook.

• The scheduled workbook's status.

• The date and time of when the workbook was last run.

• The date and time of when the workbook is next due to run.

• The length of time the workbook took to run.

• A description of the workbook.

To view this information:

1. Choose Tools | Manage Scheduled Workbooks.

   This opens the Manage Scheduled Workbooks dialog box (see Figure 9-1).

Figure 9-1 Manage Scheduled Workbooks

2. Select the user or role, whose scheduled workbooks you want to display in the list (using the Show workbooks for drop-down list).

   If you want to show all scheduled workbooks, select All Users from the drop-down list.

3. To re-sort the list, click the relevant column heading.

4. If you want to view the description for a specific scheduled workbook, select the relevant scheduled workbook in the list.

   If the scheduled workbook has a description, it will appear in the Description field.

5. To refresh the information on this dialog box, click Refresh.

6. When you have finished, click Close.

For more information about this dialog box (including the meanings of each run status) click Help.

9.5 Viewing an Error Message incurred by a Scheduled Workbook

This section describes how to view the error message that describes why a scheduled workbook failed to run.

1. Choose Tools | Manage Scheduled Workbooks.

   This opens the Manage Scheduled Workbooks dialog box (see Figure 9-1).

2. Select the user or role, whose scheduled workbooks you want to display in the list (using the Show workbooks for drop-down list).

   If you want to show all scheduled workbooks, select All Users from the drop-down list.

3. (Optional) Click the Status column heading.

   This re-sorts the list by status, which makes it easier to find all the scheduled workbooks that have their status set to Error while running query.

4. Select the scheduled workbook whose error message you want to view.

5. Click View Error....

   This button is only available for scheduled workbooks with their status set to Error while running query.

Discoverer Administration Edition displays the error message that describes why the scheduled workbook failed to run.

9.6 Editing a Scheduled Workbook

This section describes how to edit a scheduled workbook.

1. Choose Tools | Manage Scheduled Workbooks.

   This opens the Manage Scheduled Workbooks dialog box (see Figure 9-1).

2. Select the user or role, who owns the scheduled workbook you want to edit (using the Show workbooks for drop-down list).

   If you want to show all scheduled workbooks, select All Users from the drop-down list.

3. Select the scheduled workbook that you want to edit.

4. Click Edit....

   This opens the Schedule Workbook wizard. This wizard works in exactly the same way as the Discoverer Plus wizard of the same name. For more information, see your User's Guide.

9.7 Deleting a Scheduled Workbook's Result Set from the Database

This section describes how to delete a scheduled workbook's result set from the database.

1. Choose Tools | Manage Scheduled Workbooks.

   This opens the Manage Scheduled Workbooks dialog box (see Figure 9-1).

2. Select the user or role, who owns the scheduled workbook you want to delete (using the Show workbooks for drop-down list).

   If you want to show all scheduled workbooks, select All Users from the drop-down list.

3. Select the scheduled workbook that you want to delete.

4. Click Delete

   This marks the selected workbook's result set for deletion. The workbook's status is changed to Report deleted by administrator. The report is actually deleted when the workbook's owner next exits Discoverer Plus.

9.8 Removing a Scheduled Workbook from the Process Queue

This section describes how to prevent a scheduled workbook from running by removing it from the process queue.

1. Choose Tools | Manage Scheduled Workbooks.

   This opens the Manage Scheduled Workbooks dialog box (see Figure 9-1).

2. Select the user or role, who owns the scheduled workbook you want to remove (using the Show workbooks for drop-down list).

   If you want to show all scheduled workbooks, select All Users from the drop-down list.

3. Select the scheduled workbook that you want to remove.

4. Click Unschedule....