Optimize Performance with Windows HPC Server 2008 R2 and Azure
Updated: June 12, 2013
Azure CAT: ContourGlobal Case Study
Author: Antonio Zurlo
This Case-Study describes a solution adopted by a customer, Contour Global, to speed-up long running Excel calculations by combining the computational power of Windows HPC Server and the flexibility of Azure. The resulting solution is a fully virtualized environment, hosted in Azure that allows ContourGlobal employees to access a High Performance Cluster from remote locations and run Excel based models in a fraction of the original time.
The technologies used are Windows HPC Server 2008 R2, HPC Services for Excel, Azure.
ContourGlobal Company Profile
Since their founding in 2005, they have helped create new, cleaner and more reliable generating capacity in locations throughout Eastern Europe, Africa and South America, boosting economic growth and environmental health. Consumers in many of the high-growth markets in which they operate are under-served. Without reliable, centralized sources of electricity, they are forced to depend on small, expensive residential-sized generators. Such unreliable – or non-existent – electricity supplies slow economic growth and inhibit new development. What’s more, existing, inefficient generating equipment only adds to local and global environmental concerns.
ContourGlobal is “fuel-agnostic,” which means they understand that every project has its own mix of optimal fuel resources, and they operate technology centers around the globe to ensure their plants remain at the forefront of efficiency and safety. In Togo, for example, they constructed a 100 MW plant designed to burn the natural gas that a new pipeline will be delivering to that sub-Saharan nation. Until the pipeline’s completion, the plant’s tri-fuel engines utilizes heavy fuel oil and diesel, helping ease dependence on hydroelectric resources which are limited during the region’s periodic droughts. With $209 million USD in financing from the Overseas Private Investment Corporation, this project represents the country’s single largest electricity investment ever.
ContourGlobal’s expertise lies in moving quickly to evaluate and implement the right development solutions, drawing on deep technical knowledge and extensive commercial contacts. Certainly their success has helped their own bottom line, but it also has helped stimulate regional development and now provides high-quality employment for more than 1,500 ContourGlobal employees around the globe.
One of the main activities ContourGlobal is involved in are Reverse Auctions to win new contracts for the production of electricity in underprivileged Countries. Several Countries have used reverse auctions to promote deployment of renewable energy. This combination of a competitive mechanism with a demand for renewable energy should reduce costs in achieving deployment goals.
These Auctions typically go on for 1 or 2 weeks, during this period ContourGlobal engineers need to tune their models and run large simulations to be able to make competitive bids. The models are written in Excel using VBA.
Contour Global had two major immediate needs:
Speed up the calculation of their Excel based stochastic simulations (Monte Carlo method, described below).
Run the simulation from any mobile device (laptop) with a simple internet connection.
Given Contour’s business model and the way they bid for contracts (reverse auction), it is crucial for them to be able to run accurate simulations, several hundreds or thousands of Monte Carlo paths, in the shortest time possible. Their models are entirely written in VBA and run on self-contained (no external data or libraries) Excel spreadsheets. Just to give an idea of their initial situation, one of their models running on a single laptop took about 45 minutes to perform 100 simulations. Given the performance an engineer, during an auction, could not run more than 100 simulations and had very limited chances to run the entire Monte Carlo model more than once or twice.
Since the introduction of Windows HPC Server 2008 R2, customers have the opportunity of running large Excel based calculation on multiple nodes of an HPC Server Cluster, this solution is known as HPC Services for Excel.
Windows HPC Server enables running multiple instances of Excel 2010 or Excel 2013 in a Windows HPC cluster, where each instance is running an independent calculation or iteration of the same workbook with a different dataset. Many complex and long-running workbooks (Monte Carlo simulations are a good example) run iteratively—that is, they perform a single calculation many times over different sets of input data. These workbooks might include intensive mathematical calculations contained in multiple worksheets, or they might contain complex Microsoft Visual Basic for Applications (VBA) functions.
When a workbook runs iteratively, the best option for parallelizing the calculation is to run the entire workbook in the cluster. In this model, individual calculations need not be split into component parts, but the overall calculation—generating the results from many individual calculations—can be run in parallel.
Every application that benefits from this solution has three parts: the workbook, a service, and a client. Excel 2010/2013 and Windows HPC Server 2008 R2/2012 must be installed on each cluster server. Microsoft Excel 2010 or Excel 2013 must be installed on the client computer. The client computer can run Windows Vista®, Windows 7 or Windows8 operating system. The next sections describe these components and show how they fit together.
Monte Carlo Methods
Monte Carlo methods (or Monte Carlo experiments) are a broad class of computationalalgorithms that rely on repeated random sampling to obtain numerical results i.e. by running simulations many times over in order to calculate those same probabilities heuristically just like actually playing and recording your results in a real casino situation: hence the name. They are often used in physical and mathematical problems and are most suited to be applied when it is impossible to obtain a closed-form expression or infeasible to apply a deterministic algorithm.
Monte Carlo methods vary, but tend to follow a particular pattern:
The workbook refers to a standard Excel workbook. This solution runs multiple instances of Excel 2010/2013 on cluster servers, meaning that it supports workbooks that use VBA or XLL add-ins or an Excel Add-in (XLA) as well as external resources (provided these resources are accessible from the servers).
In some cases, workbooks may need to be modified to work with this solution. When Excel 2010/2013 runs on the server, it does not support user interaction. Windows HPC Server includes a comprehensive pop-up manager that can handle occasional dialog boxes and pop-up messages, but it is not designed to support interactive Excel features: Users cannot create a PivotTable when running on the server, for example, because doing so requires user interaction.
When a workbook is used in this scenario, it is important to identify the input values and the output or result of the calculation. The input values might be cells within a worksheet in which the user enters a value, or they might be parameters to a VBA function. The output might be a second set of cells within a worksheet or the result of a VBA function. Identifying the workbook input and output values is important when developing the service, which will run on the cluster servers and execute the workbook calculation.
The service is a WCF service that controls the execution of the Excel 2010/2013 workbooks on the cluster servers. The service starts Excel, calculates a workbook, and returns results.
Windows HPC Server offers an SOA model that enables running WCF services in the cluster. In a Windows HPC cluster, the WCF Broker node handles managing and hosting the service library. The Scheduler handles assigning and managing compute resources. From the standpoint of the developer, using WCF removes all the complexity of hosting and managing the service. The developer need only build the calculation functions.
When running Excel workbooks in a Windows HPC cluster, the WCF service manages the Excel process used to calculate the workbook. It provides a single method, which calculates the workbook and returns the calculation results.
Once the service has started Excel on a server, it can interact with the workbook in either of two ways to provides access to the Excel object model (similar to using Excel automation on a client computer): Using the Excel object model, the service can read from and write to the workbook, and it can trigger calculation of the workbook. For example, in a simple workbook, the service might write values into some input cells, recalculate the workbook, and then read the values of some output cells.
The Excel object model also supports calling VBA methods within the workbook. If the workbook uses a VBA method to run a calculation, the WCF service can call the VBA method with input data and return the result when the calculation is complete.
The client is a program that controls the overall calculation. It is designed to work with the WCF service, and it tells the service which workbook to calculate along with the parameters or options to use, and it receives results when the calculation is complete (see Figure).
The client typically runs on a user’s client computer. In order to run an existing standalone Excel Workbook, like in the case of Contour Global, on HPC Server, a developer needs to implement 2 VBA Macros and add the necessary HPC Server references. It is important to notice that the original VBA source code doesn’t need to be modified and every interaction with the HPC Cluster is done in VBA.
This solution works with an HPC Server Cluster running entirely on-premise, in hybrid mode with the Head-Node and Broker-Node on-premise and the Compute Nodes in Azure or entirely in Azure.
Contour Global Scenario
For Contour Global, it was important to have the entire cluster in Azure for the following reasons:
An engineer in a remote location or participating to an auction can just connect his laptop to an Azure Virtual Machine and, from there, start the Excel calculations without having to rely on any on-premise Data Center and without having to worry about bandwidth between a client workstation and the HPC Server cluster.
All the Excel Workbooks can reside in Azure, in Blob Storage, so no Intellectual Property has to be loaded and reside on the engineer’s laptop.
If the engineer has a need to run a particularly heavy calculation (1000s of simulations) or needs to get the results faster, he just can add more Compute Nodes to the existing cluster by provisioning extra VMs.
The solution offers the level of flexibility needed by Contour Global:
Several offices around the World can use the same facility and upload/modify the necessary models.
Pay-per-Use when the cluster is not in use it can be shutdown.
The capacity can grow/shrink according to business needs.
- Several offices around the World can use the same facility and upload/modify the necessary models.
Here is a description of the environment we built for Contour Global:
In the diagram:
CGADServer (Large VM) hosts an Active Directory Domain for the solution, a necessary requirement for HPC Server.
CGHNode (eXtra Large VM) is the Head Node and Broker Node of the HPC Server Cluster.
CGNode01-04 (eXtra Large VMs) are the Compute Nodes of the HPC Cluster, Excel is installed on these nodes.
CGWorkstation (Large VM) is the client Workstation, the client spreadsheets run from this Workstation.
CGVirtualNetwork is the hosting Virtual Network for all the nodes.
CGStorage1 is the Azure Blob Storage where all the Spreadsheets are stored.
The 4 Compute Nodes provide a total capacity of 32 cores, the solution would simply scale by adding more Compute Nodes. In this scenario we recommend to use XL VMs for the Compute Nodes to minimize the amount of network traffic on the network and maximize the use of the Excel license, only one per VM is needed.
A user can RDP to CGWorkstation, select an Excel Spreadsheet, already loaded on the Workstation or load one from Blob Storage and start the calculation on the HPC Server cluster. The only requirement, for the user’s laptop, is an internet connection.
The original Excel-based Monte Carlo simulation, implementing one of the models, used to take about 45 minutes to complete 100 simulations on a single laptop. By parallelizing the calculation and running on a 32-core HPC Server Cluster, Contour was able to complete 2000 simulations in about 2 minutes. In business terms this means that Contour can now obtain a much more accurate evaluation of the model (1 order of magnitude higher) in a time that is 1 order of magnitude lower. Given the flexibility of the solution, they can pretty much make decisions on the level of accuracy and desired time to complete.
This is one of ContourGlobal’s spreadsheet implementing a model for Wind Farm efficiency evaluation through a Monte Carlo simulation. A user can run the model on his laptop by pushing the “Run Monte Carlo Simulation” button or, he can upload the model to Azure and run it on Azure on HPC Server. Most of the spreadsheets are kept in Azure Blob Storage and moved to the User Workstation to be calculated.
The VBA Code, before and after
The main calculation in the original VBA code is performed by the following “For” loop (“nseries” is the total number of simulations):
For k = 1 To nseries Application.ScreenUpdating = False Calculate Range(Cells(36 + 5 * (k - 1), 22), Cells(40 + 5 * (k - 1), 60)).Select Selection.Copy Range("V26").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.ScreenUpdating = True Calculate Cells(22 + k, 2) = k Cells(22 + k, 3) = Range("IRR_USD") Cells(22 + k, 4) = Range("IRR_BRL") Cells(22 + k, 5) = Range("CF_AVG") Cells(22 + k, 6) = Range("CF_MAX") Cells(22 + k, 7) = Range("CF_MIN") Cells(22 + k, 8) = Range("MIN_DSCR") Cells(22 + k, 9) = Range("CG_K") Cells(22 + k, 10) = Range("DEBT") Cells(22 + k, 11) = Range("LEV") Range("B22") = k Range(Cells(35 + k, 63), Cells(35 + k, 141)).Value = Range("BK34:EK34").Value statusMessage = "Calculated " & k & "/" & nseries Application.StatusBar = statusMessage Calculate Next k
The code that implements a single iteration of the “For” loop is the following:
'---------------------------------------------------------- ' HPC_Execute performs a single calculation step (or ' iteration). The input data will match whatever was ' returned from the HPC_Partition function, above. ' The return value from this function should be the ' results of the calculation; those results will be ' passed to the HPC_Merge macro, running on the desktop. '---------------------------------------------------------- Public Function HPC_Execute(data As Variant) As Variant Dim k As Integer Dim output_data(1, 23) Application.ScreenUpdating = True k = data(0) output_data(0, 0) = k Worksheets("Val").Select Range("d236:ap240").Value = Range(Cells(246 + 5 * (k - 1), 4), Cells(250 + 5 * (k - 1), 42)).Value Calculate output_data(0, 3) = Range("CF_AVG") output_data(0, 4) = Range("CF_MAX") output_data(0, 5) = Range("CF_MIN") output_data(0, 1) = Range("D223") output_data(0, 2) = Range("D216") Calculate HPC_Execute = output_data ' return value from the function End Function
The function HPC_Execute runs in parallel on each core of the cluster and takes as input the “next” index of the For loop. The output is stored into a Variant Data Structure and returned as output of the function.
The output of every HPC_Execute is sent as input to HPC_Merge that runs on the client side.
Here’s the code for HPC_Merge:
'---------------------------------------------------------- ' HPC_Merge is called after a single calculation step (or ' iteration) is complete; the input data will match ' whatever was returned from the HPC_Execute function, ' above. ' ' Use this function to store results: insert results into ' the spreadsheet, write to a database, write a text ' file, or anything else. '---------------------------------------------------------- Public Function HPC_Merge(data As Variant) Dim k As Integer k = data(0, 0) Sheets("Montecarlo").Select Application.ScreenUpdating = True Cells(22 + k, 2) = k Cells(22 + k, 3) = data(0, 1) Cells(22 + k, 4) = data(0, 2) Cells(22 + k, 5) = data(0, 3) Cells(22 + k, 6) = data(0, 4) Cells(22 + k, 7) = data(0, 5) Range("B22") = k Calculate ' update the status bar with the counters RcvRecords = RcvRecords + 1 UpdateStatus End Function
HPC Services for Excel by itself is a great solution to dramatically improve performance without requiring any major re-architecture of an existing Excel-VBA application. Combining this technology with the flexibility of Azure opens a whole new set of opportunities to run high-demanding analytic applications.
One aspect of this solution that could be greatly improved is deployment/un-deployment of the HPC Cluster. Today this is still a pretty manual effort or one that requires some non-trivial PowerShell scripting capability.
It would be great to have a web-based HPC Server Azure Portal where a user can simply request a certain number of Compute Nodes, or simply cores and opt for Excel as a resource available on the Compute Nodes. No need to deal with Active Directory, HPC Server configuration etc. For some users, even a multi-tenant HPC Server Cluster, offered as an Azure service and always ready, would be a great value.