Database Design
New data warehouse solution helps G’s Fresh business intelligence flourish
Project: change++ delivered a cloud data warehouse solution to give G’s Fresh valuable insight into sales and operational data for its global fresh produce business.
Services: Azure Cloud, SQL Server, Power BI, Stakeholder Engagement.
Results: Employees across the business can access a single source of trusted data to make key business decisions and ensure robust forecasting, budgeting and reporting – saving hundreds of hours.
The Client
First established in 1952, G’s Fresh has grown to be one of Europe’s leading fresh produce companies. G’s supplies quality salads, vegetables and other produce to major retailers in the UK, Europe and North America, managing the entire process from seed to shelf.
Their Needs
G’s Fresh supplies fresh produce on a just-in-time basis. Orders are processed quickly and, in some instances, go directly to the field for harvesting. G’s agricultural technology and operational systems produce a wealth of data including; weather sensors and drones that scan the farms, IoT systems on board its agricultural equipment and sales and order processing systems.
Despite these large volumes of data, inefficiencies in capturing, storing and managing it meant that G’s Fresh wasn’t getting the true value from it. Employees could spend days collating data for a single report. This wasn’t ideal for a business where everything is done in a short space of time and where things change daily depending on the weather, retailers’ promotions and even the ‘Nigella effect’.
Alongside the effort of producing 75 weekly sales reports by product, market, category, etc. the reports often failed to tally up.
G’s Fresh knew that it was time to pave the way for the company’s new data strategy.
What we did
G’s Fresh’s Head of Business Intelligence, Data and Applications, Surb Sheri, had previously worked with the team at change++, so he knew just who to call to help develop and deliver the cloud-based data warehouse solution. We helped him overcome a major resource gap so he could increase the scope of the development without building a massive DevOps team.
One of the major challenges for any data warehouse programme is bringing together data from disparate sources. The project started with sales information from the ERP system but had to incorporate data from Excel and CSV files as well as other formats. We built a process to ingest these files into the Azure data factory, taking into account different cadences. For example, some files needed uploading every day and some every time someone updated the information.
In particular, we helped G’s Fresh with an innovative way to tackle incremental load. Extract-Transform-Load (ETL) operations are among the most challenging tasks in data warehousing. One of the trickiest tasks is identifying incremental datasets from the sources. Typically ETL runs in batch mode once a day, so it’s important to identify data that’s new or has been modified since the last run. We created a templated process so instead of writing an ETL file for every process, G’s Fresh’s IT team fills in a form to put the data into the correct structure to load. The process documentation gave the G’s Fresh team an interactive view of the complex process with the ability to run it with live or simulated data. Our proactive and independent problem-solving approach was invaluable to the project.
Our change++ BI experts worked with G’s Fresh’s team to develop, test, document and deploy the whole system within a matter of weeks. As Surb says, “The change++ team were very quick to accommodate the ways of working at G’s and brought a lot of experience in solving problems with other companies. They are very forward thinking and could talk to the business and get buy-in to what we were looking to achieve.”
The Results
G’s Fresh now enjoys automated processes that have improved productivity and saved a lot of time throughout the organisation. The data warehouse provides accurate data for sound decision making and the business now uses the budget, forecast and sales data resource daily. As Surb explains, “The category managers now get the information that they need and can focus on achieving their targets. Silence is golden! If we don’t hear anything about the data warehouse, that’s good news.”
Benefits include:
- Skilled additional resource relieved the load on the internal team
- Single source of truth for robust, reliable decision-making
- A trusted data warehouse that is used daily for budgeting, forecasting and reporting
- An innovative approach to ETL incremental load that saves IT time and resource
- Hundreds of man hours saved building reports each month
- Buy-in for the new data structure and way of working across the business
- A Head of Business Intelligence, Data and Applications who can sleep well at night