Every month, the same complaint shows up in workshops running an ERP: someone opens the sales report, and for the next twenty minutes order entry on the shop floor feels sluggish. It's tempting to blame the server, the network, or "the software." Usually the real cause is simpler than that — you are asking one database to do two fundamentally different jobs at the same time, and the two jobs get in each other's way.
Two very different jobs, one database
The operational core of your ERP — the part that creates a sales order, books a goods receipt, or updates stock on hand — is built as an OLTP system (Online Transaction Processing). It is optimized for a high volume of short, frequent read/write transactions. Each one touches a small number of rows, commits fast, and gets out of the way so the next one can run. This is what makes an ERP usable during a normal working day.
A monthly report — for example:
- total sales by region by quarter
- stock movement over the last year
- margin by product line
— is a completely different kind of query. It is read-heavy, it scans large amounts of historical data, and it can take minutes instead of milliseconds. If you run that query directly against the same tables the shop floor uses to create orders, the two workloads compete for the same locks and the same resources. That is the whole story behind "why does the system slow down when the accountant runs the report" — it is not a bug, it is two incompatible workloads sharing one system.
Why a separate data warehouse fixes this
This is precisely why the data warehouse (DW) exists as a concept separate from the operational database. A data warehouse is an integrated store built for OLAP (Online Analytical Processing) — large, complex analytical queries — kept apart from the OLTP system that handles live transactions. Data flows from the operational system into the warehouse on a schedule, so the heavy analytical work never touches the tables your order desk depends on minute to minute. The order system stays fast because it no longer has to serve two masters at once.
How the warehouse organizes data: cubes and star schemas
Once the data is separated, it is also organized differently. Analytical data is commonly modeled as a hypercube (Hyperwürfel) — an n-dimensional data cube where you can slice sales, for example, all at once by:
- time
- customer
- product
- region
Physically, this is implemented as a star schema (Sternschema, the equivalent of the Kimball dimensional model): one central fact table holding the measures you actually want to add up — sales amount, quantity — surrounded by dimension tables such as time, customer, and product that give those numbers context.
When a warehouse gets large, it is common to carve out a data mart — a smaller, subject-specific slice of it, for example just the sales data for one department — so that people who only need one view do not have to navigate the whole structure.
The practical implication for a workshop is not that you need a full data warehouse project next quarter. It is that if your ERP gets noticeably slower whenever someone pulls month-end numbers, the fix is not a bigger server — it is recognizing that transactional work and analytical work should not share the same tables. Even a modest, periodically refreshed reporting copy of your order and stock data, organized around what you actually want to measure, will do more for both speed and reporting accuracy than throwing hardware at the live system.
Nguyễn Hải Minh
I build custom software and data solutions for manufacturing ERP systems, including INFOR, for clients in Germany. As a Staatlich geprüfter IT-Techniker (Fachrichtung Informatik) and Informationselektroniker, I combine deep technical skill with business-systems thinking to help manufacturers automate operations and optimize cross-border import and export.
More about the author →