Taking control of Big Data in the national distribution center
- Logistics / Retail
- Schafisheim, Switzerland
- Organizing data from various sources, reducing data complexity
Microsoft SQL Server
Database Engine of the Data Warehouse (DWH)
Microsoft SQL Server Analysis Services
Tabular Model for Data Analysis
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.
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.
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 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.