Job Submission Task Pane Add-in

  • Article

The Job Submission task pane add-in for Microsoft Office Excel 2007 enables to schedule or immediately submit calculations to a set of Microsoft Office SharePoint Server 2007 servers running Excel Services within a Windows Compute Cluster Server (CSS) 2003 cluster.

Note

This developer guide assumes that you are familiar with Excel Services and Windows CCS 2003. For more information about both, see Additional Resources.

The Job Submission task pane add-in, customized for Excel 2007, prepares Excel workbooks for the Windows CCS and submits them for calculation.

Benefits of Using Windows Compute Cluster Server 2003

Using Windows CCS 2003 to calculate Excel workbooks provides several benefits, as described in the following table.

Benefit Description

Removal of complex jobs from desktop computers

Large, long-running workbook calculations take much time to execute on desktop computers. By using the Job Submission task pane, workbook calculations can be offloaded to the compute cluster and free the desktop computer to perform other tasks.

Improved reliability of mission-critical calculations

Long-running workbooks are often run after hours on desktop computers so they can be ready when users return to work the next day. However, if the workbook failed to calculate, for example, due to a power outage or other issue, then productivity is lost. You can use the Job Submission task pane to schedule workbooks to run within a compute cluster. If an error occurs on the compute cluster node, you can configure the workbook to automatically resubmit it for calculation on another available node.

Improved performance of parallel iterations and long-running calculations

Businesses conduct complex, time-consuming calculations. Examples such as a Monte Carlo simulation, or parametric sweeps, can iterate through hundreds, thousands, or sometimes millions of values resulting in calculations that can take from minutes to hours to complete. The Job Submission task pane enables workbooks to take advantage of parallel computing by running on several compute nodes in the compute cluster simultaneously. This results in vastly reduced calculation times.

Scheduled and Immediate Calculation

The Job Submission task pane add-in can calculate Excel workbooks in two different ways: as a scheduled or an immediate job.

Within Excel 2007, you can choose the scheduled or immediate calculation method from the Job Submission task pane.

The following table describes each job type.

Job type Description

Scheduled

Submitted to the compute cluster head node at a specific date and time, for example, at the end of the month for month-end reports, or during times when demands on the compute cluster are few. At the scheduled time, the workbook is submitted to the compute cluster and calculated, and the calculated data is saved to a new worksheet. Because the jobs are managed by the Compute Cluster Job Manager, the jobs gain the benefits of security and failover recovery. Each job is monitored for failure. If the job fails, the job is resubmitted until it completes.

Advantages of scheduled jobs:

  • Robustness

  • Failover, security

  • Ability to be reliably scheduled for calculation at a specific date and time

  • Creation of a workbook to store the results of job's calculations

Disadvantages of synchronous jobs:

  • Can experience delay in beginning the job

  • Results are not returned to the user in real time

Immediate

Sent immediately to the compute cluster head node. Data back is received back into the originating workbook in real time.

Advantages of asynchronous job:

  • Job begins quickly

  • Results are returned as soon as completed to the currently open workbook

Disadvantages of asynchronous jobs:

  • Greater demand for immediate results from the compute cluster, which can be servicing many users

  • Results might overwrite existing data in the workbook

Prerequisites to using the Job Submission Task Pane Add-in

You must meet several software and workbook prerequisites before an Excel workbook can be processed by the Job Submission task pane add-in. You must install the following:

  • CCS 2003   Installed on the server, which must be available for use; for example, the user must have permissions to schedule jobs to the compute cluster.

  • Office SharePoint Server 2007  Installed in these locations:

    • On the Windows CCS 2003 head node.

    • On each compute node that is used for compute cluster calculations.

  • Job Submission task pane add-in   Installed in Excel 2007 on the client computer.

  • Compute cluster assembly (CCPAPI.DLL)   Installed on the client computer. Allows the Job Submission task pane add-in to interact directly with the compute cluster.

    Note

    For more information about the compute cluster assembly (CCPAPI.DLL), see Microsoft Compute Cluster Pack.

In addition, you need the following:

  • An Excel workbook ready to be processed by the compute cluster. For immediate jobs, the workbook is usually a parametric sweep or a Monte Carlo simulation. For details about preparing a workbook for the Job Submission task pane, see, Walkthrough: Creating a Parametric Sweep Workbook for the Job Submission Task Pane.

  • A CCS Job XML file to submit the job to the compute cluster head node. This XML file is created by the Job Submission task pane add-in.

Job Submission Task Pane Process

After you meet all prerequisites, the Job Submission task pane add-in is ready to use to submit the workbook to the compute cluster head node. After the user clicks Submit Job Now on the Job Submission task pane, the following steps are performed to calculate the Excel workbook on the compute cluster.

Preparing the Job

The following steps are performed to submit the Excel workbook to the compute cluster.

