Running parallel Excel on Windows HPC 2008 R2 hosted in Windows Azure IaaS VMs: Part 4

Artikkeli
07/11/2013

click here to Move to previous blog-Part3

1 Introduction

In this post, we proceed with further discussion on simulation through Excel workbook running in parallel on HPC cluster. Below the list of related blogs:

Blog-Part1 : how to build up a Windows HPC 2008 R2 cluster in Azure IaaS VMs and installation of Excel 2010 on HPC cluster nodes
Blog-Part2 : how to run a Excel 2010 workbook in parallel on Windows HPC 2008 R2 cluster; it is discussed a basic multiplication table rows by columns
Blog-Part3 : how to run in Excel 2010 workbook a multiplication table row by column in parallel with data partition in block

In this forth blog we discuss a more complex Excel workbook to calculate a Fresnel transform, very common in linear optics. Fresnel transform is CPU intensive and therefore a good example to point out advantages to run Excel on multiple HPC compute nodes vs single desktop/laptop.

2 Fresnel transform in Excel running in parallel on Windows HPC cluster

In linear optics, electromagnetic diffraction through a plane screen is described in good approximation with Fresnel transform. Let V0(s,t) the electromagnetic field on a point (p,q,z=0) on a plane z=0 and V(x,y,z) the electromagnetic field on point (x,y,z) as shown in Figure 1

Figure 1

At distance z>> l the electromagnetic field V(x,y,z) is described with good approximation through the Fresnel transform:

Equation 1: Fresnel equation

where:

Our goal is to write an Excel workbook able to run in parallel on Windows HPC cluster installed in windows Azure IaaS VMs. By substitution of Eulero's formula

in Equation 1 we get the electromagnetic pattern V(x,y,z) on a plan (x,y,z) composed of real part and imaginary part:

Equation 2: Fresnel equation slit up in real and imaginary parts

For simplicity, we set the real and imaginary part:

Equation 3: real and imaginary part of Fresnel equation

To run a compute simulation we need to apply a sampling in plane (p,q,z=0) and in plane (x,y,z). Let consider

a segment L_p centered around the p axis and L_q centered around the q axis
a segment X_m centered around the x axis and Y_n centered aroung the y axis
N_p the number of samples in the p axis
N_q the number of samples in the q axis
N_m the number of samples in the x axis
N_n the number of samples in the y axis

Figure 2: sampling intervals on two planes

In Equation 4 is reported the sampling intervals and the relative ranges

Equation 4: sampling intervals and range of sampling

In Equation 4 we have following values:

Dp: it is distance between two adjacency samples along the p axis

Dq: it is the distance between two adjacency samples along the q axis

Dx: it is the distance between two adjacency samples along the x axis

Dy: it is the distance between two adjacency samples along the y axis

The distance r_jl between the sample (p_j,q_l,z=0) and the sample (x_m,y_n,z) is shown in Equation 5:

Equation 5: sampled intervals

The discrete expression for real and imaginary part of electromagnetic field are shown in the Equation 6:

Equation 6: discrete expression of real and imaginary part of Fresnel equation

The intensity I(x_m,y_n,z) of electromagnetic field is equal to the square of module of electromagnetic field V(x_m,y_n,z):

Equation 7: discrete expression of electromagnetic intensity

Our simulation is based on the Equation 4-Equation 7.

3 Project organization

Project is organized in four modules:

HPCExcelMacros.bas : it contains all HPC macro framework functions designated to support parallel execution
HPCControlMacros.bas : a set of VBA codes contains the “control” functions. In this VBA module we have few parameters as the name of HPC head node and the shared folder used from the different HPC compute nodes to get the Excel file for running the simulation
WorkingFunctions.bas : code to run discrete Fresnel transform (Equation 4- Equation 7)
PlottingGraph.bas : function to plot outcome of simulation [intensity I(x,y,z) of electromagnetic field in Fresnel approximation].This VBA module contains some controls that allow to change view and orientation of graphical chart (clockwise rotation, anticlockwise rotation, change in perspective angle, elevation angle).

