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

[Al Wood]

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

[meb97me]

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?

[Jeff Smith]

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.

[Al Wood]

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

[Jeff Smith]

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.

[ngalemmo]

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.

[Sponsored content]