Large beverage manufacturer consolidates and centralize data with Conductor4SQL.
By Trevor Niemack.
On the 2nd January 2014 I was appointed as the Data Architect for SAB Miller Africa & Asia. My first day on the job I was given login credentials to a centralized data server and to all of the in countries data servers. I spent the first month going through everything that was setup, integrated and got an understanding of all of the dependencies as well.
The architecture at the time was built to enable the replication of all transactional and master data from all countries which would then need to be consolidated into one database for the purposes of strategic and tactical reporting.
- Standard SQL built-in functions were used to replicate data so when a failure happened you would have to re-do this configuration which would mean, having to wait in some instances up to 3 weeks before all of the data was back in line.
- Expensive Bandwidth in some African countries meant slow data transmission and lots of failure which lead to redoing the replication on a regular basis.
- Damages to lines meant days without data and eventually having to re-do replication configuration.
- No automatic consolidation of central data which meant sifting through massive complicated scripts if problems occurred.
- Not all data was being replicated so if you needed to add an additional field or a new database for a new operation or if you needed to remove a current operation then you would need to once again sift through the monster scripts.
- Sending that data on, once it was replicated was nearly impossible based on the size and complexity
- Not all countries had the same data structure which added more complexity and required some fancy footwork to navigate.
- Data was a day behind as we could not get the Monster scripts to run more frequently.
After a year of trying to optimize this environment we came to the decision that this was not sustainable and that we needed a better way forward. I started my search looking for options/alternatives and everything I came to find was expensive and would need a fair amount of technical expertise to ensure we would be able to get the desired outputs.
I then came across Conductor4SQL by EnterpriseWorx and contacted them, after a few weeks of engagement we decided to give this product a try with a simple POC whereby we would sync the data for one of our African operations. At the time I was moving into a global role under Solutions Architect but still in the Data & Analytics space, but this remained a key focus delivery.
The team at EWX proceeded to setup Conductor4SQL and sync data, it took about 3 days as this operation was one of our biggest but once it was done then data started coming through seamlessly and even when we simulated a 3 day break in transmissions, the transactions just built up at the country server. Once transmission resumed, the data simply trickled down to the central server and in a matter of hours we were back in line to a live feed of data.
We then included another country so we could see how the consolidation would take place and that was amazingly simple, with just a few clicks I could add various tables from any other operation, and they would simply consolidate into the appropriate tables. I could maintain the data structure to further include operations with different data structures then sync them with ease.
Once all country operations where syncing live, we were then able to get all the consolidated data either pushed individually or consolidated into our cloud platform and make all of our data available globally for the entire organization to make use of.
With the implementation of Conductor4SQL we were able to provide the following solutions:
- Live data synchronization
- Automatic consolidation without the need for additional scripting or intervention
- Automatic restart of the syncing process when lines of communication went down and came back up days later.
- We could easily cater for different data structures
- We could now also add or remove operations through a simple interface
- We could prioritize the streams of data ensuring all reporting requirements were met
- Due to Conductor4SQL having the ability to compress the data through the sync process, slow networks were no longer an issue.
The implementation of Conductor4SQL took 3 months to roll out to all of our countries and their operations but the ROI was priceless to the organization, Data concerns became a thing of the past and we normalized the business into having the mentality that we have our data and can use it whenever we needed. So, trust in our ability to get the data to them was our main goal and that was achieved.
011 301 0900