3.1 HPCExcelMacros.bas

 Attribute VB_Name = "HPCExcelMacros"  
 Option Explicit 
  '==========================================================  
  ' Section 1: Variables and constants 
  '==========================================================  
 Public Pi As Double 
 Public z As Double 
 Public lambda As Double 
 Public K As Double 
 Public V0 As Double 
 Public Nx As Integer 
 Public Ny As Integer 
 Public Lx As Double 
 Public Ly As Double 
 Public deltaX As Double 
 Public deltaY As Double 
 
 Public Np As Integer 
 Public Nq As Integer 
 Public Lp As Double 
 Public Lq As Double 
 Public percentageCompletion As Single 
 Public m As Integer 
 Public n As Integer 
 Public indexRow As Integer 
 Public indexCol As Integer 
 Public CounterStatus As Integer 
 
 Dim CalculationComplete As Boolean 
 Dim StartTime As Double 
 Dim FinishTime As Double 
 Public Const SingleBlock As Integer = 5 
 Public Const NumBlocks As Integer = 50 
 Public Const SizeVector As Integer = NumBlocks * SingleBlock 
 
 
  '==========================================================  
  ' Section 2: HPC Calculation Macros 
  '==========================================================  
 Public Function HPC_GetVersion()  
 HPC_GetVersion = "1.0"  
 End Function 
 
 Public Function HPC_Initialize()  
 ThisWorkbook.Sheets("Sheet2").Cells.ClearContents 
 ThisWorkbook.Sheets("Sheet1").Range("percentageCompletion").Value = 0 
 percentageCompletion = 0 
 Call DeleteGraph("Sheet1")  
  '==============================================  
 Application.ScreenUpdating = False 
 
 V0 = ThisWorkbook.Sheets("Sheet1").Range("V0").Value 
 z = ThisWorkbook.Sheets("Sheet1").Range("zaxis").Value 
 lambda = ThisWorkbook.Sheets("Sheet1").Range("lambda").Value 
 Nx = ThisWorkbook.Sheets("Sheet1").Range("Nx").Value ' Nx: number of samples in x-axis 
 Ny = ThisWorkbook.Sheets("Sheet1").Range("Ny").Value ' Ny: number of samples in y-axis 
 Lx = ThisWorkbook.Sheets("Sheet1").Range("Lx").Value 
 Ly = ThisWorkbook.Sheets("Sheet1").Range("Ly").Value 
 Np = ThisWorkbook.Sheets("Sheet1").Range("Np").Value ' Np: number of samples in x-axis 
 Nq = ThisWorkbook.Sheets("Sheet1").Range("Nq").Value ' Nq: number of samples in y-axis 
 Lp = ThisWorkbook.Sheets("Sheet1").Range("Lp").Value 
 Lq = ThisWorkbook.Sheets("Sheet1").Range("Lq").Value 
 
 deltaX = Lx / Nx 
 deltaY = Ly / Ny 
 
 Pi = 4 * Atn(1)  
 m = -Nx / 2 
 n = -Ny / 2 
 
 indexRow = 1 
 indexCol = 1 
 CounterStatus = 0 ' counter on number of record computed 
 CalculationComplete = False 
 StartTime = Timer 
 End Function 
 
 
 Public Function HPC_Partition() As Variant 
 Dim data(SizeVector) As Variant 
 Dim indexBlock As Integer 
 Dim x As Double 
 Dim y As Double 
 
 For indexBlock = 0 To (NumBlocks - 1)  
 If indexCol > Nx Then 
 indexCol = 1 
 m = -Nx / 2 
  '----  
 indexRow = indexRow + 1 
 n = n + 1 
 End If 
 
 If indexRow > Ny Then 
  ' return null to indicate the end of the calculation 
 HPC_Partition = Null 
  ' and exit the function now, we can skip the rest 
 Exit Function 
 End If 
 
 x = m * deltaX 
 y = n * deltaY 
 
 data(0 + indexBlock * SingleBlock) = indexCol 
 data(1 + indexBlock * SingleBlock) = indexRow 
 data(2 + indexBlock * SingleBlock) = x 
 data(3 + indexBlock * SingleBlock) = y 
 
 If ((indexRow = Nx) And (indexCol = Ny)) Then 
 m = m + 1 
 indexCol = indexCol + 1 
 Exit For 
 End If 
 
  ' move to the next sample 
 m = m + 1 
  ' move to the next col 
 indexCol = indexCol + 1 
 Next 
 HPC_Partition = data 
 End Function 
 
 
 Public Function HPC_Execute(data As Variant) As Variant 
 Dim x As Double 
 Dim y As Double 
 Dim resultVal As Double 
 Dim indexBlock As Integer 
 
 V0 = ThisWorkbook.Sheets("Sheet1").Range("V0").Value 
 z = ThisWorkbook.Sheets("Sheet1").Range("zaxis").Value 
 lambda = ThisWorkbook.Sheets("Sheet1").Range("lambda").Value 
 Np = ThisWorkbook.Sheets("Sheet1").Range("Np").Value ' Np: number of samples in x-axis 
 Nq = ThisWorkbook.Sheets("Sheet1").Range("Nq").Value ' Nq: number of samples in y-axis 
 Lp = ThisWorkbook.Sheets("Sheet1").Range("Lp").Value 
 Lq = ThisWorkbook.Sheets("Sheet1").Range("Lq").Value 
 
 For indexBlock = 0 To (((UBound(data)) / SingleBlock) - 1)  
 x = data(2 + indexBlock * SingleBlock)  
 y = data(3 + indexBlock * SingleBlock)  
 
 resultVal = FresnelTransform(x, y, z, V0, lambda, Lp, Lq, Np, Nq)  
 data(4 + indexBlock * SingleBlock) = resultVal 
 Next 
  ' return value from the function 
 HPC_Execute = data 
 End Function 
 
 
 Public Function HPC_Merge(data As Variant)  
 Dim indexBlock As Integer 
 Dim i As Integer 
 Dim j As Integer 
 Dim x As Double 
 Dim y As Double 
 Dim resultVal As Double 
 
  'Application.ScreenUpdating = False 
 For indexBlock = 0 To (((UBound(data)) / SingleBlock) - 1)  
 i = data(0 + indexBlock * SingleBlock)  
 j = data(1 + indexBlock * SingleBlock)  
 x = data(2 + indexBlock * SingleBlock)  
 y = data(3 + indexBlock * SingleBlock)  
 resultVal = data(4 + indexBlock * SingleBlock)  
 
 
 ThisWorkbook.Sheets("Sheet2").Cells(1, i + 1).Value = x 
 ThisWorkbook.Sheets("Sheet2").Cells(j + 1, 1).Value = y 
 ThisWorkbook.Sheets("Sheet2").Cells(j + 1, i + 1).Value = resultVal 
 CounterStatus = CounterStatus + 1 
 If (i = Nx) And (j = Ny) Then 
 Call UpdateStatus 
 Application.ScreenUpdating = True 
 Exit For 
 End If 
 Next 
 Call UpdateStatus 
  'Application.ScreenUpdating = True 
 
 End Function 
 
 
 Public Function HPC_Finalize()  
 Dim cellRangeGraph As String 
 Dim cellRangeFormatting As String 
 
 FinishTime = Timer 
 CalculationComplete = True 
 Call UpdateStatus 
 
  ' Clean up the calculation. It's a good idea to 
  ' leave this here, even if you make changes to 
  ' this function. The function we call here is in 
  ' the "HPCControlMacros" module.  
 
 CleanUpClusterCalculation 
 
 cellRangeGraph = Worksheets("Sheet2").Cells(1, 1).Address(False, False) + ":" + Worksheets("Sheet2").Cells(Nx + 1, Ny + 1).Address(False, False)  
 cellRangeFormatting = Worksheets("Sheet2").Cells(2, 2).Address(False, False) + ":" + Worksheets("Sheet2").Cells(Nx + 1, Ny + 1).Address(False, False)  
 
 Call GenerateGraph("Sheet1", "Sheet2", cellRangeGraph)  
 Call ConditionalFormatting("Sheet2", cellRangeFormatting)  
 Application.ScreenUpdating = True 
 End Function 
 
 
 Public Function HPC_ExecutionError(errorMessage As String, errorContents As String)  
 MsgBox errorMessage & vbCrLf & vbCrLf & errorContents 
 End Function 
 
 Sub UpdateStatus()  
 Dim statusMessage As String 
 
 Application.ScreenUpdating = True 
 If Not CalculationComplete Then 
 statusMessage = "Calculated " & CounterStatus & "/" & (Nx * Ny)  
 Else 
 statusMessage = "Calculated " & CounterStatus & "/" & (Nx * Ny)  
 statusMessage = statusMessage & "; completed in " & FormatNumber(FinishTime - StartTime) & "s"  
 End If 
 Application.StatusBar = statusMessage 
 ActiveSheet.Range("percentageCompletion").Value = CounterStatus / (Nx * Ny)  
 Application.ScreenUpdating = False 
 End Sub

