Putting a grid behind Excel 2007

O

  ver in the Excel 2007 blog, David Gainer discussed using a cluster behind Excel Services. The Excel Calculation Engine is now multithreaded and can take advantage of a beefy server, but David points out that, for even more powerful computation, you may want to use the power of many machines. I knew they had been working on this, and Stevan Vidich had an Excel Services demo running on the cluster at the SIA Technology Management conference.

As David points out in his recent post, you can

...deploy your UDF to the cluster, and then use an XLL to (essentially – I am simplifying a tiny bit – and we will make sample code available at some point) call the UDF on the cluster with the appropriate parameters.

I know there was a lot going on behind the scenes to make this happen--installing Excel Services on each cluster node, deploying an XLL around the cluster, etc.. Being a West Coast Grid guy, I wanted to see this run natively in .NET across a cluster. So I did this:

First, I installed Sharepoint Server 2007 Enterprise Edition (which includes Excel Services) on the head node on my cluster. I made sure it was working by writing a UDF (that's User Defined Function) that does my old standby Monte Carlo simulation: calculating pi. It looked like this:

for (int i = 0; i < mNumberOfDraws; i++) {
   x = rand.NextDouble();
   y = rand.NextDouble();
   if (Math.Sqrt(x * x + y * y) <= 1.0) {
      numberInsideOfUnitCircle++;
   }
}

mPi = 4.0 * numberInsideOfUnitCircle / mNumberOfDraws;

It's inefficient and silly, but it works.

In order to make this run on a grid, I pulled that code into its own class (which I called a DigipedePiWorker). Then, in my UDF, I added this code:

DigipedeClient mDigipedeClient = new DigipedeClient();
mDigipedeClient.SetUrlFromHost("leg7");
mDigipedeClient.SetCredentials("dan", "dan");

JobTemplate jobTemplate = JobTemplate.NewWorkerJobTemplate(typeof(PiWorker));
Job job = new Job();
for (int i = 0; i < tasks; i++) {
   DigipedePiWorker thisWorker = new DigipedePiWorker(numdraws);
   Task task = job.Tasks.Add();
   task.Worker = thisWorker;
}
job.TaskCompleted +=
   delegate(object sender, TaskStatusEventArgs e) {
      DigipedePiWorker dpw = (DigipedePiWorker)e.Worker;
      pi += dpw.Pi;
   };
SubmissionResult submissionResult = mDigipedeClient.SubmitJob(jobTemplate, job);
   mDigipedeClient.WaitForJob(submissionResult.JobId);
   result = pi / tasks;

This was fantastic. It took about 20 lines of code in total. My .NET objects were automatically distributed around my grid (and my cluster, because my grid includes a small CCS cluster). I didn't install Excel Services on any of the nodes. And I didn't have to manually deploy my UDF anywhere--the grid handled all of that for me.

This is going to be a very powerful use case for Excel 2007--the ability to take a UDF and run it on many machines simultaneously puts a huge powerhouse in your spreadsheets. And Excel Services puts your spreadsheet into a browser. What does all of that add up to?

Any user on my network can now view a spreadsheet in his browser, enter the inputs they want, and have the entire grid work on the results. That is cool stuff.

I also can't get over how smoothly this worked. I'm a newbie to Sharepoint, so working with it took a little getting used to. But as far as writing my UDF (in Visual Studio 2005) and plugging it into a spreadsheet, grid enabling the UDF, then adding that spreadsheet to Sharepoint and making it accessible via browser--all that was done in less than an hour and a half this morning.

Kudos to the Excel 2007 team. From the demos I had seen, I knew it looked great. I had no idea how easy and powerful it was going to be to work with!

Technorati tags: grid computing, digipede, excel, CCS