The Co-op leverages Azure Synapse Analytics and Power BI to build a stronger community

At the heart of every co-operative business is a community committed to shared values and business goals to grow and enhance the co-operative experience. For the UK-based The Co-op Group—one of the largest co-operatives in the world—this means collecting and reporting massive amounts of data.  

The Co-op Group is a multi-faceted organization offering an array of retail and service offerings. It is one of the UK’s largest food retailers, one of the UK’s leading funeral service providers, a major general insurer, and a provider of legal services. It has more than four million members worldwide. Because The Co-op is owned by its members, it is critical for the organization to measure how active members are in the community, how often they shop, and even their purchasing trends. This data helps The Co-op develop new ways to increase member engagement and participation in co-op operations and events. 

To collect and record member metrics and other key data, The Co-op relied on a number of different reporting tools, each with different reporting functions. A portion of the data was simply entered in Excel and then distributed via email to the Co-op executive and management once a week. As such, up-to-date reports were not immediately accessible, reporting knowledge could not be easily shared amongst colleagues, and gaining access to the right data at the right time was a constant challenge. And, with the data being on-premises it was difficult to get quick insights and keep up with a rapidly changing market.  

Building a modern reporting solution with Power BI and Azure Synapse Analytics 

The need for standardization and scalability pushed The Co-op to look for a new, long-term reporting tool. The organization had a strategic vision of migrating their reporting capabilities to the cloud so that users could access data and reporting all in the same place. 

Rob McKendrick, Head of Data and Insights at The Co-op and his team determined that Microsoft Power BI would be easy to adopt, could enable self-service throughout the organization, and was the right tool to align with The Co-op’s goals. The Co-op’s existing relationship with Microsoft assisted in the quick launch of a Power BI proof of concept for membership dashboards.  

“We decided that our member dashboards would be our first use case and proof of concept using Power BI to not only give people the flexibility of something that we could update on a daily basis, but also something that offered a stronger security model,” says McKendrick.

Taking data and Power BI reporting to the next level 

Choosing Power BI as their primary reporting tool led The Co-op to explore more of the Azure ecosystem to create a seamless platform. To keep up with The Co-op’s growing reporting needs, McKendrick and his team wanted to take data and Power BI reporting to the next level with an updated data warehousing solution. With the help of Microsoft, the team chose Azure Synapse Analytics for the Co-op's strategic platform. The platform offers the end-to-end security of Azure Synapse Analytics, the power of Azure Databricks, and easy integration into the rest of the Microsoft stack.  

With this new platform, data orchestration is handled via Azure Data Factory, with data eventually landing in Azure Data Lake Gen 2. While Azure Data Factory is used for ETL processes and orchestration, most of the code is written in Python and executed using Azure Databricks. 

After data is processed within Azure Databricks, it is pushed into Azure Synapse Analytics to provide a consumer view of data and simplify Power BI integrations. Azure Databricks are used to prep for Azure Synapse Analytics data transformations that will help The Co-op process raw data into predictions and insights at scale. After data has been prepped and transformed, it is then ingested into Power BI. 

Managing legacy data while moving to the cloud

The Co-op’s overarching solution is called Data EcoSystem (DES) and all strategic and legacy solutions are managed within it. Due to the rich history of the organization, The Co-op has a large number of legacy solutions along with massive amounts of data, both of which are being migrated to DES. The largest of these systems—The Retail Data Platform—was recently moved to the dedicated SQL pool and supports the majority of data reporting for the organization’s £9 billion food business. 

With Azure Synapse Analytics as the strategic platform for data warehousing, The Co-op needed a solution to migrate the 40-terabyte system to the cloud with minimal rewriting and no end user impacts. McKendrick’s team selected Datometry, a Microsoft partner, to support the effort. Azure Synapse Analytics helped the team decommission The Co-op’s on-premises warehouse appliance and run the system in Azure with increased efficiency, stability, and scalability. 

Using data to make strategic decisions 

With their new architecture in place and on-premises data gradually being moved to the cloud, The Co-op is now focused on migrating their reporting capabilities into the cloud so that users can access data and reporting all in the same place. McKendrick’s team is concurrently working to build major, company-wide reports that enable end users to make those decisions more effectively.  

For The Co-op, using company-wide reporting to identify new ways to support a tailored in-store shopping experience is a priority. The organization’s retail stores are unique in their layout and customer experience. To understand queue times and patterns across various stores McKendrick’s team created the OurService Queues Insight report which captures data from self-checkout kiosks. This information helps retail support center employees make recommendations on how to optimize queue times and staff locations.  

Another important tool is The Co-op Scorecard. Used primarily by senior management, this dashboard tracks performance and highlights business strategies and key measures. The color of a measure on the dashboard signifies whether the measure has hit its target or not. The graphic beneath the value shows the trendline of that measure over the past two years. Users can also click on a card to drill-through to secondary KPIs and business unit splits. 

Growing from a strong foundation

With the rapid adoption of Power BI, users across The Co-op initially had a lot of questions. In response, the Data and Insights team worked with the Co-op’s Retail and Analytics and BI team, led by Chris Allen to create a Center of Excellence (CoE). Driven by Office 365 the CoE is designed to help increase user confidence in Power BI in a self-service environment.  

With the expansive reporting capabilities of Power BI, users gain critical insights to understand the value behind certain actions. This has increased efficiency and created a data-first culture at The Co-op that supports the cooperative community. Data-informed decisions help create better member and customer experiences.  

“Creating something automated in Power BI is saving all that manual time spent collating data and reporting on it and allowing people more time to do the analysis and actually make decisions on data,” says Allen.  

Looking ahead, The Co-op is utilizing Azure Purview to integrate data governance within their growing architecture to get even more out of their data. This will help continue to enhance both the data first culture at The Co-op, as well as the overall co-operative community experience. 

Learn more about Co-op at Twitter, Facebook, LinkedIn, Instagram, and the company's website.