Running parallel Excel on Windows HPC 2008 R2 hosted in Windows Azure IaaS VMs: Part 4
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 Lp centered around the p axis and Lq centered around the q axis
- a segment Xm centered around the x axis and Yn centered aroung the y axis
- Np the number of samples in the p axis
- Nq the number of samples in the q axis
- Nm the number of samples in the x axis
- Nn 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 rjl between the sample (pj,ql,z=0) and the sample (xm,yn,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(xm,yn,z) of electromagnetic field is equal to the square of module of electromagnetic field V(xm,yn,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:
Login with HPC administrator you see presence of default job template (Figure 3)
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)
Nx = 128 (number of samples in Lx segment)
Ny = 128 (number of samples in Ly segment)
Lx = 0.002 m (segment centered around x axis)
Ly = 0.002 m (segment centered around y axis)
Np = 128(number of samples in Lp segment)
Nq = 128 (number of segment in Lq segment)
Lp = 0.005 m (segment centered around the p axis)
Lq = 0.005 m (segment centered around the q axis)
z = 0.25 m
l = 6.94x10-7 m (light length)
V0 = 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).
Figure 20: simulation elapsed times with different block size
HPC heatmap in Figure 21-Figure 24 give an rough idea of CPU utilization and cores allocation with different job templates.
Figure 21: HPC heatmap when runs the job with 16 cores
Figure 20: HPC heatmap when runs a job with 12 cores
Figure 23: HPC heatmap when runs the job with 10 cores
Figure 24: HPC heatmap when runs the job with 4 cores
Here you have all elements to write your Excel workbook and runs on HPC cluster in Azure IaaS VMs; enjoy it!
Fresnel.xlsb
Fresnel simulation