A nice neat dimension, but it has a comma delimited attribute

View previous topic View next topic Go down

A nice neat dimension, but it has a comma delimited attribute

Post  Al Wood on Fri Feb 11, 2011 11:16 am

Hi,

I have to create a drug dimension, but one column is [chemical] and its a column delimited string. How can I put all the chemicals in one column so reports can easily use them?

Al Wood

Al Wood

Posts : 46
Join date : 2010-12-08

View user profile

Back to top Go down

Re: A nice neat dimension, but it has a comma delimited attribute

Post  meb97me on Fri Feb 11, 2011 12:06 pm

do you mean the the list of checmicals within a drug is supplied a delimited list of chemical and basically what you want to do is split each of the chemicals out into seperate rows?

meb97me

Posts : 34
Join date : 2010-07-28

View user profile

Back to top Go down

Re: A nice neat dimension, but it has a comma delimited attribute

Post  Jeff Smith on Fri Feb 11, 2011 12:30 pm

The Chemicals should be in a seperate Dimension, one row for each Chemical. A fact table should join the Chemicals to the Drug.

If you wanted to link Chemicals to something like Pateint, to see all of the Chemicals in the drugs taken by a patient, you could create fact table at the Patient/Drug/Chemical level.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: A nice neat dimension, but it has a comma delimited attribute

Post  Al Wood on Fri Feb 11, 2011 12:51 pm

Yes, meb97me, some of the drugs have many chemicals, in a comma-delim string. But most have none.

Thanks, Jeff Smith, I see what you mean. I was going to use a drug issues fact table as the lowest granularity, but I might consider a chemical issues fact table now. I suppose, where the drug has a blank chemical string (most of them do) I will need to load just one row with the drug sk, referring to a drug in the Dim with a blank or "UNKNOWN" chemical. So my drug dimension will just have more rows; every drug with a chemical set will appear multiple times? Interesting! But then I can't easily use that fact table to count drug issues.?

Al Wood

Al Wood

Posts : 46
Join date : 2010-12-08

View user profile

Back to top Go down

Re: A nice neat dimension, but it has a comma delimited attribute

Post  Jeff Smith on Fri Feb 11, 2011 5:07 pm

You can use the fact table to count durg issues. A colum in the table can be a count of total Chemicals in the drug. You can get a count of issues by summing (1/Count of total Chemicals) - then round it to the nearest integer.

Or, you can create an aggregate fact table at the Drug level.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: A nice neat dimension, but it has a comma delimited attribute

Post  ngalemmo on Tue Feb 15, 2011 1:44 am

I would treat it as a multivalued dimension. I assume the composition of a drug is always the same (otherwise it would have a different identifier), then the key to drug dimension can serve as the chemical group key. There would be a separate chemical dimension and a bridge table made up of the drug key and the chemical key. You may also include a quantity value in the bridge if that is available for the chemical (i.e. volume in a dose or tablet).

You would then simply use the drug key off the fact table to get the chemical composition. Where you don't have a chemistry, you wouldn't have an entry.

One advantage to this approach is chemical information is cumulative. If you have drugs without chemistry, and later you receive the chemical makeup, adding it to the bridge makes the chemistry available to all instances of the drug in the fact table regardless of when the facts occured.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: A nice neat dimension, but it has a comma delimited attribute

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