SCD built from fact table

[jonahMilton]

I'm working on a project where the extraction source is putting what is going to be dimensional data in each row of what will be the fact. In this case further drug information is stored in each drug administration. Only a limited drug information like name and description will be in a referenced code table. Dose, dose form, strength, etc will be in EACH row of the administration source table. I'd like to build a drug dimension, but really don't like the fact I need to go to the administration table. I *think* this is really bad practice. I'd prefer to extend the source code table to have all the drug information. Am I making a big deal about nothing? Is is common to check the dimension before inserting a fact and insert if it doesn't exist. Somewhat new to the dw world, very experienced in the relational world.

Thanks,
Jonah

[Mike Honey]

Hi Jonah,

As far as possible, I would try to ignore the data structures of the source system and focus on designing the dimensional model to suit the reporting & analysis requirements. A good dimensional model will survive source system schema changes or integration with a completely new source system.

I certainly wouldnt wait for a source system to reorganise it's data structure to suit the dimensional model.

I think the specific scenario you describe is what Kimball terms an "early arriving fact":

http://www.kimballgroup.com/2004/08/02/design-tip-57-early-arriving-facts/

Good luck!
Mike

[ngalemmo]

What you do will depend on what those values mean on the administration record. If this is information collected at the time the drug is dispensed, I would not intermix it with static descriptive data. If it represents the manufacturers description then you can intermix it.

You see stuff like this often in health care. Claim forms, for example, have patients age, which represents the age told to the nurse or a estimate of the patients age by the nurse or physician. The insurer has the patient's date of birth in their static record. The insurer most certainly would not update the static record, but rather keep the claim age with the claim.

So, if this is dispensing information recorded by the pharmacist, I would keep it in a separate dimension. Possibly a junk dimension.

[Sponsored content]