ERP Master Data: Who Owns Item, Vendor and Customer Records Before Cutover

Suppose a company is preparing to go live on a new ERP. The project team rehearses loading data from the old system for the first time. The record counts match and the program raises no errors, but when purchasing opens the vendor report, the same vendor appears under three codes with slightly different spellings. The warehouse finds that the same product is counted in "boxes" under one code and "pieces" under another. Accounting asks which code the amount owed to this vendor should be summed from, and nobody can answer right away, because nobody was ever assigned to own this data.

This article is written from the viewpoint of a team that delivers and maintains systems, for business owners and project sponsors who have to decide where to spend time before the real go-live. We describe what the sources we could open state, say what they prove and do not prove, and then propose a way of handling master data that does not require buying more tools: name the data owners, cleanse at the source, write validation rules together with conversion rules, and rehearse loads until the results settle.

The sources we used and what they say

The first source is an article from the publisher SAP Press titled What are the data migration phases for an SAP S/4HANA project (see the SAP Press article). We opened this page ourselves. A caution: it is a publisher's blog, so we treat it as a secondary source, and the page does not show a publication date. The relevant statements are these.

• Data cleansing is one of the most underestimated activities in a data migration project, and it should start as early as possible in the project.

• Data cleansing should be completed, at the latest, with the last productive load test, the one that uses real data.

• If possible, data cleansing should always be performed in the source system. If that is not possible, it can be done in the tool used to transfer the data, and some systematic source errors can be fixed with conversion rules.

• The best time to start defining validation rules is when you determine the conversion rules.

• Validation before migration checks the source data for syntactic and semantic correctness. Validation after migration checks record counts, completeness, and whether business processes run without errors on the loaded data. A combination of the two is frequently used.

That article is specifically about SAP S/4HANA. We are not saying your product is SAP. But in our opinion the principles it states, which are start cleansing early, cleanse at the source, and write validation rules together with conversion rules, apply to other ERP products too. That is our opinion, not a statement the article makes about other systems.

The second source is report GAO-12-134 from the US Government Accountability Office (GAO), published on 28 February 2012, about ERP programs of the US Army and Air Force (see the GAO report page). The report's summary says independent assessments of four ERP systems identified deficiencies in data accuracy, and that users had to rely on manual workarounds. It also says roughly two-thirds of invoice and receipt data had to be entered by hand in one system because of interface problems with the invoicing and receiving system.

To be clear, we cite this report only to show that data accuracy problems in ERP programs are recorded in public audit reports, and are not something software vendors or consultants exaggerate. The summary we read does not say the cause was master data or data conversion. The invoice problem GAO describes is an interface problem. So we do not claim this report proves that master data is the cause of stalled projects. We also did not find any openly verifiable figure for what percentage of ERP projects stall because of master data, so this article contains no number of that kind.

What master data is, and why damage spreads everywhere

Master data is the reference data that all other transactions point to, such as items, vendors, customers, the chart of accounts, units of measure and warehouses. A purchase order does not store the vendor's name again; it points to a vendor code. An invoice points to a purchase order. The amount-owed report sums from invoices. If the same vendor exists under three codes, the transaction data is scattered under three codes, and every report that totals by vendor has to be combined by hand.

What sets master data apart from transaction data is that one error does not stay in one place. Suppose one item code has the wrong unit of measure. Every purchase order, every stock deduction and every cost calculation that references that code is wrong too. And once the transactions have been recorded for several months, fixing the code at the source means fixing past transactions as well, which is much heavier than fixing before the load.

Another point that is often overlooked is that the old system can often hide the mess. People who have used it for years know which code to read together with which other code. A new system that enforces unique codes, or that requires units of measure to come from a table, will make the knowledge that lives in those people's heads surface as errors at load time or in real use. That does not mean the new system is wrong. It means an agreement that was never written down is being forced to be written down for the first time.

Name an owner for each data type first

The first thing we recommend is not choosing a data-cleansing tool but naming the person responsible for each type of data. The owner should be a business person who can decide whether a record is right or wrong, not the IT team, which cannot decide on the business's behalf whether two item codes are the same product.

We recommend a one-page table with one row per master data type, answering five questions.

• Data type, for example items, vendors, customers, chart of accounts, units of measure, warehouses.

• Data owner, meaning the one person who can decide that a record is right or a duplicate, not a broad department name. If you cannot say who owns it, that is the answer to fix before anything else.

