Pioneering Successful Data Warehouse Modernisation
Journey of one of the world’s largest consumer co-operatives: How Co-op modernised their legacy data warehouse to Azure Synapse with Datometry
Number of Tables
Where It Began
This is a story of pioneering with data. I like that because the Co-op is a pioneering movement, started in Rochdale in 1844 to free common people from the oppression of the tyranny of unethical food production and provision. Now I’m not suggesting for a moment that shifting from a legacy on-premise database to the cloud is a comparatively revolutionary liberation, but if you ask the data scientists in my team, they might give you a different perspective.
The story began two years ago with a technology and data architecture review to assess options to modernize our enterprise, one of which was a re-engineering of our data warehouse in the cloud.
We had a legacy on-premises self-managed data center in which our retail data warehouse lived. This platform had serviced the needs of the retail business for decades and built up to 10TB+ of live data in over 10,000 tables fed by 885 scheduled processes feeding 6000+ reports to 1000+ office and field-based users. A monster.
We started the journey thinking we had two options:
• We could reinvest in our on-premise data warehouse
• We could migrate our retail data warehouse SQL to the Cloud (with a number of vendor options)
The Data community was torn. Leveraging a vast array of data processing facilities in the cloud held enormous appeal, especially for our data scientists who yearned for the speed and agility it promised.
But our legacy SQL code runs through the veins of most of our data processes and the prospect of rewriting it, re-inserting it, re-testing it, re-deploying it – all without service interruption – to get to the same outputs in a more modern casing, was daunting. Payback would be long-term and there was high risk of service disruption.
Our business is complex, and our Technology and Data leaders were agreed that our ecosystem was not portable; we assessed and affirmed we could not simply and easily repoint our ETL.
We were also in the process of overhauling our data strategy and I didn’t want us to rush into a rewrite of the same business logic primarily for risk mitigation as the opportunity was to reimagine that logic. Our end-to-end processes were changing (soon be accelerated by Covid and Brexit) with our business adopting a greater online dimension and a parallel retail operations transformation in partnership with SAP.
We didn’t really have the capacity for another huge transformation program.
But the fact that our legacy data warehouse servers were nearing end of life with more frequent node failures and service outages, meant standing still was not an option either.
As part of their strategic response to our market soundings, Microsoft offered us an introduction to a third way – Datometry.
Co-op Virtualized Data Warehouse With Datometry
Datometry is SaaS database virtualization/emulation platform allowing existing applications to run natively on cloud data management systems without being rewritten. Datometry Hyper-Q virtualization offered the opportunity to accelerate Enterprise Database Modernisation to the cloud, seamlessly, while significantly reducing the TCO. In short, we could virtualize our legacy data warehouse processes in the cloud without needing to rewrite them. This met our strategic and operational goals.
The outcome of the review was the selection of Microsoft Azure Synapse cloud platform as our chosen strategic technology supported by Datometry to virtualize the legacy logic, retaining service without disruption, while we designed the new analytics layer in parallel.
12 months later and following a period of UAT and successful parallel running, we have fully cut over to Azure Synapse cloud, supported by Datometry and our Technology partners are decommissioning our on-premise legacy data warehouse servers. Our engineers can focus on removing redundancy in the code, and over the next 24 months modernize applications with a target end-state of a re-engineered platform to support our modern, digital, end-to-end business processes.
Co-op Successfully Moved On Azure Synapse
Through the process of database virtualization, we have learned a lot about our legacy data, and we have taken the opportunity to rewrite around 15% of the code where it was evident doing so would remove unnecessary complexity and increase speed and reliability.
Some of our larger batch transfers, such as EPOS and daily sales, needed tuning in their new cloud environment, for example. It’s not a lift and shift that magically happens by a removal company while you put your feet up and enjoy a cuppa; it requires investment in engineering, service and testing. We didn’t have a huge team assigned and in retrospect, it’s one of those occasions where more resources could have delivered confidence and benefits more swiftly.
Having said that, we have already seen impressive benefits in processing speed, with many processes taking hours less than SLA standards and using less capacity than anticipated.
Crucially we have also saved significant multi-million-pound sums annually compared to the cost of license and service of our existing data warehouse tech.
I’ve had the pleasure of sponsoring the programme and chairing the fortnightly data replatform Steering committee (SteerCo) over the past 18 months and Mike Waas,
Datometry CEO and Steve Crisp, Microsoft Data & AI Specialist for Retail, show up personally to offer practical and strategic support and advice and on occasion a gang of engineers. It’s been a real partnership and between Microsoft, the wonderful Data Engineering Community at the Co-op and Datometry; we’ve achieved far more than we set out to at our first SteerCo meeting which was of course just two months before we were consumed by a global pandemic.
The definition of a pioneer is ‘a person who begins or helps develop something new and prepares the way for others to follow and I like to think our Rochdale forebears will be looking down and tipping their flat caps to their Co-operative data engineering descendants.
Charlotte Lock (Data, Digital & Loyalty Director at the Co-op)
About the Co-op:
Co-op is one of the world’s largest consumer co-operatives with interests across food, funerals, insurance and legal services. Owned by millions of UK consumers, the Co-op operates 2,600 food stores, over 800 funeral homes and provides products to over 5,100 other stores, including those run by independent co-operative societies and through its wholesale business, Nisa Retail Limited. Employing over 62,000 people, the Co-op has an annual turnover of £11.5 billion. As well as having clear financial and operational objectives, the Co-op is a recognised leader for its social goals and community-led programmes. The Co-op exists to meet members’ needs and stand up for the things they believe in.
Datometry offers the database virtualization platform for the cloud. Datometry technology brings unprecedented simplicity to enterprises that want the benefits of
modern cloud databases. seamlessly and rapidly, without the complexities of reinventing existing applications and business processes. Find out more at https://www.datometry.com
About Microsoft Azure:
Microsoft Azure cloud platform is more than 200 products and cloud services designed to help you bring new solutions to life—to solve today’s challenges and create the future. Build, run, and manage applications across multiple clouds, on-premises, and at the edge, with the tools and frameworks of your choice.