3.2 HPCControlMacros.bas

 Attribute VB_Name = "HPCControlMacros"  
 
 Option Explicit 
 
  '==========================================================  
  ' Section 1: Variables and constants 
  '==========================================================  
 Private Const HPC_ClusterScheduler = "exhn001"  
 Private Const HPC_NetworkShare = "\\exhn001\share"  
 Private Const HPC_JobTemplate = ""  
 Private HPCExcelClient As IExcelClient 
 
  '==========================================================  
  ' Section 2: Control Functions 
  '==========================================================  
 
 Private Sub CalculateWorkbook(CalculateOnDesktop As Boolean)  
 Dim HPCWorkbookPath As String 
 On Error GoTo ErrorHandler 
 
  ' Create a new excelCient instance per session 
 Set HPCExcelClient = New ExcelClient 
 
  ' Initialize the excel client object with the current workbook 
 HPCExcelClient.Initialize ActiveWorkbook 
 
 If CalculateOnDesktop = False Then 
 
  ' We need a copy of the file on the network, so it's accessible 
  ' by the cluster compute nodes. Save a temporary copy to the 
  ' share directory.  
 
 HPCWorkbookPath = HPC_NetworkShare & Application.PathSeparator & ActiveWorkbook.Name 
 
 ActiveWorkbook.SaveCopyAs HPCWorkbookPath 
 
  ' Create a cluster session with the desired options. Here, we're 
  ' just using the scheduler name and (optionally) a job template.  
 
 If HPC_JobTemplate <> "" Then 
 HPCExcelClient.OpenSession headNode:=HPC_ClusterScheduler, remoteWorkbookPath:=HPCWorkbookPath, jobTemplate:=HPC_JobTemplate 
 Else 
 HPCExcelClient.OpenSession headNode:=HPC_ClusterScheduler, remoteWorkbookPath:=HPCWorkbookPath 
 End If 
 
 End If 
 
  ' Run on local machine or cluster as chosen in workbook 
 HPCExcelClient.Run CalculateOnDesktop 
 Exit Sub 
 
 ErrorHandler:  
  ' Notify user of error and clean up any allocated resources 
 MsgBox Prompt:=Err.Description, Title:="HPC Calculation Error"  
 If Not HPCExcelClient Is Nothing Then 
 HPCExcelClient.Dispose 
 End If 
 End Sub 
 
 Public Sub CalculateWorkbookOnDesktop()  
 CalculateWorkbook (True)  
 End Sub 
 
 Public Sub CalculateWorkbookOnCluster()  
 CalculateWorkbook (False)  
 End Sub 
 
 Public Sub CleanUpClusterCalculation()  
 
 On Error Resume Next 
 HPCExcelClient.CloseSession 
 HPCExcelClient.Dispose 
 On Error GoTo 0 
 End Sub