To prepare the job

  1. Verify the following file share resources to ensure correct creation and storage of the XML job files that are used by Excel Services on the compute nodes:

    1. Local file path

    2. Result output location

    3. Compute cluster connectivity and permissions

    4. UNC working directory

  2. Set credentials for the job and for compute cluster nodes.

  3. Divide the parametric sweep or Monte Carlo input rows into separate compute tasks, each with a task XML file. The Job Submission task pane add-in determines the number of rows to calculate for each compute task by the available jobs and the Input Divisor value.

    • If Input Divisor is blank, the Job Submission task pane add-in divides the rows evenly among the compute nodes. For example, if there are 10,000 rows to calculate, and 20 compute nodes are available, then each compute task calculates 500 rows at a time.

    • If Input Divisor is not blank, the rows in each compute node are divided into the number of compute tasks specified by the Input Divisor. For example, if there are 10,000 rows to calculate, and 20 compute nodes are available, then each compute node must calculate 500 rows to complete the job. If the Input Divisor is set to 5, then there are five compute tasks for each compute node, and each compute task calculates 100 rows at a time.

    Due to the overhead of starting and stopping the additional compute tasks, higher Input Divisor values slightly increase the overall time a compute job requires. However, these additional compute tasks greatly increase the interactivity of immediate jobs because smaller compute tasks calculate faster and update the Excel workbook more frequently than larger compute tasks.

  4. For a scheduled job, do the following:

    1. Create one additional compute task to collect the results created by the previous compute tasks.

      Note

      For immediate jobs, the Job Submission task pane add-in collects the results as they become available.

    2. Create the CCS Job XML file.

    3. Add a new Windows Task Scheduler task to the compute cluster head node.

    4. Execute the Windows Task Scheduler task on the compute cluster head node.

    The scheduled Windows Task Scheduler task creates and begins the CCS job on the head node. The job is then passed to the Compute Cluster Job Manager.

Executing the Job on the Compute Cluster Nodes

The head node of the compute cluster manages and controls the jobs and compute tasks for the compute cluster. To perform the calculations on the Excel workbook, the head node and compute node do the following:

  1. The head node checks for an available compute node on the compute cluster.

  2. When a compute node becomes available, the head node submits a compute task to it.

  3. The head node allocates one compute task to each available compute node on the cluster.

  4. The compute node performs its compute task, and then notifies the head node when it is finished and ready for a new task.

  5. The head node continues allocating compute tasks and monitoring compute nodes until all compute tasks are complete.

Performing Excel Calculations

Each compute node performs the following steps:

  1. Creates a connection to the Excel Calculation Service on each server running Office SharePoint Server 2007 on each compute node.

  2. Opens the workbook from the server running Office SharePoint Server 2007.

  3. Loops through the following steps until the compute task is complete.

    1. Within the Excel workbook, copies the assigned rows of input data to the input ranges or cells on the workbook.

    2. Performs the calculations by using the Excel Calculation Service.

    3. Stores the results of the Excel calculations in the XML workbook files on the head node.

      Note

      The output data is stored on the head node, not in the workbook.

    4. Repeats the previous steps for each set of inputs.

Collecting the Results

For parametric sweeps and Monte Carlo simulations, each compute cluster task calculates only a part of the solution. To assemble the fully calculated workbook, the individual results must be collected. This collection process differs depending on whether the job was submitted as a scheduled or an immediate job.

Scheduled Job Result Collection

For scheduled jobs, results collection is a separate and final compute task of the job. This final compute task performs the following:

  1. Opens a new instance of the template workbook.

  2. Reads the result data from the object model serialized XML files.

  3. Sets the values of the results in the workbook to the values in the result XML files.

  4. Saves the workbook to a SharePoint document library that resides on a different server running Office SharePoint Server 2007 that is not running in the compute cluster.

At the end of the job, the new workbook with the complete results of the cluster server calculations is available in a SharePoint document library.

Immediate Job Result Collection

For immediate jobs, results collection is not a separate compute task. Instead, the Job Submission task pane add-in obtains the calculated results as each compute node completes each task.

This immediate job results collection is performed in the following sequence:

  1. The head node is checked for newly completed compute tasks.

  2. The results of the new compute task calculation are sent to the Job Submission task pane add-in on the client computer.

  3. The Job Submission task pane add-in updates the workbook with the results of the compute node calculations.

Note

The head node is queried until new tasks are found, the final task is complete, or the timeout value is exceeded. You can set the timeout value on the Settings tab of the Job Submission task pane. It is important to set the timeout to a high enough value to allow the longest running compute task to complete.

See Also

Concepts

Performing a Parametric Sweep using the Job Submission Sample
Overview of Excel Calculation Services on Compute Cluster Server API
Walkthrough: Creating a Parametric Sweep Workbook for the Job Submission Task Pane
How to: Schedule a Workbook Calculation by using the Job Submission Task Pane
How to: Schedule or Submit a Workbook by using a Custom Web Form
Hardware and Software Requirements for the Job Submission Sample
Installing a Compute Cluster for the Job Submission Sample
Manually Installing the Job Submission Sample
Additional Resources