Walkthrough: Creating a Parametric Sweep Workbook for the Job Submission Task Pane

Article
10/16/2013

This walkthrough demonstrates a basic parametric sweep workbook containing three simple formulas. The workbook takes two numbers as input, from which it calculates three separate values. Following are those values:

The two numbers added together
The first number subtracted from the second
The two numbers multiplied together

The workbook contains a list of values that are used as input parameters during the workbook calculation. Within the compute cluster nodes, Excel Services uses these parameters as values to the input cells of the workbook. Each of the compute cluster nodes uses a subset of the input parameters to calculate the workbook. The results from each calculation are then stored in the output cells within the workbook.

In this sample workbook, there are only two input parameters, three formulas, and ten rows of numbers to calculate. In practice, you can write a workbook with dozens or hundreds of input parameters (for example, interest rate, stock price, strike price, and so on), thousands of formulas, and hundreds or thousands of values within each parameter to calculate (for example, interest rates from 4% to 17% in increments of 0.01%, and so on).

Creating the Workbook

To create the workbook

Start Microsoft Office Excel 2007.
Enter the data into the workbook, as shown in Figure 1.

Figure 1. Sample workbook with data
Name each of the input cells. Select the cell to name, and then on the Formulas tab, click Define Name.
Name cell A2 Input1, as shown in Figure 2.

Figure 2. Naming cell A2
Name cell B2 Input2.

Note

Though naming ranges for inputs and outputs is optional, we recommend it. In addition to making it easier to write formulas, when you name ranges you can recognize them more easily in the Job Submission task pane and when you publish the workbook to a SharePoint site.

Specifying the Input Parameters

Add the input ranges to the Job Submission task pane by using the following steps.

To specify the input parameters

Select cell A2.
Click Capture Range in the Calculation Input Range section, as shown in Figure 3.

Figure 3. Selected range captured by clicking Capture Range button
Select cell B2.
In the Calculation Input Range, click Capture Range.

Specify the Calculation Results

Enter formulas under the Add, Subtract, and Multiply columns in cells D2, E2, and F2 by using the following steps.

To specify the calculation results

In cell D2, type =Input1+Input2.
In cell E2, type =Input1-Input2.
In cell F2, type =Input1*Input2.
Select D2:F2, and then name these cells OutputCells as shown in Figure 4.

Figure 4. Naming cells D2:F2
In the Calculation Input Range section, click Capture Range, as shown in Figure 5.

Figure 5. Capturing a range

Specifying the Input Range

The example workbook has a list of ten numbers to use as parameters for the parametric sweep. You specify these numbers as the input range as follows.

To specify the input range

Select the numbers under the Input Rows column, that is, ranges A5:B14, as shown in Figure 6.
In the Job Submission task pane, click Accept Input Range.

Figure 6. Accepting an input range by clicking Accept Input Range button

Specifying the Location to Store Output Results

After each calculation is performed, the calculation results are returned to the location you specify. You specify the location to store the output results as follows.

To specify the location to store output results

Select cell D5, and in the Job Submission task pane, click Accept Result Start Location.

Figure 7. Specifying the location to store output results

Save the Workbook to a SharePoint Document Library

You must place the workbook you want the compute cluster to access in a SharePoint document library. You can save a workbook to a SharePoint document library by using the following steps.

To save the workbook to a SharePoint document library

Click the Microsoft Office Button, point to Publish, and then click Excel Services, as shown in Figure 8.

Figure 8. Saving a workbook
Before saving the workbook, you must specify the input ranges. In the Save As dialog box, click Excel Services Options,****as shown in Figure 9.

Figure 9. Excel Services Options button
Click the Parameters tab, and then click Add.
In the Add Parameters list, select the Input1 and Input2 check boxes, as shown in Figure 10.

Figure 10. Adding parameters

The workbook is now ready to send to the compute cluster. Click OK.
In the Excel Services Options dialog box, click OK.
In the Save As dialog box, click Save.

Specifying the Compute Cluster Parameters

To submit the workbook to the compute cluster, the Job Submission task pane needs certain information about the compute cluster. You can determine most of these details from the compute cluster head node by using the following steps.

To specify the compute cluster parameters

In the Job Submission task pane, click the Configuration tab, and then click Apply Defaults.
In the Cluster Name dialog box, enter the name of the compute cluster head node, as shown in Figure 11.

Figure 11. Entering the name of the compute cluster

The necessary defaults are supplied automatically.
On the Advanced Settings tab, click Auto populate XML file path. The Job Submission task pane fills in the value for you based on the path to the SharePoint document library for this saved workbook, as shown in Figure 12.

Figure 12. Automatically populating the XML file path

Specifying the Credentials and Submitting the Job

All that remains to do is to set the credentials for the job, and submit it to the compute cluster as follows.

To specify the credentials for the job and submit it

On the Basic Settings tab, enter the User Name and Password, and then click Submit Job Now.

Figure 13. Submitting a job

The job is sent to the compute cluster. The results are returned to the workbook as they are calculated. Figure 14 shows an example of returned calculated results.

Figure 14. Example of returned calculated results