Case Study · Retail
Derimod: from data warehouse to Optail — three layers, seven projects in three years
We brought data scattered across AX ERP, Excel and CRM into a single data warehouse, built more than 20 Power BI dashboards on top, and placed Optail — initial allocation, replenishment and inter-store transfer decisions — at the top. Then came stock analytics — lost sales, size-run breaks, idle stock — and a CRM layer. This page is the umbrella; each of the seven projects has its own page.
- 20+
- Dashboards
- 7,000+
- Optail scenarios
- 7
- Projects
- 3 years
- Running for
Sales, stores, warehouse-allocation, CRM
In two years of live use
Warehouse, BI, 3 Optail modules, stock analytics, CRM
Growing with every new need since 2023
Problem
It would be wrong to say Derimod had no data; on the contrary, there was plenty. The problem was that it lived in three separate worlds.
| Source | What it carried | Who used it |
|---|---|---|
| AX ERP | Daily operations: sales, stock, shipments, product master | IT, operations, accounting |
| Excel files | Processes each team had shaped to its own needs: budget, plan, capacity, rules | Planning, allocation, store operations |
| CRM | Customer profile, campaigns, loyalty | CRM and marketing |
Each source did its job in its own domain. But what one called “sales” was not what another called “sales”; the same product sat under three different codes in three systems. Before the weekly meeting, teams spent hours reconciling numbers in their own spreadsheets, and part of the meeting went to arguing which number was right.
On top of that sat retail’s oldest question: which product should go to which store, how much, and when? A product waits on the shelf in one store while a customer in another store looks for it and cannot find it; size 38 of the same model is sold out in one store and three pairs sit in another. Making these decisions by hand for hundreds of stores and thousands of products is not feasible; even when it is done, it is redone every week and depends on individual experience.
We approached this in a deliberate order: data first, then visibility, then the decision.
Three layers, in order
1 — Data warehouse (August 2023). We started with data, not algorithms. AX, Excel and CRM data was brought together in a single analytics database on SQL Server; SSIS packages pull from the sources on schedule, validate and fit everything into one model. Large tables are partitioned and loaded by partition switch; every row carries a RowHash, so only changed records flow downstream. Details →
2 — Business intelligence (October 2023 – ). Clean data from the warehouse became more than 20 Power BI dashboards: sales, product, customer, budget, store and warehouse capacity, logistics, e-commerce operations. All of them are fed from the same warehouse; no report has a data source of its own. Details →
3 — Optail (2024 – ). A dashboard shows, it does not decide. Optail — Optimization + Retail — makes the three distribution decisions in a product’s life cycle every week with the same rules: inter-store transfer (February 2024), replenishment (October 2024), initial allocation (2025). The user sets up a scenario, runs it, reviews the result; an approved proposal goes to the ERP as a shipment order.
Two more things came on top of the third layer: stock analytics that measures whether the decisions work — lost sales, size-run breaks, idle stock, a success metric — and a CRM layer that connects the customer side to the same warehouse.
Projects
| Project | What it does | Period | Status |
|---|---|---|---|
| Data warehouse | Brings AX, Excel and CRM into one analytics database; delta/RowHash ETL | Aug 2023 – | Live, daily |
| Business intelligence | 20+ Power BI dashboards: sales, product, customer, capacity, warehouse, logistics | Oct 2023 – | Live |
| Optail · Inter-store transfer | Size-run consolidation, out-of-collection and season-end transfers | Feb 2024 – | Live, weekly |
| Optail · Replenishment | Automatic warehouse-to-store feed for what sells | Oct 2024 – | Live, daily |
| Optail · Initial allocation | First distribution of new product with master plan, store grouping and capacity | 2025 – | Live; Flow Through in 2026 |
| Stock analytics | Lost sales, size-run breaks, idle stock, success metric; warehouse–store stock visibility | 2024 – | Live, weekly |
| CRM / campaign analytics | Customer 360, campaign success datamart and dashboard | 2025 – | Ongoing |
Timeline
- August 2023 — Start; analytics database and the first SSIS flows.
- October 2023 — First dashboards: sales, sales–stock, warehouse capacity.
- February 2024 — Optail inter-store transfer live; size-run consolidation transfer follows.
- May 2024 — Dashboard presentation to the board; store capacity, budget, idle stock reports.
- October 2024 — Automatic replenishment live; two thirds of replenishment from Optail in the first two months.
- 2025 — Initial allocation and master plan; size-run, lost-sales and idle-stock analytics; CRM and campaign datamart; replenishment automation above 90%.
- 2026 — Flow Through, open-quantity initial allocation, season-end class-based transfer, lost-sales tree.
Outcome
Optail has been part of daily operations for two years; more than 7,000 scenarios have run. The Derimod team now makes its initial allocation, replenishment and transfer decisions through Optail; needs such as emptying a closing store, season-end consolidation and product-list exceptions grew out of daily questions and were added to the system.
What three years taught us fits in three sentences. Order: had we tried to build Optail without the warehouse and BI, the algorithm would have produced inconsistent proposals from inconsistent data and lost trust in the first month. Ownership: because the system was built with the Derimod team, in their language and with their rules, the planner sees it not as an outside tool but as the digital form of their own knowledge. Measurement: had we not measured how much of each decision turned into sales, there would have been no case for raising the automation rate.