3.3 WorkingFunctions.bas

 Attribute VB_Name = "WorkingFunctions"  
 Option Explicit  
  ' Np: number samples in origin plane in p axis 
  ' Nq: number samples in origin plane in q axis 
 Public Function FresnelTransform(x As Double, y As Double, z As Double, V0 As Double, lambda As Double, Lp As Double, Lq As Double, Np As Integer, Nq As Integer) As Double 
 Dim realPart As Double 
 Dim imgPart As Double 
 Dim i As Integer 
 Dim j As Integer 
 Dim p As Double 
 Dim q As Double 
 Dim r As Double 
 Dim deltaP As Double 
 Dim deltaQ As Double 
 
 deltaP = Lp / Np 
 deltaQ = Lq / Nq 
 
 Pi = 4 * Atn(1)  
 
 K = 2 * Pi / lambda 
 realPart = 0 
 imgPart = 0 
 For j = (-Nq / 2) To (Nq / 2) Step 1 
 q = j * deltaQ 
 For i = (-Np / 2) To (Np / 2) Step 1 
 p = i * deltaP 
 r = Sqr(z ^ 2 + (x - p) ^ 2 + (y - q) ^ 2)  
 realPart = realPart + disturbance(V0, p, q) * Sin(K * r) * ((z * deltaP * deltaQ) / (lambda * (r ^ 2)))  
 imgPart = imgPart - disturbance(V0, p, q) * Cos(K * r) * ((z * deltaP * deltaQ) / (lambda * (r ^ 2)))  
 Next i 
 Next j 
 
  ' return value 
 FresnelTransform = ((realPart ^ 2) + (imgPart ^ 2))  
 End Function 
 
 
 Function disturbance(V0 As Double, p As Double, q As Double) As Double 
 Dim Radius As Double 
 Radius = 0.0005  
 If ((p ^ 2) + (q ^ 2)) <= (Radius * Radius) Then 
 disturbance = V0 
 Else 
 disturbance = 0 
 End If 
 End Function

