excel in azure.jpg
HOW TO

Distributed Computing: How to Move Excel Processing to Azure [2018]

Embrace your models with Azure Excel capabilities
  
Last updated: June 25, 2018

An estimated 750 million people use Excel regularly, making it one of the most important software programs of all time. But is Excel the right tool for distributed computing?

In almost every sector, there are organisations that have pushed Microsoft Excel to its limit. In fact, there are many enterprises running business-critical processes in server-based Excel compute grids, which can no longer support their business needs.

With this in mind, we built a scalable processing solution in Microsoft Azure to run Excel workbooks without the need for Excel. Our Azure Calculation Engine (ACE) enables organisations to focus on core business processes, while leaving the distributed computing to Azure.

modelling guide

Distributed Processing: when Excel is not Enough

Excel is one of the world’s most popular data processing tools for a reason; it’s highly accessible, functional and has a wide range of use cases. However, despite its functionality, Excel is fundamentally a desktop application and not a background processing engine. For those working on highly complex workbooks, Excel processing has a number of limitations:

  • Restricted memory and an inability to scale on-demand
  • Limited processing power
  • Expensive on-premises infrastructure investments
  • Limited security and control

Organisations often realise these limitations when expanding their workbooks to more than one user. The image below illustrates one user performing an Excel task on their local machine, storing the workbook on their network.

Distributed-excel-processing

When the workbook becomes more critical and other users start to work on it, things get a little more complex. What was once a relatively simple system, now requires added security and version control.

Screen Shot 2018-04-18 at 09.44.21

From here, if a workbook becomes a business-critical process it needs the ability to scale and the team responsible must work out a way to distribute the processing. To solve this, a cluster server grid shown below is added and the team work to orchestrate the distributed computing environment.

Distributed-excel-processing

However, the management of a cluster like this is often problematic and the associated licensing can get very expensive.

The Advantages of Distributed Computing in the Cloud

That’s why RedPixie built the Azure Calculation Engine, swapping out the cluster server grid for a more flexible cloud solution. ACE is a highly-scalable distributed processing engine, capable of calculating hundreds or thousands of workbooks in parallel, all fronted by a simple API.

Using this approach, organisations can significantly reduce the run-times of key workloads. The distribution service is run on as many nodes as can sensibly distribute the work, using the cloud’s economies of scale to avoid unreasonable licensing fees.  

The solution has a host of use cases, spanning various industries, including:

  • Monte Carlo type simulations
  • Optimisation problems
  • Financial or business forecasting
  • Clinical modelling

Distributed-excel-processing

As well as slashing runtimes, broader benefits of ACE include:

  • Elastic scaling: the service automatically scales up and down, switching processing nodes on and off depending on the workload.
  • Flexibility; the user can prioritise speed or cost efficiency.
  • No hardware costs: no upfront hardware costs as it is a native cloud service.
  • Per minute pricing: when nodes are switched off we stop paying for them.
  • Robust monitoring and reporting: detailed telemetry is built-in.
  • Robust modern architecture: we use best practices and patterns to build a reliable, decoupled cloud system.

We have introduced ACE to a range of clients. So far, they have seen incredible gains in processing speed and efficiency -  our predictions suggest that what once took seven hours could now be completed in two minutes.

For leading property company Telereal Trillium, ACE was a key component in their cloud success story. Thanks to ACE, the organisation slashed their processing times and modernised their financial forecasting models.

ACE in Action: Building a Distributed Computing Environment in Azure

In this next section, I’ll briefly guide you through the three main steps of distributed processing in Azure.

1. Prepare your Data

Find a way to calculate Excel workbooks through code. While you can achieve this by using a Visual Basic for Applications (VBA), it can be a challenging, time consuming process. Instead, we refigure VBA into cloud compatible languages C# and R, which perform far better.

The beauty of ACE is there’s no need to convert your Excel workbooks into another language. ACE calculates these workbooks intuitively and produces Excel-compatible outputs that can be accessed at any time from an Azure SQL database.   

2. Define the necessary Azure Architecture

In this context, we decided to use the Azure Service Bus Queue with the Queue-based load leveling pattern.

We also chose to use the container platform Docker. Docker allows you to take your running software, bundle it up into a consistent format, and run it on any infrastructure that knows how to handle a container. This results in a highly scalable model, perfect for distributed processing.

Finally, we licensed a 3rd party component to handle the Excel processing. This system is successful due to its ability to manage numerous workloads and has been very strong across various performance and compatibility tests.

3. Run your Distributed Processing Calculations


In ACE, auto-scaling workers pull each task from the queue for processing. Since the solution works as an Azure service, Excel is not needed for any of these calculations. Once the calculation complete, the output is written to the relevant storage location and progress is logged in an Azure storage table.

Getting more from your Distributed Computing Projects

While ACE is not meant to replace or accelerate Excel on individual computers, the solution is a much more efficient way of running workbooks in parallel at scale.  

As we know, server-based Excel processing does not have the same level of elasticity and, as business demands evolve, it cannot always keep up. However, the program is still an essential modelling tool and will influence distributed processing for many years to come. ACE allows organisations with complex workbooks to use Excel in a more sophisticated way, without having to reskill or overhaul legacy infrastructure.

Excel is just one example of what true distributed processing can do for your organisation.

ACE has the potential to accelerate a number of business-critical tasks. Using APIs to integrate with other applications, modelling teams can get the answers they need to essential business questions faster and with less fuss.

To learn more about the ways Azure is revolutionising distributed Excel processing, download our free ACE brochure.

 

excel performance guide

Topics: ACE, Azure, Excel

KNOWLEDGE IS POWERGet weekly email insights from RedPixie

IT Partner Success Choose correctly and boost your value by 56% →
surface banner.jpg