When different systems don't "talk" to each other - Master Data Management in the Public Sector (but not only)

Since I joined NCC, I am constantly learning about the council, Data Analysis and also about Data Architecture and solutions. I am looking into different case studies and examples and I was really curious about what other people think - so decided I am gonna share my insights. Let me know your thoughts (yes, I am very much afraid I am wrong somewhere in my article- but I am happy to learn!)

I am wondering how many different systems is used in your organisation? Comment below and let me know if that's the public or private sector :)

If your company hasn't been created less than 5 years ago - more than likely you have some legacy systems, which are still used, alongside more modern solutions. There might be systems your partners are using - and you just can't abandon them. It creates a matrix of systems with a range of databases, which very often - don't "talk to each other".

Why? The answer is pretty simple - each system speaks a little bit different "language" and very often they exist on separate architectures. Both might have similar syntax (like in most languages there are verbs and nouns), but they don't know what they mean. There are potentially fields like a Name and Address in each of the systems, but we can't just simply say that Mrs Smith in one system is definitely the same Mrs Smith in the other.

Why does it matter?

For three main reasons, but there are plenty more, let me know why is it important from your point of view in the comment below? My list:

1. For a better user experience - when I am logging into my customer account - for example in my Council I would like to see my full journey and all contact points. For example, I want to know how much taxes I paid, which services I used (eg. Adult Services, Children Services), what is going on with my recycling bin replacement, what public consultations are happening in my neighbourhood and what projects I am and have been part of.
2. For easier and more clear data analytics. Simply put - when you provide support for 1.000 people and record it in one system and then support for 3.000 recorded in another system you would like to understand if those are the same people and you in fact helped 3.000 people or if you covered 4.000.
3. To better understand the customer journey and track support for individuals - when the customer is signposted to another service you would like to be able to track the journey so we will not "miss" anyone". You can easily imagine that when you manually copy the case details in between Customer Services and let's say - Adult Services it is very easy to introduce a human error. Sometimes it is impossible to avoid errors altogether, but having the ability to cross-check systems would help to avoid the biggest consequences - like not following up on a person in need.

"Let's have one system then!"

That would be very tempting - why not have one system which will cover all operations? There will be no issues with having range of datasets with no communications between them, all simply in one database. It does remind me of something though - a discussion between monolith architecture and microservices (or often - just services:)).

Quite a few years ago there was a big shift from monolith structures to microservices, which - obviously - communicate with each other very well. Most likely using API endpoints. All big-known applications such as Amazon or Netflix migrated to that solution. So... one system is clearly not an answer and having multiple "services" is not bad in essence.

Main benefits of microservices: Separate development and deployment, Scaling and resilience, Independent tech stacks.

Infographics from What Are Microservices at Weave Works

Check more here about examples of microservices.

It's all about communication!

When we look into the "cons" of microservices - Difficulty, Changes and dependencies and Complex testing and troubleshooting - if you have multiple services - you already are dealing with those problems and actually probably managing it quite all right - services are working, users do their daily job, the work gets done.

The main things missing are the communication layer in between your services and - very often - the joined User Interface (UI).

Why not introduce another system into the mix?

The 1st reason why many institutions are approaching Master Data Management is to improve customer service experience. And this is a moment where new system might resolve both problems - master data management and better user experience. That's also what many councils did - here is an example of implementation of Civica in North Lanarkshire Council.

Another similar solution - but where the main UI is used by the officers, not the customer Have Your Say Today – Camden Residents' Index – Commonplace.

Very similarly are developing things internally in Northumberland County Council - we are in the process of replacing the existing Customer Service system with PlaceCube - more modern and versatile solution, which hopefully eventually will link with all other systems.

Below is my variation of the microservices structure but based on a range of services with our own UIs and central Customer Service Management system.

The communication between services on the diagram above is based on two approaches - more modern and definitely best - through calling the built-in API endpoints in existing services/systems. Well, not every system offers that option, especially legacy one. We could use some SQL jobs or ETL solutions between those two databases, though. Luckily, SQL is not a recent invention and many systems will operate based on SQL database. Eventually - you might want to replace this legacy system with a newer one, which supports API connections.

So - depending on the technology and approach - you might also have duplicates of the same information in both services, that's where the constant updates in both directions are extremely important. However, the goal is to use the main service more like a coordinator, not a monolith service with loads of information.

We need a translator!

We have some idea of how our systems might talk to each other, but they still don't understand each other. How the systems will communicate when they don't know which record corresponds to which in another system?

We need to do some first bulk matching using multiple fields like name, address, date of birth etc. With fields which are not possible to match yet, we can wait for the customer to log in and provide further details (eg. - NIN or NHS number or confirm current address) which will allow making further matching.

As each system might create new records almost all the time (eg Adult Services will still use their system with their well-known UI), the matching ability must be something ongoing, not a one-off. So during the implementation of your "central" service, it will be good to have that discussion, IMO.

Why not just join on UPRN?

Many times I heard that phrase! Let's use UPRN - Unique Property Reference Number which exists in many public systems and could be used to match people's records as well. It is a Unique Identifier for every residential building in the UK. I would be, however, very cautious using this as the only key - people are changing addresses, moving out etc. and even if we will use it only for data analytics, not for the customer service central database - it will still not work properly and we will not catch important trends.

Let's look at this case study - we are monitoring the well-being of one town area. We have confirmed that in 5 years the quality of life improved, median income improved etc. Unfortunately, because we were looking at the area only through properties, not individuals, we didn't notice that a significant number of less wealthy people moved out of the area, as it became too expensive for them. On paper - we improved life in that area, in reality - the life of people in need didn't change for the better.

Primary Key and Keys Matrix

Finally, you should have the table with a keys matrix, where you will be able to view data from different services and match them, even if they are not in the new system (they haven't been copied).

The Primary key will more likely be the Unique User Identifier used in the new system - as it in essence should cover all users' communications. The new system is like a window to all the other services in the council.

That's my vision and understanding of this approach to Master Data Management in the council, I am open to critique and further enhancement of this idea - so please comment or speak with me directly!

Please subscribe to my LinkedIn newsletter, where I will share my insights from speaking with many people from the Public Sector and working in Northumberland County Council.