• Main source system, meaning where this data is actually created and edited. It might be the old accounting system, a warehouse spreadsheet or a point-of-sale system, and if there are several places, you need to say which is authoritative when values disagree.

• Duplicate rule, meaning the condition used to decide that two records are the same, for example a vendor's tax ID plus branch, or a manufacturer's part number, and also which code is kept when a duplicate is found and how history is merged.

• Validation rule, meaning the minimum conditions the data must pass before loading, for example fields that must not be empty, the format of the tax ID, units of measure that must come from the agreed table, and accounts that must exist in the new chart of accounts.

The table sounds simple, but in projects we see from the delivery side, the emptiest column tends to be the data owner. That is an opinion from experience, not a figure we have a source for. If a row has no owner, we suggest the project sponsor names one, because this is a question of decision authority, not a technical question.

Cleanse at the source, not at the destination

The SAP Press article recommends cleansing at the source system where possible. Our reasoning is that if you cleanse at the source, the clean data lives in the system people actually use, users see the result, and they begin creating new data in the correct form. Fixing things midway in the conversion script while leaving the source as it is means the source keeps producing bad data every day, and the same fixes have to be repeated in every rehearsal load.

Cleansing at the source can be done in steps, ordered from lower risk to higher.

• Find the obviously wrong records first, such as required fields left empty, tax IDs with the wrong number of digits, and units of measure that are not in the table. The output is a list the data owner can fix straight away.

• Find the likely duplicates using the agreed duplicate rule, then give them to the data owner to decide. We do not recommend letting the system merge records automatically without human confirmation, because a wrong merge is harder to undo than leaving a duplicate in place for now.

• Retire records that are no longer used, such as vendors with no transactions for a long time and products no longer sold. Decide with the owner whether they will not be transferred or will be transferred as inactive, because some records still need to be kept for look-back.

In some cases cleansing at the source is not possible, for example when the old system can no longer be edited or nobody has permission to edit it. The same article says to cleanse in the transfer tool instead, and some systematic errors can be fixed with conversion rules. For example, a unit of measure that the old system wrote as "box", "boxes" and "bx" can be mapped to a single value with a lookup table. But we think conversion rules should be used for repeating pattern errors, and not for deciding whether two records are the same thing, because that decision needs the person who owns the data.

Write validation rules together with conversion rules

Many project teams write the conversion rules first and only think about validation close to the rehearsal. The result is validation rules written to match the data that loads, not what the business needs. SAP Press recommends starting to define validation rules when you determine the conversion rules. We agree, and here is how we understand it.

When the team writes that a field from the old system will be converted into a particular field in the new one, write immediately what a correct value looks like, whether it may be empty, and who confirms the result. Doing so makes the conversion rules and the validation rules one document. The project sponsor reading it can see which data types still have no criteria for right and wrong.

Validation has two moments, as the article describes. The first is before migration: check whether the source data is correct in form and in meaning, for example that the tax ID has a valid format and that a vendor that has been closed has no purchase orders still open. The second is after migration: check record counts, completeness of fields, and what we consider the most important for a business owner, whether business processes actually run on the loaded data. For example, can you create a purchase order for a migrated vendor, receive goods into the warehouse and issue an invoice without errors? A matching record count does not guarantee the data is usable, as the example at the top of this article shows.

We cover post-migration validation and cutover in general in our article on validation and cutover when changing systems. This article focuses on master data and the people who own it.

Rehearse loads until results settle, and what to ask your developer to show before cutover

One rehearsal load is not enough. We suggest treating rehearsals as a cycle: load, check, send the wrong records back to their owners, fix them at the source, then load again, until the remaining wrong records are ones the owner accepts and has recorded. SAP Press says cleansing should be complete, at the latest, with the last productive load test. That means if cleansing is not finished by then, you have two options: postpone the cutover date, or go live with data that is not yet clean, and you have to choose knowingly.

In each rehearsal we suggest asking the developer or service provider to show you the following, as documents or demonstrations, not as verbal assurances.

1. A report of pre-migration and post-migration check results, split by master data type, with the number of records that passed and failed each rule.

2. A list of records that were rejected or converted automatically, with the reason, so the data owner can check that the conversion rules are not hiding errors.

3. One or two real process paths run on the loaded data, such as purchasing through to payment and sales through to receipt of money, with business users doing the clicking themselves.

4. A record of what changed in each round, so you can compare whether this round improved because the data got cleaner or because the rules changed.

