Using the Excel Cluster Connector to Offload UDFs

Updated: July 2010

Applies To: Windows HPC Server 2008 R2

Windows HPC Server 2008 R2 and Microsoft Excel 2010 support offloading user-defined functions (UDFs) that are implemented in an XLL file on the cluster. Offloading UDFs provides increased processing power and speed for long-running, calculation-intensive UDFs. The HPC Pack 2008 R2 client utilities include and add-in for Excel (the Excel Cluster Connector) that acts as a proxy between Excel and the cluster. When you install the HPC client utilities, you can select the connector from a drop-down list on the Advanced tab in Excel Options. This topic describes how to enable and configure the Excel Cluster Connector to offload UDFs.

Before submitting UDF offloading jobs to a cluster, review Requirements for UDF offloading.

Note

When you enable UDF offloading for your workbook, the Excel Cluster Connector submits a job to the cluster with your credentials, sends calculation requests, and receives calculation results. The performance improvements that you experience can vary according to how soon your job starts running and how many resources are allocated to your job. Scheduling and allocation policies are defined by the cluster administrator.

This topic includes the following sections:

• Use the Excel Cluster Connector to offload UDFs

• Save the cluster settings to the workbook

• Specify a custom service for UDF offloading

• Important considerations

• Additional references

Use the Excel Cluster Connector to offload UDFs

The following procedure describes how to enable and configure the Excel Cluster Connector and how to submit calculation requests to the cluster. For a list of requirements, see Requirements for UDF offloading.

To use the Excel Cluster Connector to offload UDFs

1. Run Excel 2010 and open your workbook.

2. In the File menu, click Options. The Excel Options dialog box appears.

3. Select the Advanced tab, and then scroll to the Formulas section.

   The following screenshot illustrates the Excel Options dialog box.

   

4. To enable the Excel Cluster Connector, select the Allow user-defined XLL functions to run on a compute cluster check box.

   Note
   When the Excel Cluster Connector is enabled, Excel attempts to offload any UDFs that are registered as cluster-safe. This setting is not limited to a specific workbook.

5. In Cluster type, select the Microsoft HPC Server 200 R2 (x86) or the Microsoft HPC Server 200 R2 (x64) connector. The version of the connector that appears in the drop-down list is determined by the version of Excel that you are using. A 32-bit version of Excel loads the x86 connector, and a 64-bit version of Excel loads the x64 connector.

6. To configure the Excel Cluster Connector, click Options. The Excel Cluster Connector for Microsoft HPC Pack 2008 R2 dialog box appears.

   The following screenshot illustrates the Excel Cluster Connector dialog box:

   

7. Type the name of the cluster head node.

8. Type the name of the job template to use. If you leave the job template field blank, the default template is used.

   Clusteradministratoren können Berechtigungen dazu angeben, welche Benutzer eine bestimmte Auftragsvorlage verwenden können. Informationen zu den Auftragsvorlagen, auf die Sie zugreifen können und die Sie verwenden sollten, erhalten Sie von Ihrem Clusteradministrator.

9. Optionally, select the Save these settings to the current workbook check box. For more information, see Save the cluster settings to the workbook.

10. Click Apply.

11. Run a calculation in the worksheet.

When the calculations are submitted to the cluster, a dialog box appears that indicates requests are being sent. When the job starts running, you will begin to receive calculation results. The following screenshot illustrates the calculation progress dialog box:

Important

If you click your Excel workbook while calculations are in progress, the calculations are canceled. To help avoid an accidental cancelation, the Excel window is disabled while the calculation progress dialog box is open. The dialog box closes when the calculations complete successfully. If there are any errors, the dialog box reMayns open to display the error message. In some cases the Excel Cluster Connector is not notified that calculations are complete, and the dialog box does not close automatically. If the dialog box indicates that all messages have been received, you can close the dialog box by clicking the Close button (X). If you click Cancel Calculation after the calculation is complete, you might have to restart Excel before you can use the Excel Cluster Connector again.

Save the cluster settings to the workbook