In the WorkingFunctions.bas macro it is useful enlighten some variables:

lambda: it is the wavelength
K=2 * Pi / lambda : it is the wavenumber
the Function disturbance(V0 As Double, p As Double, q As Double) is the electromagnetic function in the plane (p,q,z0)
R is the radius of pinhole in the plane z=0
Public Function FresnelTransform it is the Intensity of light at the coordinate (x,y,z)

3.4 PlottingGraph.bas

 






















Attribute VB_Name = "PlottingGraph" <br> Option Explicit   Public Const NameGraph As String = "Intensity"   Public Sub DeleteGraph(sheetGraphName As String) <br> Dim ws As Worksheet <br> Set ws = Worksheets(sheetGraphName) <br> On Error Resume Next <br> 'ActiveSheet.ChartObjects.Delete <br> ws.ChartObjects.Delete <br> End Sub   Public Sub GenerateGraph(sheetGraphName As String, sheetDataName As String, rangeCellsToBePlot As String) <br> Dim ws As Worksheet <br> Dim wsData As Worksheet <br> Dim rSrc As Range, rLocation As Range <br> Dim chtO As ChartObject    Set ws = Worksheets(sheetGraphName) <br> Set wsData = Worksheets(sheetDataName)    Set rSrc = wsData.Range(rangeCellsToBePlot) <br> Set rLocation = ws.Range("E4:L33")   With rLocation <br> Set chtO = ws.ChartObjects.Add(.Left, .Top, .Width, .Height) <br> chtO.Name = NameGraph <br> End With   With chtO.Chart <br> .SetSourceData Source:=rSrc <br> ' xlSurface: Displays a standard surface chart <br> ' xlSurfaceWireFrame: Displays a surface chart without colors <br> ' xlSurfaceTopView: Displays the a surface chart viewed from above <br> ' xlSurfaceTopViewWireFrame: Displays a surface chart without colors, viewed from abov <br> .ChartType = xlSurface <br> .HasTitle = True <br> .ChartTitle.Font.Size = 10 <br> .ChartTitle.Font.Color = RGB(110, 10, 155) <br> .ChartTitle.Text = "Intensity-Fresnel approx." <br> .Legend.Position = xlRight <br> .Legend.Font.Size = 8 <br> .Legend.Font.Name = "Arial" <br> End With   'With chtO.Chart <br> ' .Rotation = 40 <br> ' .Elevation = 30 <br> ' .Perspective = 15 <br> 'End With <br> End Sub <br> ' The rotation of the 3-D chart view (the rotation of the plot area around the z-axis, in degrees). <br> ' The default value is 20. <br> Sub Rotation1_Click() <br> Dim objCht As ChartObject <br> Dim rotationValue As Integer <br> On Error Resume Next <br> Set objCht = Worksheets("Sheet1").ChartObjects(NameGraph) <br> If Not objCht Is Nothing Then <br> rotationValue = Worksheets("Sheet1").ChartObjects(NameGraph).Chart.Rotation <br> With Worksheets("Sheet1").ChartObjects(NameGraph).Chart <br> .Rotation = rotationValue + 20 <br> End With <br> End If <br> End Sub   ' The rotation of the 3-D chart view (the rotation of the plot area around the z-axis, in degrees). <br> ' The default value is 20. <br> Sub Rotation2_Click() <br> Dim objCht As ChartObject <br> Dim rotationValue As Integer <br> On Error Resume Next <br> Set objCht = Worksheets("Sheet1").ChartObjects(NameGraph) <br> If Not objCht Is Nothing Then <br> rotationValue = Worksheets("Sheet1").ChartObjects(NameGraph).Chart.Rotation <br> With Worksheets("Sheet1").ChartObjects(NameGraph).Chart <br> .Rotation = rotationValue - 20 <br> End With <br> End If <br> End Sub   Sub Elevation1_Click() <br> Dim objCht As ChartObject <br> Dim elevationValue As Integer <br> On Error Resume Next <br> Set objCht = Worksheets("Sheet1").ChartObjects(NameGraph) <br> If Not objCht Is Nothing Then <br> 'Elevation: Gets or sets the elevation of the 3-D chart view, in degrees. <br> elevationValue = Worksheets("Sheet1").ChartObjects(NameGraph).Chart.Elevation <br> With Worksheets("Sheet1").ChartObjects(NameGraph).Chart <br> .Elevation = (elevationValue + 20) Mod 90 <br> End With <br> End If <br> End Sub   Sub Elevation2_Click() <br> Dim objCht As ChartObject <br> Dim elevationValue As Integer <br> On Error Resume Next <br> Set objCht = Worksheets("Sheet1").ChartObjects(NameGraph) <br> If Not objCht Is Nothing Then <br> 'Elevation: Gets or sets the elevation of the 3-D chart view, in degrees. <br> elevationValue = Worksheets("Sheet1").ChartObjects(NameGraph).Chart.Elevation <br> With Worksheets("Sheet1").ChartObjects(NameGraph).Chart <br> .Elevation = Abs((elevationValue - 20) Mod 90) <br> End With <br> End If <br> End Sub   ' Perspective: Gets or sets the perspective for the 3-D chart view. <br> ' the perspective for the 3-D chart view. Must be between 0 and 100. <br> Sub Perspective1_Click() <br> Dim objCht As ChartObject <br> Dim perspectiveValue As Integer <br> On Error Resume Next <br> Set objCht = Worksheets("Sheet1").ChartObjects(NameGraph) <br> If Not objCht Is Nothing Then <br> 'Elevation: Gets or sets the elevation of the 3-D chart view, in degrees. <br> perspectiveValue = Worksheets("Sheet1").ChartObjects(NameGraph).Chart.Perspective <br> With Worksheets("Sheet1").ChartObjects(NameGraph).Chart <br> .Perspective = (perspectiveValue + 20) Mod 100 <br> End With <br> End If <br> End Sub <br> Sub Perspective2_Click() <br> Dim objCht As ChartObject <br> Dim perspectiveValue As Integer <br> On Error Resume Next <br> Set objCht = Worksheets("Sheet1").ChartObjects(NameGraph) <br> If Not objCht Is Nothing Then <br> 'Elevation: Gets or sets the elevation of the 3-D chart view, in degrees. <br> perspectiveValue = Worksheets("Sheet1").ChartObjects(NameGraph).Chart.Perspective <br> With Worksheets("Sheet1").ChartObjects(NameGraph).Chart <br> .Perspective = Abs((perspectiveValue - 20) Mod 100) <br> End With <br> End If <br> End Sub     ' https://msdn.microsoft.com/en-us/library/office/hh203953%28v=office.14%29.aspx <br> Public Sub ConditionalFormatting(sheetDataName As String, rangeDataCells As String) <br> Dim rng As Range <br> Set rng = Worksheets(sheetDataName).Range(rangeDataCells) '"B2:AY51" <br> rng.FormatConditions.Delete   ' Add a 2-color scale. <br> Dim cs As ColorScale <br> Set cs = rng.FormatConditions.AddColorScale(ColorScaleType:=3)   ' Set the color of the lowest value, with a range up to <br> ' the next scale criteria. The color should be red. <br> With cs.ColorScaleCriteria(1) <br> .Type = xlConditionValueLowestValue <br> With .FormatColor <br> .Color = &H6B69F8 <br> ' TintAndShade takes a value between -1 and 1. <br> ' ' -1 is darkest, 1 is lightest. <br> .TintAndShade = 0 <br> End With <br> End With   ' At the 50th percentile, the color should be red/green. <br> ' Note that you cannot set the Value property for all values of Type. <br> With cs.ColorScaleCriteria(2) <br> .Type = xlConditionValuePercentile <br> .Value = 50 <br> With .FormatColor <br> .Color = &H84EBFF <br> .TintAndShade = 0 <br> End With <br> End With   ' At the highest value, the color should be green. <br> With cs.ColorScaleCriteria(3) <br> .Type = xlConditionValueHighestValue <br> With .FormatColor <br> .Color = &H7BBE63 <br> .TintAndShade = 0 <br> End With <br> End With <br> End Sub

