Multi-value data column in OLTP modeled in datamart

View previous topic View next topic Go down

Multi-value data column in OLTP modeled in datamart

Post  westcam on Thu Nov 29, 2012 5:08 pm

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?

westcam

Posts : 2
Join date : 2012-11-29

View user profile

Back to top Go down

Re: Multi-value data column in OLTP modeled in datamart

Post  Jeff Smith on Thu Nov 29, 2012 6:04 pm

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

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Multi-value data column in OLTP modeled in datamart

Post  westcam on Thu Nov 29, 2012 8:30 pm

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


westcam

Posts : 2
Join date : 2012-11-29

View user profile

Back to top Go down

Re: Multi-value data column in OLTP modeled in datamart

Post  BoxesAndLines on Fri Nov 30, 2012 3:43 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Multi-value data column in OLTP modeled in datamart

Post  ngalemmo on Fri Nov 30, 2012 5:59 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multi-value data column in OLTP modeled in datamart

Post  hang on Fri Nov 30, 2012 6:28 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Multi-value data column in OLTP modeled in datamart

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum