Digipede: Distributing Excel Computations – Part I

"Yes Virginia, you can run an Excel spreadsheet on a compute grid." It’s true. But you can’t run just any spreadsheet and there is no magic bullet. You will have to do a little work. Something, a Master, has to initiate the work and something, a Worker, has to do the work. Whether the initiator is an Excel spreadsheet or a custom application doesn’t matter, whether the work is an Excel spreadsheet, a DLL, or an executable doesn’t matter. All that matters is the relationship between the Master and the Worker.

For this code sample I’m going to use an Excel Master and I’m going to distribute an Excel Worker. I’ve created a closed system on my machine (I have a Digipede Server, Agent, and Framework all installed on one machine) to simplify my testing environment. Remember when building a distributed application to create your simplest starting case first and then to expand as you verify each phase.

The first thing to do is make sure that I correctly tell the Digipede Network what files to move and how to initiate the Worker spreadsheet. I then will test everything on the compute node.

Excel Master with VBA code-behind:

Private Sub btnStartCalculations_Click()

    ' Perform validation – we need a valid user id, password and Digipede host address
    Dim errMsg As Variant
    Dim userId As String
    Dim password As String
    
    userId = Sheet1.Range("UserId")
    password = Sheet1.Range("Password")
    If userId = "" Or password = "" Then
        errMsg = "Enter a Digipede Network user id and password."
        MsgBox errMsg
        Exit Sub
    End If

    Dim hostName As String
    hostName = Sheet1.Range("HostName")
    If hostName = "" Then
        errMsg = "Enter a Digipede Server name."
        MsgBox errMsg
        Exit Sub
    End If
    
    Dim fileShareLoc As String
    fileShareLoc = Sheet1.Range("FileShareLocation")
    If fileShareLoc = "" Then
        errMsg = "Enter the file share location of the files to distribute."
        MsgBox errMsg
        Exit Sub
    End If
    
    ' Depending on the version of the Digipede Network 
    ' you may want to setup pools
    Dim poolId As Long
    poolId = Sheet1.Range("PoolId")
    If poolId = 0 Then
        poolId = 1
    End If

    Dim strLaunchScript As String
    strLaunchScript = "RunWorker.vbs"

    ' Disable the Calculate button
    'btnStartCalculations.Enabled = False
    Sheet1.Range("SubmissionTime") = ""
    Sheet1.Range("FinishedTime") = ""
    Sheet1.Range("JobId") = ""
    
    ' Initialize the client
    ThisWorkbook.mClient.SetCredentials userId, password
    ThisWorkbook.mClient.SetUrlFromHost hostName
    
    ' Create the JobTemplate
    Dim jobTemplate As Digipede_Framework.jobTemplate
    Set jobTemplate = New Digipede_Framework.jobTemplate

    ' I set DiscardAfterUser to False because I’m debugging, 
    ' I plan to move some files to the compute nodes and I
    ' need to manually confirm that after the move everything
    ' will work the way I expect.
    jobTemplate.DiscardAfterUse = False

    ' Create a RemoteLocation for the Worker Script
    Dim location As Digipede_Framework.RemoteLocation
    Set location = New Digipede_Framework.RemoteLocation

    location.ID = 0
    location.location = fileShareLoc
    location.ProtocolType = Digipede_Framework.ProtocolType_Share
    jobTemplate.RemoteLocations.AddItem location

    ' Create a filedef for the launch script – the filedef 
    ' defines a file to be moved by the Digipede Network.
    Dim fileDef As Digipede_Framework.fileDef
    Set fileDef = New Digipede_Framework.fileDef
    fileDef.ID = 0
    fileDef.remoteLocationId = 0 
    fileDef.LocalName = " RunWorker.vbs"
    fileDef.Name = " RunWorker.vbs"
    fileDef.Relevance = Digipede_Framework.Relevance_JobTemplate
    jobTemplate.FileDefs.AddItem fileDef
    
    '  Create a filedef for the Worker Excel file
    Set fileDef = New Digipede_Framework.fileDef
    fileDef.ID = 1
    fileDef.remoteLocationId = 0 
    fileDef.LocalName = "Worker.xls"
    fileDef.Name = "Worker.xls"
    fileDef.Relevance = Digipede_Framework.Relevance_JobTemplate
    jobTemplate.FileDefs.AddItem fileDef
    
    ' Create one parameter placeholder
    Dim parameter As Digipede_Framework.parameter
    Set parameter = New Digipede_Framework.parameter
    parameter.Name = "InputText"
    parameter.Relevance = Digipede_Framework.Relevance_InputPlaceholder
    jobTemplate.Parameters.AddItem parameter

    jobTemplate.Control.CommandLine = "cscript RunWorker.vbs $(InputText)"
    jobTemplate.Control.SaveStandardError = False
    jobTemplate.Control.SaveStandardOutput = False
    jobTemplate.Control.UseShellExecute = True
    
    jobTemplate.Name = "ExcelSubmitterTest1"

    ' now the JobTemplate is complete; create a job with tasks.
    Set mJob = New Digipede_Framework.job
    mJob.Name = "ExcelTest1"

    Dim i As Integer
    Dim oTask As Task
    
    ' Create a job but only put one Task in.  I want to make
    ' sure that everything works for one Task before I 
    ' add more.
    For i = 1 To 1
        Set oTask = New Digipede_Framework.Task
        
        ' Later I’m going to passing along an input data file.
        ' Right now I’m just passing in a parameter that will 
        ' eventually be used to build the name of that file.
        Dim taskParameter As Digipede_Framework.parameter
        Set taskParameter = New Digipede_Framework.parameter
        taskParameter.Name = parameter.Name
        taskParameter.Value = i
        oTask.Parameters.AddItem taskParameter
        
        mJob.Tasks.AddItem oTask
    Next i

    Sheet1.Range("SubmissionTime") = Format(Time, "hh:mm:ss")

    ThisWorkbook.mClient.SubmitJob poolId, jobTemplate, mJob,
      Digipede_Framework.SubmissionOptions_None
        
    Sheet1.Range("JobId") = mJob.JobId

