ETL & Data Modelling Solution for an Asia based Restaurant Chain

Background

A restaurant chain with various businesses and locations had their existing ETL system unable to manage the data appropriately and the processing usually resulted into huge pile of managed data.

Challenge

The existing ETL system was unable to manage the data appropriately and the processing usually resulted into huge pile of managed data. A new and apt ETL tool was required with streamlined processing and management that could churn out valuable insights out of their enormous data.

Some of the data challenges we faced were:

1. With the colossal amount of data out of their 100 restaurants, it was getting difficult to provide responsive insights in a stipulated time period.
2. The existing ETL system being inefficient reduced performance while the transformation operation was being performed. Team had to re-engineer the whole process to derive better performance.

Solution

After a detailed study of existing ETL model was done to identify the upcoming bottlenecks, areas of improvement were determined in order to come up with a robust and flexible data model. This data model was later proposed to provide a better business insight. One data model was redesigned to better the overall data performance. We built concurrent and scalable ETL solution using Microsoft SSIS.

Benefits

The new ETL delivered many benefits to the client including:

• Due to the firmly integrated codes, the new batch processes ran much faster and effective than before. The immense speed ensured a 70% time reduction in batch processes and enhanced the efficiency of the firm's processing capabilities
• The flexibility in the processing helped add newer data from sources that were not initially supported. This improved the capabilities of the data warehousing and ad-hoc reporting

Impact

• Useful insights generated helped the firm to take key business decisions with high quality data
• Better customer satisfaction achieved with continuous enhancement to the production environment using data load and balancing processes automation and reconciliation addition.