The accounting team closes the month-end report. The sales manager pulls his own numbers for the same month. When the two are placed side by side, they do not match. One side counts a customer as a single entry; the other has the same firm listed three times under different names. One side groups a product into a single line; the other splits it across several rows. This scene plays out in many small and mid-sized businesses. The problem is not careless staff. The problem is that different software programs record the same thing under different names and different dates.
A data warehouse — a system that collects information from multiple programs into one place and prepares it for reporting — is built precisely to solve this. But setting one up is not simply a matter of connecting programs to each other. The real work is finding where the programs speak different languages and making them all say the same thing. This is called data alignment, but the plain version is: sit everyone down and agree on what things mean.
The most common clash is over customer definitions. The accounting program records a customer by tax number. The sales program may have entered the same company by its trade name, or even opened separate records for each branch. One customer in accounting becomes three records in sales. When you try to combine the reports, the totals either balloon or come up short. The fix is to assign a shared code that both programs recognise for that customer. That code acts as a bridge between the two systems.
The second major clash is over product definitions. Accounting tracks a product by its stock code. The sales team may log the same item under a campaign name or by package size. The warehouse or production side classifies it by production line. Before a data warehouse can be built, these three separate lists must be merged into one shared table. This table is called a dimension table, or more simply, a data dictionary. Once the dictionary is built correctly, reports no longer need to be corrected by hand every month.
The third clash is over periods, and it is often the most stubborn. Accounting records an invoice on the accrual date. Sales reports by the date the order was taken. Operations records the date the goods left the warehouse. The same transaction can fall into three different months depending on who you ask. Which month is the real sales month? The answer varies by business, but an answer must be chosen once and every program must be aligned to that decision. Without this, the same argument restarts at the end of every month.
Building a shared data dictionary is as much a people task as a technical one. The accounting manager, the sales manager and the warehouse supervisor need to sit at the same table. Each person explains how their program defines things, the differences are written down and a common decision is reached. These meetings take time. Disagreements will come up. But if this step is skipped, the data warehouse will start producing inconsistent reports within a few months. No matter how well the technical setup is done, mismatched definitions will always produce mismatched results.
For a small business owner thinking about starting a data warehouse project, the most important first step is not to call the technical team — it is to ask your own department heads three questions: how do you define a customer, how do you classify a product, and which date do you use as the basis for reporting? Until those three questions are answered on paper, no software installation will deliver lasting results. The technical infrastructure comes later. The dictionary comes first.
This article was originally written in Turkish by Gökhan MERCANOĞLU on March 11, 2002 and has been automatically translated into English and other languages using machine translation.