Highlights

Data merger and consolidation

A major merger created a perfect storm of requirements for the businesses, which needed to combine the data from two large organisations into a coherent data model and satisfy the needs of the combined business.

Very large data volumes were involved, with some databases containing over two billion rows. The data needed to be consolidated and associated reporting re-engineered to utilise the merged data efficiently.

Retail systems, in the process of being upgraded across the retail estate of over 600 shops, also needed to be integrated. The customer needed a partner with knowledge and expertise of a wide range of technologies, and the capability to deliver solutions quickly.

The situation was compounded by a simultaneous upgrade of the core systems for over 600 retail outlets.  Xoomworks Technology was uniquely equipped to provide state-of-the-art, robust solutions to these challenges.

New data model with mission-critical reporting

Xoomworks Technology picked up the challenge and quickly mobilised their ‘Big Data’ team to undertake a number of projects:

1. Data consolidation and migration to the cloud

Data from the two companies (in Oracle and SQL Server databases) was migrated into a single Data Warehouse in the cloud, using Amazon Web Services (AWS). The team used Talend for the Extract, Transform and Load (ETL) process.

Data was classified according to the reports needed by the business and loaded to AWS S3, and then to either AWS RDS PostgreSQL for the low latency data or AWS Redshift for the high latency data. As part of the project, tuning was carried out to ensure that reports were performed within the required levels. As a result of this work, the business had a unified set of data enabling reporting across the merged organisation on time and on budget. Reporting performance was transformed, with some reports operating hundreds of times quicker than previously.

2. New retail system integration into data warehouse

A staged rollout of a new system to all retail outlets was in progress at the same time as the merger project. This required running parallel data flows from the old and new systems to the AWS Data Warehouse. A new data delivery mechanism, designed to take data from both the old and new systems, was developed using Python.

The heritage data was migrated to the Redshift Data Warehouse, and new data flows were designed to feed data from the new systems. In addition, some processing of data was moved from the front-end retail systems to the Data Warehouse, reducing the processing power required in shops. This work enabled the customer to successfully complete the systems upgrade to over 600 retail outlets while maintaining their ability to report across both businesses.

3. New data model

A new data model was required that combined data from multiple sources for both businesses. This was developed by Xoomworks Technology. Data flows, and task sequences were defined using the Airflow platform. The data volumes were vast, with over 2 billion rows in the final database. Tasks were organised and orchestrated using Airflow Directed Acyclic Graphs (DAGs), ensuring data integrity and the correct sequencing of processes. The completed functional data model was delivered in less than 3 months, allowing the customer’s analytics teams to develop mission-critical reporting of betting activity and associated financials across the whole organisation.

4. Sub-minute latency on key reporting

Key events, such as very high-value bets or pay-outs need to be reported immediately to the Loss Prevention team to minimise risk and liabilities. This project delivered a ‘low latency’ alert solution for the business which provided sub-minute alerts for these types of events. The AWS Kinesis data streaming service was used to handle the gigabytes of data per second required by this feature. The business is now alerted of events that require intervention to minimise risk within seconds, maintaining its profitability.

5. Reporting Integration

Existing reports used Microsoft SQL Server Reporting Services (SSRS) to deliver a wide of business reports. As a result of the changes to the data platforms and architecture, they needed to integrate these reports into the Airflow technology. An Airflow Operator was built in Python to connect to the SSRS and execute and deliver the reports. The project was delivered despite the lack of any documentation of the existing systems. The combined organisation was able to run all existing and new operational and financial reports, incorporating data from all areas, as a result of this work.

Xoomworks resources integrated quickly and completely with the in-house teams, providing an expert and effective service to the business.

• Merge and migration of Big Data to cloud solution Integration of retail system data flows during an upgrade roll-out
• New data model empowering analytics teams to develop reporting
• Critical alerts delivered to key personnel within seconds rather than minutes
• Enterprise-wide reporting integrated with new data technology

Contact us to discuss how a flexible partnership can support your technology requirements and business growth.