4 Run simulations on HPC cluster with different number of cores

Now that our Excel workbook ( .xlsb file) contains the four modules HPCExcelMacros.bas, HPCControlMacros.bas, WorkingFunctions.bas, PlottingGraph.bas and reference to HPC library (Microsoft_Hpc_Excel) is added, we are ready to run it on Windows HPC cluster. In the macro HPCControlMacros.bas there is a constant:

Private Const HPC_JobTemplate

referring an HPC job template; by default this constant is set to empty ("") that means to use the default job template.
Through the job template the HPC administrator can specify which resources (max job duration, maximum number of nodes, maximum number of cores, etc.) a specific HPC user is authorized to use for HPC job submission. We want to evaluate the performance of our Excel worksheet running many times the same discrete Fresnel transform with same simulation input parameters, but with different number of HPC cores. for this purpose we create by the HPC management console different HPC job template each with different number of maximum number of usable cores:

Figure 3: default job template

The default job template is create at installation time and it does not set any limitation on resources available in the system.

To define new job templates, click on "New…" in the action pane (Figure 3); a job template wizard drive you to set few parameters (Figure 4)

Figure 4: job template wizard

After specifying job template name click on Next button

Figure 5: limitation of how long a job can run in the HPC cluster

We do not specify any limitation on maximum amount of time a job can run in our HPC cluster (Figure 3).
Next steps in the job template wizard is to set the priority of job; we do not have interest in our test in contention of simultaneous jobs so we left the Priority to Normal default).

Figure 6: job priority set to Normal

Figure 7: option to set a constraint on project name

Last step in job template creation is the node group; default node group “ComputeNodes” include in our case EXCN001 and EXCN002 (Figure 8)

Figure 8: node group

At the end of wizard procedure the new job template appears in the list (Figure 9)

Figure 9: list of job templates

We need to set in the job template the maximum number of cores; right click-on job template and choose “Edit…” (Figure 10)

Figure 10: edit the new job template

In the job template Editor click on Add button and select “Maximum Cores” (Figure 11)

Figure 11: Maximum number of cores

In the Max Value set the value two; in this case when the use will submit a job specifying the job template named “JobTemplate2Cores” it can be used no more than two cores (Figure 12).

Figure 12: job template with maximum two cores

We have as shortcut “Copy” to create a duplicate job template (Figure 13)

Figure 13: copy operation to duplicate the job template

Editing the new copied template we can set the right name and the new max value (Figure 14)

Figure 14: job template with maximum 4 cores

Repeating the procedure we have following list of job templates

Figure 15: list o of job templates

A short description of job templates is reported in the Table 1:

JobTemplate2Cores: Maximum number of usable cores=2
JobTemplate4Cores: Maximum number of usable cores=4
JobTemplate8Cores: Maximum number of usable cores=8
JobTemplate10Cores: Maximum number of usable cores=10
JobTemplate12Cores: Maximum number of usable cores=12
Default: it is the default job template create in installation procedure. tis job template allocate all available cores (in our case 16 cores because we have two XL Azure IaaS VMs)

