Andea helps Burger King reduce the long loading time of selected ETLs

How Andea helped Burger King SEE improve its data warehouse design and performance

Every prominent corporation that operates with the usage of a large quantity of data faces a critical challenge–how to store and manage it. In many instances, a data warehouse is an answer. Burger King SEE is one of such companies. Their priority was to find a supplier who would take responsibility for data warehouse management and maintenance so that the database is an efficient, dependable, and a duplicate-free tool. We accepted the challenge! As a result, our client reduced the loading time of selected ETLs by 72%.

Thursday, September 17, 2020

Burger King SEE (“BKSEE”) was founded in 2014 as a joint venture with Burger King Corporation. The company is a Burger King Master Franchisee in Italy and operates all the Burger King restaurants in Poland. The Group and its franchisees run together over 700 restaurants, generating over EUR 1 billion in sales, comprising four major brands in seven countries. Here is the list of them:

BKSEE needed a well-designed data warehouse that would enable users to analyze data quickly, flexibly, and efficiently.

Defining problems and needs

The company deployed a data warehouse solution that provided near real-time access to consolidated global sales data. The solution was developed utilizing the SQL Server Integration Services (SSIS) platform. The client was struggling with:

Long loading times for selected ETLs
Difficulties with loading accurate and current data within the available time frame
Developing new ETLs

BKSEE entered into cooperation with Andea to help improve some of the problematic areas and maintain the data warehouse solution on an ongoing basis.

The Audit

We began our work by conducting a thorough audit of the current state of the BKSEE data warehouse. We had to verify and check their server’s performance and find potential bottlenecks that slowed it down.

At this stage, we knew what tasks we had to perform and what are the BKSEE expectations. We could proceed to the implementation phase.

Improvement implementation

We divided the implementation phase into three stages: diagnosis, treatment, and cure.

Diagnosis

First of all, we had to verify irregularities that could cause problems in the functioning of the implemented solutions. In our opinion, it was particularly important to conduct a server performance test before implementation. Thanks to this, at the very beginning of the cooperation, we were able to diagnose potential problems that could reduce the effectiveness of the implemented functionalities.

We checked the configuration and backup processes. Actions taken at the diagnosis stage allowed us to prepare tools that sped up the server's work. One of the implemented solutions was the Database Monitoring and Alerting System that notifies the users about unexpected server’s events.

At the diagnosis stage, we also performed general checks of the data warehouse. Not only to verify the database performance bottlenecks but also to analyze the server configuration control and backup policies.

Treatment

The audit and the diagnosis stage allowed us to develop a list of necessary work that needed to be done to speed up the server processes.

We introduced changes what improved the BKSEE key load actions as they ensured database consistency and optimal storage usage by landing tables. We noticed that, thanks to the applied solutions, we have significantly improved the loading operations.

We focused our attention on performance monitoring and tuning the database to ensure its correct and stable functioning. Constant observation of the effects allowed us to see that, at this stage, it is necessary to increase the performance of the database server to make it capable of reducing the loading time of selected ETLs.

Cure

First, we identified the most time-consuming operations. Basing on this, we modified the SQL code, thanks to which the loading time of the three main packages was reduced by more than 54%. The last essential step was implementing new indexes, which decreased the loading time of large SSIS packages by another 40%.

The results

Our work, from analysis to implementation, allowed Burger King SEE to speed-up the loading time of selected ETLs. When it comes to data regarding the purchase of restaurant physical equipment, the time was reduced by over 55%, while for data regarding detailed sales per restaurant, the loading time decreased by over 72%.

Ongoing maintenance

The cooperation with BKSEE is constantly developing and we continue to do all the maintenance work, including in particular:

Resolution of SSIS ETL and data warehouse issues (remote level 2/3 support)
Development and delivery of new ETL functions along with improvements to existing solutions
Implementation of new source systems and implementation of the new FACTS and DIMENSIONS tables
Delivery of unit and functional tests (scripts creation, execution, and evidence)
Updating existing project documentation

To show you the scale of our assignment, in the last 18 months, BKSEE logged about 190 tickets, including:

About 88 Change Requests
About 103 Issues

Working with Burger King SEE, we were able to restructure their data warehouse, which is now well-designed, processes queries much quicker, and provides users with high data throughput and sufficient flexibility.

Andea turned out to be a great partner to collaborate with. Implementing their recommendations has significantly improved the performance of our solution. Simultaneously, they enabled the addition of new source systems and further development. Throughout the process, maintenance, implementation of corrections, and change requests have always been carried out successfully. We consider Andea to be both a flexible and capable partner.

Jacek Wałaszek

Burger King SEE, BI Manager