Case Study - Burger King SEE
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%.
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.
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.
We divided the implementation phase into three stages: diagnosis, treatment, and cure.
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.
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.
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%.
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