5. A plan for records that are still wrong on the cutover date: who fixes them, within how many days, and how the system prevents those records from being used in the meantime.

These requirements should be written into the TOR or the contract from the start, so they do not become a negotiation close to delivery. We wrote about putting conditions that keep a system maintainable into a TOR in our article on conditions in a TOR, and it applies here.

Another related point is the data used for rehearsal loads. Customer and employee master data is personal data. If real data is used in a test environment, decide how to mask it first. We wrote about this in our article on data masking and test data.

Things not to do

In our view there are three things not to do just because the go-live date is getting closer.

The first is to load all the data into the new system and plan to clean it up later in the new system. Once real transactions start referencing those codes, merging or fixing codes also means handling the history, and that work often falls on accounting and the warehouse, who have to do it alongside their normal work. The second is to let the developer decide on the business's behalf whether two records are duplicates, based on how similar the names are. Developers know how to find likely duplicates, but the people who know whether two item codes are really different models are the warehouse or sales. The third is to use a complete record count as proof that the data is ready, because a complete count and usable data are two different questions.

Limits we should state plainly

If your business has only a few hundred customers, vendors and items, and all the data is in one system that a few people already look after, a full data-owner table may be more than you need. A short table saying who decides duplicates for the three main types is enough to start.

Another limit is that our main sources are a publisher's article written about SAP and a GAO report about US government programs, which are different contexts from a mid-sized Thai business. We do not have an openly verifiable figure for how many Thai businesses stall because of master data, and we did not check whether every ERP provider's tools support the steps described here in the same way. If you are making a high-value decision, have the developer or an advisor who knows your chosen product confirm these steps against that product's official documentation.

And finally, having data owners and validation rules does not make the data clean by itself. It only shows where the data is not yet clean and who has to answer for it. Cleansing still takes time from people in the business, which has to be planned from the start.

Objections we often hear

The first objection is that the system vendor says their tool can clean the data. That may be true technically. Tools can find duplicates and convert formats. But deciding whether two records are the same thing is still a decision for someone who knows the business. The question to ask the vendor is: if the tool proposes merging records, who confirms it, and is there a log to look back at?

The second is that old data is no longer used, so transfer all of it just in case. Keeping data available for look-back is reasonable. But bringing every record in as usable master data in the new system is another matter. Unused records will appear in every user's pick lists and raise the chance of picking the wrong one. The balance we see is to transfer records that are no longer used as inactive, or to keep them in a separate historical store, as accounting and legal requirements dictate.

The third is that there is no time and we must go live on schedule first. We understand the pressure, and we are not saying you must always postpone. What we recommend is deciding knowingly: know which data types are not yet clean, who owns them and when they will be fixed, rather than going live and finding out later from reports whose numbers do not match. For what an ERP is and when a business should have one, see our article on the symptoms business owners usually see before they know they need an ERP.

Where to start

1. Make a one-page master data table with the data type, owner, source system, duplicate rule and validation rule, and have the project sponsor name an owner in every row that is still empty.

2. Sample one hundred source records per type, or fewer if there are not that many, and note what kinds of problems appear, such as duplicates, empty fields and multiple units of measure. Use the result to estimate cleansing effort instead of guessing.

3. Start cleansing at the source before the first load, letting data owners fix the obviously wrong records first and then decide on the likely duplicates.

4. Write validation rules alongside conversion rules in the same document, and have the data owners read it and sign to acknowledge it.

5. Set the number of rehearsal loads and the pass criteria in advance, including running real processes on the loaded data, and record each round's results so they can be compared.

6. Set the date by which cleansing must be finished at or before the last productive load test, and agree in advance that if it is not finished, the sponsor will choose between postponing and going live with the remaining records outstanding.

Summary

The sources we could open do not prove that master data is the number one reason ERP projects stall. But they agree that data cleansing tends to be underestimated, should start early, should be done at the source where possible, and should have validation rules written alongside conversion rules. If you take one thing from this article, make a one-page table saying who owns each type of master data, which rule decides duplicates, and what it must pass before loading. That table can be used to question your developer, plan rehearsal loads, and hand the system over to whoever takes it on next.

If you would like to talk through the details of a project, you can reach us at 088-983-9386 or [email protected]. Our office is in Bang Kapi, Bangkok. For organizations still drafting a scope of work, we are glad to arrange a conversation to understand the problem first, without needing complete documents. And if we find that the problem is not yet at the point of needing a new system, we will say so plainly, because starting a project at the wrong time costs more than waiting another quarter.