reused business keys

I did a search and didn't see anything that addresses this. Just curious if anyone has experience with a dimensional model where the business key gets reused over time. Here's the scenario...

In health insurance, the common format to identify an insured person is a group id + subscriber/insured id + a suffix. The suffix for the primary insured is usually 01, the spouse 02 and dependents start at 03 on up.

e.g. "ABC" is the Group Number and "1234567" is the subscriber ID for the person that holds the policy. The spouse and kids are under the same group/subscriber ID with different suffix's.

Primary - ABC 1234567 01
Spouse - ABC 1234567 02
Child 1 - ABC 1234567 03
Child 2 - ABC 1234567 04

The problem is with the spouse. If the primary is married, then divorced, then remarried, in the transactional system the new spouse will be reassigned "ABC 1234567 02", the same as the original spouse. That ID is how the claim is processed and tracked in the claims processing system. From an OLTP standpoint, it makes sense, but it's messy for historical DW work.

You can create a type 2 "person" dimension along with a separate type 2 "ID" dimension, but it still makes reporting difficult. HIPAA privacy requirements complicate this because spouse #2 can't see spouse #1's claims history (or visa versa), even though they share the same subscriber ID/suffix (just at different times).

Just curious how others have modeled cases where you had business key reuse in similar circumstances.

One of the effects of implementing a data warehouse is that is exposes system discrepancies like you describe.

While a technical response may be to try to use some other attribute, in combination with the member ID, such as birth date to uniquely identify the person, the correct solution is to change the way members are identified in the operational system. Reassigning IDs, particularly when you are dealing with medical records, exposes your company to very significant liability risk. There is no valid reason why the operational system should function in this manner.

ngalemmo wrote:One of the effects of implementing a data warehouse is that is exposes system discrepancies like you describe.

While a technical response may be to try to use some other attribute, in combination with the member ID, such as birth date to uniquely identify the person, the correct solution is to change the way members are identified in the operational system. Reassigning IDs, particularly when you are dealing with medical records, exposes your company to very significant liability risk. There is no valid reason why the operational system should function in this manner.

I agree, but it's actually pretty common with health insurance (check out you and your SO's health insurance ID). There's only been a couple replacement claims systems sold in the US in last few years. Full lifecycle costs are in the $100 million range. Most health insurance claims systems are 20+ year old mainframes with a distributed system bubble gummed and bailing wired on top to accommodate HIPAA, NPI, etc. Because of the age of the systems, the transactional side was never designed to accommodate that problem.

The problem with name or birthdate plus ID (or name and birthdate and ID) is that they don't end up being unique. Rare, but it happens.

Because it's somewhat common, I thought I'd post to see if anyone had come across a common practice to resolve it.

I understand... I worked for a HMO for a few years in the early 90's. But there is still no excuse for being sloppy. They have all managed to get away from using SSN's as member IDs, it seems to me they could correct this problem as well.

The fundimental technical issue is the source system needs to provide some means to uniquely and consistantly identify a specific person. Either it is able to do it or not. If the latter is the case, you are basically SOL unless changes are made to the source system. There isn't anything you can do at the back end if the front end is unable to provide a consistant identification.

The problem is, if you try to invent some way of creating a unique identity on the back end (using birth date, enrollment date or some other attribute(s)), you are simply creating a new set of problems for which the data warehouse, not the source system, become responsible for. The biggest of which is the member may have a changing identity over time making very difficult to do outcomes and quality of care analysis. By 'covering up' flaws in the operational system, you effectivly degrade the reliability and trust of the data warehouse.

What you are discounting is that the claim processing system doesn't really care which member submitted the claim, only that the member has coverage and that the claim should be paid. So from the OLTP perspective, all business rules are being met. This is an example of problems when repurposing data from another source. The data quality may or may not meet the needs for which you are trying to meet. Most insurance companies are trying to better manage their members through managed medical care. The problem is identifying members since the claims systems generally don't care. Think of same sex twins and their claims, ugh. When you go to the applications they don't really care since their business rules are being met and no executive wants to fund the enhancement since most of these systems were written by EDS back in the 80's.

There's no easy answer here unfortunately. You can buy some identify software to help and that may get you 95-98% match rate on your members.

BoxesAndLines wrote:What you are discounting is that the claim processing system doesn't really care which member submitted the claim, only that the member has coverage and that the claim should be paid. So from the OLTP perspective, all business rules are being met.

Times have changed and I think HIPAA would disagree that 'all business rules are being met'. Omaha's company should embrace the 'opportunity' and bring their operational systems into conformance. Besides, its should be THAT big of a change... unless (and my guess is it is probably very likely) there is code looking at the '02' suffix and reading some meaning into it...

ngalemmo wrote:

BoxesAndLines wrote:What you are discounting is that the claim processing system doesn't really care which member submitted the claim, only that the member has coverage and that the claim should be paid. So from the OLTP perspective, all business rules are being met.

Times have changed and I think HIPAA would disagree that 'all business rules are being met'. Omaha's company should embrace the 'opportunity' and bring their operational systems into conformance. Besides, its should be THAT big of a change... unless (and my guess is it is probably very likely) there is code looking at the '02' suffix and reading some meaning into it...

I agree with you 100%. The reality is that I've worked with well funded health insurance data warehouses that could not get this pushed through. Either the claim system couldn't get the information reliably or the change was too big.

There's no HIPAA violation (privacy or code set). A provider submits an X12 claim with the Group/Subscriber ID/Suffix. The claim system adjudicates the claim according to the policy rules associated with that Group/Subscriber ID/Suffix number, returns the results to the provider, and adjusts the out of pocket expenses, life time payouts, etc. for the insured.

The feedback loop is that after the fact a remittance is sent to the provider and an EOB is sent to the name and address in the OLTP system of the insured associated with the Group/Subscriber/Suffix (not the subscriber, unless the insured is under 12). The system meets all the transaction processing rules required by the business to process claims. It's fully HIPAA compliant. The only "intelligence" embedded in the "02" suffix is that it's associated to the subscribers spouse. It's compliant, but it's not DW/BI friendly.

Again, just seeing what others have done for work arounds for this problem. Thanks for the discussion.