To run all simulations we consider a circular pinhole in the plane z=0 with radius R (Figure 16) and we fix the input parameters:

Figure 16: pinhole in the plane z=0

R = 0.005m (radius of circular pinhole in the plane z=0)

N_x = 128 (number of samples in Lx segment)

N_y = 128 (number of samples in Ly segment)

L_x = 0.002 m (segment centered around x axis)

L_y = 0.002 m (segment centered around y axis)

N_p = 128(number of samples in Lp segment)

N_q = 128 (number of segment in Lq segment)

L_p = 0.005 m (segment centered around the p axis)

L_q = 0.005 m (segment centered around the q axis)

z = 0.25 m

l = 6.94x10-7 m (light length)

V₀ = 10 (electromagnetic field value on the plane (p,q,z=0))

In Figure 17 is shown the outcome of simulation for the diffraction field of a circular pinhole.

Figure 17: outcome of simulation

The Sheet1 has few buttons to change the orientation and prospective.

Though the submission of same job with different job templates, we have the chart with elapsed times (Figure 18) Figure 18: elapsed times with different job templates

The chart seems strongly non linear mostly due to the initial value with 2 cores; excluding the values with 2 cores and 4 cores and re-plotting we have the new chart:

Figure 19: elapsed times with different scale factor

The chart in Figure 19 points out a good behavior with strong reduction of elapsed time when the number of available cores increase. Charts in Figure 18-Figure 19 describe the behavior of Excel workbook with block in HPC_Partition equal to 50. it is useful to compare performance of Excel workbook with different block sizes; chart in Figure 20 shows the simulation elapsed time for block size equal to 50 vs block size equal to 5.

With low number of cores 2,4,8 there is no much difference in block size; influence of block size appears in HPC job with larger number of cores. A larger block size reduce traffic between the client and HPC head node carry on a better efficiency in CPU usage (with larger block size every single HpcServiceHost.exe process waits less time).