Taking control of Big Data in the national distribution center

Logo
Industry
Logistics / Retail
Location
Schafisheim, Switzerland
Goals
Organizing data from various sources, reducing data complexity

Products used

SQL Server
Microsoft SQL Server

Database Engine of the Data Warehouse (DWH)

SQL Server
Microsoft SQL Server Analysis Services

Tabular Model for Data Analysis

SQL Server
Microsoft Power BI

User Reports and Live Dashboards

In addition to well-known supermarkets, the Switzerland-based Coop Genossenschaft also includes other commercial enterprises and various production plants.

In Schafisheim, a high-capacity fulfillment center was built to pick and deliver frozen goods to the supermarkets scattered all over the country. The picking process is highly automated, which in turn posed a new set of problems: the large amount of data generated by the machines has become difficult to analyze with conventional tools.

The data was often simply too large for Excel, and to further complicate matters, each system had its own naming conventions — making merging the data very difficult.
These underlying systems could communicate with each other to fulfill their tasks, but they couldn't produce overarching reports — which made further optimization of the logistics center difficult.
Furthermore, to optimize for database performance, operational data had to be discarded shortly after the packets left the fulfillment center, making historical analysis impossible.

Coop wanted to take ownership of their data.

Centralizing Data

To solve these problems, a Data Warehouse (DWH) system was built, collecting data from the various systems, and storing it in a central SQL Server database. This database regularly pulled data from the existing production servers, merging and organizing them into a much simpler structure.

Image Description

Even though an ordinary Data Warehouse usually imports data only once a day (overnight), the data collection process here was so efficient, that it could run every 10 minutes — keeping the Data Warehouse always up-to-date.

This, in turn, has made it possible to analyze and visualize large amounts of live data, without further impact on the production systems.

Data Analysis

Data was loaded into an SQL Server Analysis Services in-memory store, arranging and summarizing it in a way that made the most sense for the employees. With a direct connection from Microsoft Excel, this data could be organized in PivotTable reports, and refreshed automatically.

For even easier (and faster) access, Power BI Dashboards are displayed in various places, giving everyone at-a-glance information on the current status. These displays are always on, and always up-to-date. If there is a problem that needs manual intervention from the employees, color-coded warnings are displayed automatically. This greatly reduced the time until problems are detected and subsequently solved, improving the overall efficiency — and reducing overtime.

Image Description
“Mr. Kerekes took the work from conception to execution into his own hands and created a user-friendly tool for us, which is now indispensable in our daily work.”