The importance of data

Xero has appreciated the value of data since its inception, and its engagement with NOW Consulting goes back to ‘early days’, confirms Nathan Griffiths, Data Services Platform Lead at Xero in Auckland.

“Data is a key business asset. We recognise this and recognise the value in the data we hold in terms of providing services back to customers, creating our Small Business Insights report, and even in pages on our website which provide high-level overviews of business performance in the regions where we operate,” he explains.

“Our data is used by government analysts to assess trends within business, and it is used to inform innovation and provide services within Xero products. Essentially, data is a fundamental component in the services we offer.”

“The BI team where I work has been around for five or six years, so that’s more or less the time period in which we’ve used WhereScape RED for building the data warehouse.”

Smoothing the migration path

When Xero opted to move to Amazon Web Services, the implications were enormous: (as we stated earlier there were more than 59 billion records, 3,000 apps and 120 databases representing all data and applications for its 862,000 customers globally at the time), the sheer scale of the migration was epic. When talking about the data warehouse, Griffiths says “It meant a huge rearchitecting of all infrastructure and systems which involved changing from SQL Server to AWS Redshift as the database platform.

It was a major undertaking”. The data warehouse was part of this exercise; built over time it represented a considerable investment as it was created and constantly modified to meet changing business information demands. But Xero had a couple of aces up its sleeve which would ease the transfer of this structure to the new platform.

Steps to the cloud

The first step was an engagement with NOW Consulting, which performed a proof of concept to show that it was technically feasible to move from one platform to another while preserving the logic, processes and code of the data warehouse. “NOW delivered on that aspect successfully, then provided additional manpower for the actual migration,” says Griffiths.

One of the major challenges faced was what he describes as ‘a hefty SQL Server’ hosting the existing data. “We just could not recreate a system of that size in Amazon; we couldn’t just lift and shift, so we needed a new solution in Redshift which was an unknown.”

“There was a fair bit of risk and a lot of work”, says Griffiths. “With NOW’s help and with WhereScape RED as the tool to do it, we were able to reduce the complexity and risk to a point where it was feasible.”

There was a little pressure, too. “There wasn’t any question of it not being done as we had to have a solution in place; the consequences of no solution would be a lot of internal analytics and reporting would have ceased to work, it would impact our Customer Experience teams, our sales and marketing teams and even our product teams.”

NOW developed the necessary functionality within WhereScape RED which enabled connecting the existing data warehouse to the AWS Redshift database. “This was a new feature at the time called Templates; instead of code generated in WhereScape itself, the template has variables specifically for Redshift which allowed extremely flexible code generation. This was the key to moving to the new platform,” says Griffiths.

Successful migration results

In short, Griffiths credits NOW Consulting and the WhereScape RED product for enabling a successful migration with minimal disruption of a key asset in Xero’s data-driven approach. “It’s been extremely valuable, not only in terms of the migration itself, but after the fact,” he confirms.

One of main differences between SQL Server and AWS Redshift, he explains, is that the former has a scheduling agent. “By contrast, Redshift is just a database with no other features; you have to provide your own. WhereScape RED provides that to run code on the database and do certain things, so if we didn’t have WhereScape RED, we simply would not have the tools to go to work on our data.”

Despite its name, Redshift also does not provide any tools to manage data migration. WhereScape RED plugged that gap.

The key features, Griffiths says, include WhereScape RED’s provision of visual data representations and its ability to automatically generate code. “WhereScape RED is used daily by our BI developers who build data models, data marts and other models. The code is reliable and repeatable. That’s hugely useful, you can easily schedule and manage jobs and dependencies.”

Where in the past there were multiple environments, versions, tools and models, today it is all done within WhereScape RED and on AWS Redshift. That’s brought structure and reliability to the development process and our data initiatives,” Griffiths notes.

“The ability to generate custom code by building and modifying templates has been very successful. It has allowed us to take advantage of specific Redshift features; matching the platform with the development environment and scheduling capabilities of WhereScape has enabled us to make the leap to AWS with minimal pain.”