End Sub

Notice that I am distributing a VBS script. I have yet to figure out how to get
Excel to close properly on a remote machine without a script. Code that opens and closes Excel on my machine doesn’t work from a remote call. So for now I invoke the Worker using a script. This actually works fine for this use case because I need to initialize the Worker spreadsheet anyway.

Launch Script to launch the Worker spreadsheet:

' Create a WshShell to get the current directory
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application") 

‘ Disable as much UI as possible.
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone

' Set this so that the Excel files can be found and saved
Dim strPath
strPath = WshShell.CurrentDirectory
myExcelWorker.DefaultFilePath = strPath
 
Dim objArgs
Set objArgs = WScript.Arguments

' Open the Workbook specified on the command-line 
Dim WBook
Dim strWorkerWB
strWorkerWB = strPath & "\Worker.xls"
Set WBook = myExcelWorker.Workbooks.Open(strWorkerWB)

' This is my test code, I want to update a cell so that I know that I
' opened the spreadsheet and did something.
Dim TaskId 
TaskId = objArgs(0)

WBook.Sheets("Automate").Range("O3") = TaskId

WBook.Save

' Clean up and shut down
WBook.Close
Set WBook = Nothing
Set objArgs = Nothing
Set myExcelWorker = Nothing
Set WshShell = Nothing

Notice in the code that I build a path to the Worker workbook. I need to do this
because of how Excel works, if I don’t use a path then Excel looks in the wrong directory and doesn’t open my workbook.

It should also be noted that neither the Worker workbook nor the Launch script should require user interaction. Anything that runs on a compute node will not complete execution if user feedback is required or message boxes pop up. So make sure you have removed all UI elements.

Test it:

We’re now ready to test. As I stated earlier I run all initial testing on a closed system. This just makes it easier for me to access everything I need. Here are my testing steps:

1. I open the Master Excel spreadsheet and press ‘Start’. This action causes the files I need to be copied to my compute node. In a closed system this results in the files being placed in a specific subdirectory on my machine.
2. Once the Master returns I open Digipede Control and get my JobId for that test.
3. I then open a Command Prompt and go to the directory: “C:\Documents and Settings\All Users\Application Data\Digipede\Agent\Data”. The subdirectory I want from here is JobId plus “v1”. So in this case I will go to “15v1” because my JobId is 15.
4. I look in the subdirectory to see if the files I was expecting are there. Yep, looks good.

The code above is the working code but it didn’t work in its first incarnation. Debugging was required. To debug what is happening on the compute node, I ran the RunWorker.vbs file from the command-line with the following call:

cscript /x runworker.vbs 1

I then modified the script until it worked. When I had a working script I replaced the original copy of RunWorker.vbs with the working copy.

I now know that the files are being moved and that my script is launching and initializing the Worker correctly. The next step will be to add an input data file and I will cover that in Part 2.

Essay Links:

Start
Automation
Part 1
Part 2
Part 3
Part 4
Part 5

Technorati tags: Digipede, Grid Computing, Distributed, Excel, .VBA, .VBS, Microsoft