Can a conformed dimension have a varying granularity?

Hi,

Am I doing this right? It seems wrong.

I have a dimension table that is to be used by multiple fact tables. It is a drug & regimen flattened dimension. A regimen is a set of various drugs.

Three of the fact tables come from different source systems and they are:

fact_drug_issues with a drug granularity
fact_prescriptions with a drug & regimen granularity (with regimen sometimes blank)
fact_appointments with a regimen granularity

The drug and regimen codes are not the same across the systems. I hope to conform the dimensions by having various
attribute columns in the drug-regimen dimension which allows the drug/regimen to to be given reporting names by Data Stewards.
They will also group the drugs and regimens there.
From each system I generate a unique Natural Key for each drug/regimen to use to lookup the sk from the dimension.

The appointments fact table will have a different granularity to the other two.
But this means that the dimension will have a varying granularity. Is this acceptable?
Many thanks in advance,
Al Wood

In a word, no. A dimension can only represent one thing. It can be an arbirary group of things (as in a junk dimension) but it should not be a either/or kind of table. A regimen is a group of drugs, so, if you have a drug dimension, a regimen would be a multivalued dimension of drug... you would represent regimens as a bridge table.

Thanks for the reply, but I'm not sure if I should still have the dim_drug should still have a regimen attribute? It will still be used by fact_prescriptions.
I'm also not sure how to conform regimens when the prescriptions system regimen names are in dim_drug and the appointment system regimen names are in dim_regimen?

Or, should there be no regimen name in dim_drug, but it uses the bridging table to conect to dim_regimen? So then should fact_prescription join to both dim_drug and dim_regimen? But that would mean it connects in a loop to dim_regimen two ways around? I'm sensing wrongness again!

(I have manged to determine that a true hierarcy is not needed, no more than two levels anyway, which are called regimen and drug.)

Please help.
Al

There is a many to many relationship between regimen and drug. They are two different dimensions. You need an associative entity (i.e. a bridge) to relate regimens with drugs. The facts should carry regimen as the dimension and associate drugs through regimen as a multivalued dimension.

But fact_drug_issues has a drug granularity. There is no regimen data for drug issues. (I hope to be able to build a function to deduce some regimens, but that is far down the line.)

For fact_prescriptions, I can link to dim_drug, and use the bridge to link to dim_regimen, but some drugs have no regimen. Is that allowed? Do I just use a "NO REGIMEN" entry in dim_regimen?

For fact_appointments, I have an [activity description] which should be the regimen, but sometimes an activity description can refer just to a drug. It needs normally to link direct to dim_regimen, because there is no data at drug level, except, what do I do when it turns out to be a single drug? Would a hierarcy dimension help here?

Many thanks,
Al

Drug issues has drug as its dimension. No regimen. Thats fine.

As far as prescriptions go, would there not be one row per drug? If that is the case, drug would also be a dimension. If you wish to record the regimen with the prescription that is fine as well. it would just be another dimension with a 'does not apply' row for those prescriptions not accociated with a regimen OR it could be simply an entry for the drug, with a flag indicating it is not a regimen. This would allow you to cover appointments with the regimen dimenion.

If you need to relate prescriptions with appointments, you need a bridge table to relate regimens with drugs.

Thanks.

What would be in the regimen_sk field in the fact_prescription table for a drug issue that was not on a regimen, i.e. there was no regimen.
Should it point to a dim_regimen entry with the value 'NO REGIMEN - SINGLE DRUG' or similar?

We think we can have two fact tables. One showing regimen level events, combining prescriptions and appointments, and one with prescriptions and drug issues at drug level, but with a regimen_sk in there just for luck.

The regimen-to-drug map would be used to span these tables.

If you can have a regimen that has only one drug, does that mean that the fact_appointment table can have a single-drug granularity for some rows? Taking this further, can we use a 'NO REGIMEN - SINGLE DRUG' default regimen for single drugs, or would this group them together wrongly?

Al

If you treat regimen as a multivalued dimension of drug, then for single drug cases you would create a regimen for that drug and use it whenever the single drug is used as the 'regimen'.

You then have conformance. You can locate that drug in appointments through the bridge, weither or not that drug is used alone or as part of a regimen.

ngalemmo, just curious-

Why not make the fact table at the drug level with Regiment as a dimension and drug as a dimension?

With a Regiment Fact table and a bridge table, the join would be

f_regiment
INNER JOIN d_regiment
ON f_regiment.Regiment_Key = d_regiment.Regiment_Key
INNER JOIN b_regiment_drug
on d_regiment.Regiment_Key = b_regiment_drug.Regiment_Key
INNER JOIN d_drug
ON b_regiment_drug.Drug_Key = b_regiment_drug.Drug_Key

OR MAYBE

f_regiment
INNER JOIN d_regiment
ON f_regiment.Regiment_Key = d_regiment.Regiment_Key
INNER JOIN b_regiment_drug
on f_regiment.Regiment_Key = b_regiment_drug.Regiment_Key
INNER JOIN d_drug
ON b_regiment_drug.Drug_Key = b_regiment_drug.Drug_Key

But if the f_regiment was f_regiment_drug, then

f_regiment_drug
INNER JOIN d_regiment
ON f_regiment_drug.Regiment_Key = d_regiment.Regiment_Key
INNER JOIN d_drug
ON f_regiment_drug.Drug_Key = d_drug.Drug_Key

Maybe I don't understand the concept of a bridge table.