Master data controls all business processes. If master data is not maintained correctly, errors are “passed on” to business transactions and something is pretty much guaranteed to go wrong as a result. Similar problems arise if master data in SAP is not unique because duplicate entries exist. This blog post explains what the specific problems relating to master data duplicates are and how they arise in a business environment. As is befitting for any decent SAP Audit Blog post, it will of course also reveal how to locate master data duplicates for vendors and debtors in SAP, and then clean up them properly.
How do master data duplicates arise?
First of all, you might think that master data duplicates are not a problem at all. Why would you create master data for the same vendor twice anyway? Of course, it is not usually something that you do consciously or intentionally, but rather something that happens because you have lost a general overview of things.
Reasons for master data duplicates are common in practice:
- Poor Master Data Governance: Responsibilities for the creation of master data are unclear and therefore different people are busy with the creation of master data and duplication of work occurs.
- Missing or inadequate Segregation of Duties: There is no proper authorization concept in the SAP system and proper attention is not paid to segregation of duties when granting access authorizations. For example, a person should not be able to create/change vendor master data and post incoming invoices. Since the authorizations which are assigned are often too far-reaching, this is something that people take advantage of and vendor master data is created by different users.
- Consolidation of multiple data sources: Different data sources supply master data to an SAP system (also via automatic processes). Master data duplicates are created because, for example, a vendor only occurs once in a source system, but may exist in several source systems at the same time.
- Data migration: Data is transferred to a new SAP system during a data or system migration. Because things are often hectic when working on such projects and time is at a premium, an indulgent attitude is taken to data cleanup before migration and it is often overlooked. In the new SAP target system, the quality of data is then poor right from the start.
What problems arise due to master data duplicates?
Master data duplicates do not necessarily always have to be bad if the participants are aware of their existence – but, in practice, this can of course in itself be a problem. The following phenomena and associated problems often occur with master data duplicates:
- If there are several vendor accounts for the same vendor and the payables are distributed across these accounts, the amount of liabilities remains unclear.
- It may start to become difficult to find liabilities: Liabilities are not easy to find because they are arbitrarily located on different accounts payable.
- Chaotic management of open items: Because finding liabilities becomes difficult, clearing open items becomes more complicated and takes longer.
- Hectic payment processes: Since a proper overview is lost due to there being several different accounts payable, when the vendor sends a payment reminder, payments may be made in a hectic way. Because of this, the payment may be posted as a downpayment and the liability might remain open. This makes management of open items even more chaotic.
- Overpayments: Because liabilities are confusingly located on different accounts payable and are then settled by means of downpayments, vendors may be being overpaid without it being noticed immediately.
- Financial losses: Recovering payments to overpaid vendors can be difficult. Some vendors may already be insolvent or have ceased operations. You are simply left with an open claim. The money is long gone.
How to identify duplicate master data?
In order to avoid such problems associated with master data duplicates, you have to clean up the master data. However, it is only possible to do this once these have been reliably identified. There are different approaches to doing this, depending on whether it is vendor master data or debtor master data:
- Multiple use of VAT IDs: For debtor master data, the use of the same VAT identification numbers for different debtors is an indication that master data duplicates exist. In the SAP data, you can therefore evaluate whether there are VAT IDs stored for at least two debtors.
- Multiple use of tax numbers: For example, for vendor master data, the use of the same tax number (not the VAT ID in this case) is an indication that master data duplicates exist for different vendors. You can therefore evaluate whether there are tax numbers stored for at least two vendors in SAP.
- Multiple use of bank details: In the case of vendor master data, for example, using the same bank details more than once for different vendors is an indication that master data duplicates exist. You can therefore evaluate whether there are tax numbers stored for at least two vendors in SAP.
- Comparison of vendor name and address in the vendor master: In the SAP vendor master data, you can query whether suppliers have the same name and address. However, an exact comparison of e.g. the supplier’s name, the street and the place is often not advisable, as different spellings can easily prevent detection due to the lack of an exact match. These characteristics should therefore be compared to determine whether they are “soft” variations. Phonetic algorithms can be used for this purpose. Phonetic algorithms check whether two words are identical in pronunciation. The pronunciation can then be the same, although the source words are slightly different. One frequently-used algorithm is SOUNDEX. With SOUNDEX, the words “HAMBURG”,”HAMABURG” and “HAMBURCH” all result in the SOUNDEX code “H516”. In contrast, “HARBURG” produces the SOUNDEX code “H616”. “HAMBURG” and “HARBURG” would not be the same according to SOUNDEX,” while “HAMBURG” and “HAMABURG” would.
If you want to discover how these approaches can be implemented very quickly and easily by using some SQL queries, simply download the corresponding guide for SQL. The guide contains an SQL glossary of database queries that will make your task easier:
How to handle detected master data duplicates?
Once you’ve identified the master data duplicates, you are now in a position to do something about it. Reasonable measures you can take are:
- Transfer posting: Transfer all receivables and payables from master data duplicates to the correct subledger account and clear all open items on the redundant subledger accounts.
- Blocking: In SAP, block the redundant subledger accounts for further postings.
- Archiving: Archive the redundant subledger accounts so that you can delete them at some point after the cleanup work is done.