I’m working on a healthcare project that is specifically exx\posing data quality issues with Providers.The problem is, I’m working with 5 systems and they don’t have consistent business keys. Example:
For EPIC, most, but all providers have an NPI. All providers have an EPICID. Sounds pretty easy to use the EpicID, but none of the other systems include that ID. However, other systems have the same problem - most providers have an NPI, but all have a System ID. It would be great to use the NPI as the business key accross the board, but it is a valid case to have providers that don’t have an NPI. (NPI is only assigned to providers that bill Medicare).
I could use the SystemID for each system as the Hub key, then create a SAL where the NPIs match. That would leave dangling Providers that at this point that don’t have a match between systems. I could then try to do fuzzy logic to try and match those providers on name/address/office or other attributes. Is there a better way to do this? I also considered one satellite for a system like EPIC that includes providers with NPIs and one that includes providers without NPIs. I’m not sure what that buys me, but it might make for easier organization.
The same issue exists across all of the 5 systems I will be ingesting. Any thoughts would be greatly appreciated!
Sounds to me like you might be tackling the issue of multi tenancy. If I remember right you can solve this by adding in a separate attribute for the source system ID and then create a composite key with each systems preferred business key. @patrickcuba covers this in good depth in this article.
These sound like poor candidates for Business Keys. Ask the business: “If the same provider is recorded in more than one system, how do you identify that they are the same provider?” That’ll get you closer to a natural key and that is how I seek out the business key. It needs to be system agnostic, particularly in your situation, one business and >1 surrogate key.
On my own experience (if I quess the setting correctly), in these kind of projects there is not necessarily a single business you can ask the question about busness key.
You might get 5 answers all stating that “our System ID is the key and all providers don’t have the NPI so that can not be used as the only key”.
It is worth asking though. Just be prepared to the fact that the answer is something like above.
As a default solution, I would go for SystemID, with appropriate BKCC and same-as link utilizing the NPI.
That way, you can at least use NPI with those providers that have it. Use bkcc-SystemID for the rest and make sure, everyone from the information mart level onwards knows how to separate providers with NPI (where it is known, who is really who) from those without (two providers might be the same one in reality).