Multi-value data column in OLTP modeled in datamart

I trying to model the data into the data mart

Below is the example of how the data is currently stored in OLTP system. There is a drug table in OLTP but the drug name is stored in the detail table. It is a vendor designed OLTP system

IncidentID MedicationInvolved

1 Tylenol|Morphine (2 drugs involved are separated by pipe | character)
2 Acetaminophen (1 drug only in this case)
3 Ibuprofen|Morphine|Seltzer (3 drugs involved are separated by pipe | character)
4 Acetaminophen|Morphine (2 drugs involved are separated by pipe | character)

The question asked by Physicans and Leaders are number of times Morphine is involved in Adverse Events

Anyone know best practice to resolve this?

The issue is the table is at the "indicident" level - one row per incident - and the question is at the Incident/Drug level.

You could have a table that takes the "Medication Involved" field and has a row for each drug.The "Tylenol|Morphine" would be 2 rows:

Tylenol|Morphine Tylenol
Tylenol|Morphine Morphine

coming back what would be the best way to model this in datamart where the BI end users are not tripping over themselves when they have find out number of incidents where morphine or tylenol is involved?

Does the multivalued dimension and bridge table apply in this scenario and what would be the benefits

Multi-valued columns are rarely a good idea in any database. Substringing columns is not an effective method for querying data. Sounds like a factless fact table, Prescribed Medication where Incident is a dimension.

A multi-valued dimension approach is pretty much the way to go. The bridge table offers a lot of flexibility when trying to look for instances of different combinations of drugs.

There are two ways of building such a bridge. You can create separate collections of relationships for each fact, or store only unique collections in the bridge and associate a key with each unique collection (i.e. a drug group key). The latter is more work to code, but usually results in a much, much smaller bridge table. Load times for the latter approach usually improve quickly as, over time, new unique combinations become rare.

BoxesAndLines wrote:Multi-valued columns are rarely a good idea in any database. Substringing columns is not an effective method for querying data. Sounds like a factless fact table, Prescribed Medication where Incident is a dimension.

Agree, and Incident is a dimension, but degerate by IncidentID. However, when you see an open ended multivalued dimension to the fact, it's likely the candidate for bridge table.

ngalemmo wrote:A multi-valued dimension approach is pretty much the way to go. The bridge table offers a lot of flexibility when trying to look for instances of different combinations of drugs.

That's exactly what I am thinking. I guess you do need a bridge table to group drug prescriptions instead of trying to change grain of your Incident fact table. I imagine the bridge table would have a group Key with its value repeated for each drug prescribed to an incident. The group key will be referenced in the Incident fact table. The group bridge table may not grow alarmingly after it has cover most drug combinations. Eventually group key may be referenced by multiple incidents in the fact table.

With help of bridge table, you can always count the incident uniquely and apply some interesting IN or EXIST WHERE clause to filter on drug items. If you join the fact table with bridge directly on group key without constraint, you effectively produce a fact at grain of drug by incident.