We have previously talked about the importance of managing change in MDM projects. Now we turn our attention to an area that affects everything: data quality. Together with Peter Karlsson, we explore why data quality is so often the biggest challenge in an ERP migration, and how Master Data Management (MDM), with the right methods and tools, can turn it into one of the project’s biggest success factors.
Start with an honest picture of where you are
Data quality is one of the most important issues in ERP replacements and modernization projects. Peter, as a supplier you came in with a fairly blank slate. What was the first step you took?
– The first thing we wanted to do was form our own picture of the challenges ahead of us. It was actually very easy to get answers from the business, but harder to get answers that were correct and detailed enough. At first glance, the answers rarely reflect reality. People often say they are good at cleaning up, that they keep their data in order and that it is accurate. Unfortunately, that is all too often not the whole truth.
Why is that?
– You have to remember that these people come from a business that actually works. Orders come in, they are dispatched and customers receive their goods, so from that perspective everything seems fine. What you don’t see is that along the way, many people are fixing and correcting things manually. That costs both time and money, and it also creates dependence on individuals, and with it a vulnerability.
So the goal is to get past the initial belief that everything is in order. How do you do that?
– We start by requesting a subset of data from a number of entities that are central to that particular business, such as customer, product and supplier.
Data profiling gives you a first indication
Does that give you a clear picture of those entities?
– No, unfortunately it’s never that simple. In this case, the subsets were extracted from ERP systems that had been in operation for almost 40 years. Not surprisingly, a customer record created in the late 1900s looked very different from one created this year.
– When we do the first analysis of this data, we use data profiling tools, but without studying exactly what each field contains. Instead, the goal at this stage is to produce distribution curves for things like completeness. For example, what share of customers and suppliers have the company registration number field filled in.
What do you use the results of that analysis for?
– The value is that you can start cleaning up early in the project, and completeness is typical low-hanging fruit. It’s easy to analyze and easy to hand over for enrichment while you dig deeper into the rest of the data. The first analysis also gives both us and the client organization an indication of the overall state of data quality. The result is a basis for discussing and deciding which steps to take next, and in what order.
Fix it at the source, not in the tool
Do you make those corrections in the data profiling tool?
– No, you correct the data in the source system. Data profiling tools are primarily for identifying problems, not fixing them. If you fix things directly at the source, all later work in the modernization becomes easier. It’s the old principle of garbage in, garbage out. If incorrect or missing data stays in the source system, it risks contaminating everything throughout the modernization, and handling it means introducing and strictly following temporary and costly routines. It’s better to fix it at the source so you know you’re getting quality out.
Are there other benefits to this first analysis?
– Absolutely. It forces the business to take a closer, more critical look at its data. There are often clear reasons why important data such as company registration numbers has been missing for a long time without anyone noticing. For example, customers who are no longer customers and really should be deactivated or removed altogether. That is also a very important part of maintaining high data quality.
When the address field is used for everything
How well did the quality level match your expectations?
– I have to admit I was surprised at how low the quality actually was. But it also depends on how you define quality. After all, the business was working even before the modernization. When people read delivery addresses, for example, they have usually been able to deliver, even when the field contained other data besides the address, or even wrong and misleading data.
What do you mean, do you have an example?
– Yes, it could be instructions about which door to deliver packages to, phone numbers to call to get someone to open, special delivery instructions for Mondays and others for Thursdays.
– None of that belongs in an address field, but with nowhere else to store the information, it ended up there anyway. So is it good or bad? The amount of data is very good and informative, which is good, but its structure makes it hard to work with, which is bad. All deviations like these need to be identified, and better ways to store and manage them need to be created. So on top of everything else, we identified yet another improvement opportunity already in the initial data quality analysis.
When fields are used for several purposes like that, because the right fields are missing, don’t you have to correct the data in the target system rather than at the source?
– Yes, that’s true, and it’s not entirely black and white either. The principle is always to correct at the source, but where that isn’t possible, or where it’s more efficient or gives better results to take in the incorrect data and fix it with the new tools, then of course you do that. Preferably with as much automation as possible.
Quality rules that do the work automatically
I suspected as much. Can you tell me more? Any good examples?
– Sure, we have a concrete example with the attribute “Country”. In this case, we found that most variations were recurring and consistent. Depending on who registered the record and what their native language was, Greece could be entered as “Grekland” by a Swedish speaker, as “Grækenland” by a Danish speaker, or in some cases just as the country code “GR”. For this, we created new quality rules that automatically identified the country during onboarding and set a common value for country on the supplier record.
But doesn’t that mean the problems will continue? The same people who registered with different languages and codes in the old systems will surely keep doing so in the new one.
– True, but we make sure it takes care of itself. In the new MDM system, every country is a predefined code. Instead of typing in the country name manually, users have to select the country from a predefined list. This also means the country name can be shown in different languages. A Danish user sees “Grækenland”, a Swede looking at the same supplier sees “Grekland” and a British user sees “Greece”, but all three are simply versions of exactly the same record. You can also link synonyms and abbreviations to each country, so that every integrated system gets exactly the country identifier it needs, such as the country code “GR” or the ISO code “GRC”.
Do you do this with the tools in the MDM suite?
– Yes, the data quality rules are created with the built-in quality engine in IDMC.
You said country was often entered with recurring variations, which made it easy to identify the right country code automatically. But how do you handle the exceptions, like misspellings? Are they set aside for manual processing, or have you built automated support for that too?
– Yes, we actually have. The system has built-in support for validating spelling variations, where it makes an educated guess and returns suggestions. This still creates some manual work, but most of it is prepared in advance. The manual effort is reduced to almost nothing but decisions, based on clear, ready-made input. We have used this technique in many more cases than countries. Email addresses, for example, have to follow a clear format with an “@” followed by a domain and a dot. That is validated with data quality rules. We also use mail servers, which in many cases can tell you whether an email address actually exists and is active. For street addresses and place names, you can validate correctness by including the postcode and using Data as a Service to automatically ensure the correct spelling by matching against verified addresses.
– For address fields, we have also built automatic splitting into the cleansing process. The data model has one field for street name, another for street number, a third for floor, and so on. In legacy systems, all of this is often entered on a single line. That splitting no longer has to be done manually, because the quality rules are defined so that the output is a complete set of correct data, separated and stored per field.
AI as an extra colleague
Have you used artificial intelligence in your data quality work?
– Yes, in Informatica’s world the tool is called CLAIRE. CLAIRE is the AI engine and is connected to most of the modules in Informatica’s MDM suite. A valuable feature in data profiling is that everything you put in there feeds into CLAIRE. CLAIRE then uses that data to suggest sets of quality rules for each field, drawing conclusions about what kind of data a particular field actually contains. This also makes the data modeling work, which covers hundreds of fields, much easier. Since we as modelers get CLAIRE’s input on what a field most likely is right from the start, along with a ready-made set of rules to apply, the modernization work becomes much easier and more efficient.
How much impact does AI have on this work?
– It depends on how much you choose to use it, but simply put, it’s like having an extra colleague, or rather a whole team of new colleagues. Partly during the modernization project, but above all in the day-to-day management of master data, where many time-consuming tasks disappear because CLAIRE does the work for you. So it’s about saving time and resources and becoming more efficient.
Data quality is ongoing work
The project has been running for quite a few months and you’re not live yet. The first data quality work was based on the data that existed then, and much of it may have changed since. How do you handle that?
– We have solved that by continuously adjusting our data quality rules. We have regularly requested updated data and used it to validate, refine and implement rules based on the current reality.
Doesn’t that mean doing the same work over and over again?
– Well, in a way, but the repetitive part is mostly automated. When we receive new, updated data, it runs through the data profiling we have already set up and through the existing quality rules. The output shows us what further actions and changes are needed. So it’s not really about redoing the same work, but about building on what we’ve already done and getting help to find further improvements.
It sounds like you’re really getting value out of the quality work even before it’s finished?
– Yes, exactly. Every piece of the puzzle you put in place makes all the later pieces more valuable.
A recipe for success, then?
– Exactly!
Facing an ERP migration? Talk MDM with us.
