Healthcare Data Model: How to get reporting and analytics out of a complex IT system?

Data Modeling

Product

Team Ellie

Ellie Editorial Team

Data has always been an essential factor in the decision-making process. Particularly in the healthcare industry, where the healthcare data model helps in determining the best way to allocate resources in light of multiple converging trends, which present enormous challenges. In this blog, we walk you through a scenario of a major source system (in this case, an extremely complex one in terms of database structure) being at the center of a BI/DWH project.

As all data warehouse specialists know, many of the problems we face arise from the fact that the databases of legacy source systems are usually beyond human understanding. One of our healthcare industry customers started to deploy a large and important Electronic Health Record (EHR) that had to be hastily configured due to unexpectedly demanding requirements from the customer side. The events that led to these difficult circumstances would be worth a story of their own, but here we focus on the insights on how to overcome some of the challenges involved.

In fact, this kind of scenario is extremely common regardless of the industry sector. Many ERPs, such as SAP, have similarly complex internal database structures meaning that these challenges are inevitable. And even though all ERPs offer their own native reporting features, they usually fall short of meeting all the reporting and analytics requirements of the business. But back to our customer’s case! Many ERPs, such as SAP, have similarly complex internal database structures meaning that these challenges are inevitable. And even though all ERPs offer their own native reporting features, they usually fall short of meeting all the reporting and analytics requirements of the business. But back to our customer’s case!

Data Mesh organization in action

A successful healthcare data model is achieved by asking the right question and keeping the requirements clear. Our customer wanted to integrate the EHR data with other data – so all in all a very typical data warehousing use case in the healthcare space.

The challenge was that there were no in-house personnel certified to this particular IT system, so they had to bring in an international consultancy company to handle the ETL process – again, a very typical process that many corporations have to undertake. But this is where they were able to bring in a secret weapon. The customer had set up data management teams for each domain, looking after their domain data.

Does that ring a bell? Yes, if you follow what the data speaking world is currently talking about. This kind of team setup is an important part of Data Mesh – but here it was implemented before the whole concept was born. However, there were some problems at first.

Navigating the jungle

How on Earth do these external consultants know which data they should process through ETL pipelines into the data warehouse? The EHR had 7000 tables, so it’s not an easy task to navigate through such a system.

The truth is that the database of the legacy system is often like a jungle: large, chaotic, full of layers, and, of course, data. To find anything in it that would make sense for humans is a very difficult job for anyone. It’s like sending an expedition into the Amazon jungle to find a specific fruit, but you have no idea what it looks like. This is where Ellie came into the picture.

Transforming business requirements specifications

The customer’s teams started to produce conceptual data models as blueprints for the external consultants, to help them with their data discovery tasks. The blueprints were basically defining “Data Products” that the customer wanted to be able to utilize.

So in this case, the consultants, in-house IT staff, business controllers, and also domain experts such as doctors and nurses, teamed up and gave input for the composition of the models – each from their own perspectives.

The business folks gave their “top-down” perspective of the business domain and the consultants, based on the modeled business needs, their “bottom-up” view from the perspective of the database.

For instance, one area of focus was surgery operations. Together, they created an Ellie data model that reflected the information about the surgery process exactly, and it was defined in terms that healthcare professionals themselves use on a daily basis.

Business Glossary is vital for a successful DW implementation

The data management team then realized that defining their essential data concepts is a must-do thing from the beginning. In the source systems, the tables containing the surgery-related data are named differently than in real life. In Ellie, all identified data entities were cross-checked against these tables to make sure that the data can be linked with business terminology. This kind of business-centric approach is an essential component of Domain-Driven Design, which is a core part of the Data Mesh language as well.

There’s a business glossary feature in Ellie serving exactly that need – all relevant business and data terms (descriptions, synonyms, attributes, data types, etc.) can be defined and maintained in Ellie. As an outcome of this process, they had a data model that was able to incorporate both the source system structure and the business understanding. Ellie acts as a connector of the “top-down” and “bottom-up” views, creating a map to help you navigate the jungle!

Using the same terminology throughout the BI-process

For the benefit of the entire BI process, Ellie data models were also used as blueprints when building BI dashboards for data consumers. The idea at the core here is that the domain language is captured in the beginning of the requirement gathering phase in Ellie, and then passed through the whole data process. The data users then know what data they actually are using – a real game-changer, if you ask our customer! Within this customer organization, there are some 50+ people modeling with Ellie, using it to support their reporting and analytics work.

This is how Data Products are being made in real life, and it’s an example of how some of the Data Mesh type of thinking can be also utilized in a perhaps more traditional situation. On top of the people working on creating the models, there are more than 200 business users, such as business controllers, also utilizing Ellie in multiple use cases just to understand their data assets. When having multiple different use cases, or Data Products of different kinds, wouldn’t it be great to have some sort of reusable components for all of them? The answer is yes, of course.

Ellie helps customers to productize their data

What Ellie actually does “under the hood” is that it uses all the models, entities, and relationships, and links them all together as an “enterprise model”. So each time the process described above is applied and new models created with new entities in them, the whole information architecture becomes richer and wider – simply because of the reusable glossary defining the entities.

Instead of trying to define the information architecture as a waterfall type of exercise, with Ellie, you can do it in an agile manner, use-case by use-case. And the coolest part of all of this is the fact that when you create any given Data Product, you can reuse the building blocks in all data production. Ellie ensures they’re documented and accessible by everyone.