The cluster connector dialog box includes an option to save the head node and job template settings to your workbook. This allows you to associate a workbook with a specific cluster and a specific job template. You can set and edit these properties through the Excel Cluster Connector dialog box, or you can edit them through the advanced properties for your workbook.

To edit the advanced properties for your workbook

1. Open your workbook in Excel 2010.

2. Click File, and then click the Info tab.

3. Click the down-arrow next to Properties, and then select Advanced Properties from the drop-down list. The Properties dialog box appears.

4. Click the Custom tab.

5. If you saved the cluster settings to the workbook, the Properties dialog box displays the HpcHeadNode and HpcJobTemplate settings, as illustrated by the following screenshot.

   

6. To edit a value, for example the HpcJobTemplate value, use the following steps:

   1. In the Properties table, click HpcJobTemplate. The name and value appear in the corresponding text boxes.

   2. In the Value text box, type the name of the job template that you want to use, and then click OK.

Note

If you save your settings to the workbook, and you do not specify a value for the job template, the Excel Cluster Connector saves a value of “default” for the HpcJobTemplate property. The string “default” is saved in the language that Excel is running in. If the Excel Cluster Connector is running in a different language than the language in which the workbook settings have been saved, the connector will not recognize the template name. If this occurs, the Excel Cluster Connector reports a “SessionCreation” error, and the event log indicates that the job template was not valid.

Specify a custom service for UDF offloading

By default, the cluster connector uses the Microsoft.Hpc.Excel.XllContainer32 or the Microsoft.Hpc.Excel.XllContainer64 services to host UDF calculations on the cluster. In some cases, the cluster administrator might create an alternate service registration file with an alternate configuration. If you must use a custom service configuration to offload UDFs, you can specify the custom service name in the advanced properties for your workbook. To specify a custom 32-bit service, add a property named HpcServiceName32. To specify a custom 64-bit service, add a property named HpcServiceName64.

As an example, the following procedure describes how to specify a custom 64-bit service:

To configure a workbook to use a custom service

1. Open your workbook in Excel 2010.

2. Click File, and then click the Info tab.

3. Click the down-arrow next to Properties, and then select Advanced Properties from the drop-down list. The Properties dialog box appears.

4. Click the Custom tab.

5. In Name, type HpcServiceName64.

6. In Type, select Text.

7. In Value, type the name of your custom service.

8. Click OK.

Important considerations

• For UDF offloading, if a cell references too many other cells (for example, if a cell references an entire column), the serialized data result might be larger than the 64 KB limit on message size. In this case, the cluster connector returns a value of #N/A to the cells, and the calculation progress dialog box displays an error message from Excel.

• If you uninstall the Excel Cluster Connector (in the HPC Pack 2008 R2 client utilities), ensure that you also disable the Excel Cluster Connector in the Excel Options dialog box.

• If you enable and configure the Excel Cluster Connector, and then you change versions of Excel (64-bit or 32-bit), the correct version of the cluster connector does not load automatically. Go to Excel Options and select the appropriate connector from the Cluster type drop-down list.

• The Excel Cluster Connector is a COM add-in to Excel that depends on the primary interop assemblies (PIAs). If the PIAs are present, the add-in is activated automatically when you install the HPC Pack 2008 R2 client utilities. If the PIAs are not present and the add-in is not activated, you will see an error message from Excel when you attempt to enable and configure the Excel Cluster Connector. To resolve this issue, you can install the PIAs, then manually activate the Excel Cluster Connector as follows:

  • See How to: Install Office Primary Interop Assemblies (https://go.microsoft.com/fwlink/?LinkId=198481).

  • Then to manually activate the connector in Excel, click File, click Options, and then click the Add-Ins tab. In Manage, select COM Add-ins from the drop-down list and then click Go. Select Excel Cluster Connector for Windows HPC Server 2008 R2, and then click OK.

Additional references

• Windows HPC Server 2008 R2: HPC Services for Excel

• Developing Excel workbooks that can run on a cluster

• Windows HPC and Microsoft Excel Survival Guide on the TechNet Wiki (https://go.microsoft.com/fwlink/?LinkID=198462)

• Windows HPC Server 2008 R2