Case Study · Retail
Derimod data warehouse: from AX, Excel and CRM to a single analytics database
The layer everything at Derimod sits on. We brought AX ERP's transaction-level data, the teams' Excel files and the CRM together in one analytics database on SQL Server: schema and naming standard, procedure registry and log mechanism, daily flow with SSIS, uninterrupted loading with partition switch, delta detection with RowHash. Both the dashboards and Optail are fed from this warehouse.
- 3
- Source systems
- 7
- Schemas
- Switch
- Loading
- RowHash
- Delta
AX, Excel, CRM — into one model
By department; every project has its place
Prepare in staging, attach in one move; reports never lock
Only changed rows flow downstream
Problem
At Derimod the data lived in AX’s database at transaction level: every sales receipt, every stock movement, every transfer order. To build a decision system on top, that data first had to move to a simpler architecture. The teams’ budget, plan, capacity and rule knowledge was in Excel; customer data in the CRM. The three sources did not mean the same thing by “sales”, and the same product carried three codes in three systems.
In August 2023 we started with this layer; Optail and the dashboards waited.
Rules first
The first document was not a table design but a conventions document. Two databases — Analitik and DevAnalitik — one live, one Lumtify’s development and test area. Projects and data were separated by department schemas: allocation, planning, stores, e-commerce, logistics centre, supply chain, staging. A transfer project’s table sits in the allocation schema; whose data it is can be read from its name.
| Rule | Decision |
|---|---|
| Table names | No prefix, no underscore; each word capitalised: ReplenishmentParametre |
| Columns | No Turkish characters, no spaces; varchar unless Unicode is needed, then nvarchar; the needed length instead of MAX |
| Procedures / views | sp and vw prefixes; every procedure registered in the spList table — what it does, when it was written |
| Log | Every procedure writes its steps to spLog: which step, when — when a nightly flow slows down, the slow step is visible |
These rules still hold three years on; the procedures written by Derimod’s own team are in the same registry.
The flow
SSIS packages pull data from the sources on schedule, load it into staging, validate it and move it to the main tables. Validation was done together with Derimod’s IT, planning and business intelligence teams: a table was not “done” until the sales figure the warehouse produced matched the figure the teams trusted.
Two design decisions became decisive over time.
Partition switch. Sales and stock are partitioned by day. The new day is prepared in staging and attached to the main table with ALTER TABLE … SWITCH — a metadata operation, milliseconds. Reports do not lock during loading; when the dashboards open in the morning the data is ready.
Delta with RowHash. Because the switch overwrites, Change Tracking and CDC are out. Instead every row carries a persisted RowHash column computed from the business columns; the hash is computed during the bulk load and costs nothing extra. Staging and the live table are compared only on the (key, RowHash) pair — a narrow index, never touching wide rows. Changed and new rows land in the delta table; Optail’s and the dashboards’ downstream processes work only on that delta. Date and decimal columns are converted to a fixed format before hashing; otherwise the same value can produce a different hash and a false change.
Excel inside. Rather than ignoring the plan and rule knowledge the teams had kept in Excel for years, we connected it to the warehouse in a defined format. The master plan, product exclusion lists, store groups — all come from Excel, land in a table, and become Optail’s input.
We also validated the critical architectural decisions in consultation with Microsoft. In structures like this, the difference between “works” and “still works three years later” is decided by these choices.
What was built on it
The warehouse feeds two things: more than 20 Power BI dashboards and Optail’s scenario tables. Optail’s own database — base data, scenario, parameter and result tables — reads from the warehouse; every scenario stores its input and output under a unique id, and the parameters a past scenario ran with can be seen years later. In 2025 the CRM and campaign datamart was added to the same warehouse.
Why this way
- The standard comes before the table. Had the naming and schema rules not been set in the first week, by the third year nobody would know whose table was what.
- Delta must be cheap. Burying the hash in the load cost is both faster and safer than comparing the whole table every night.
- When the source changes, the warehouse absorbs it. When a field changes in AX, the dashboards and Optail do not know; the warehouse layer takes the change.