SCD built from fact table

View previous topic View next topic Go down

SCD built from fact table

Post  jonahMilton on Tue May 07, 2013 3:25 pm

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

jonahMilton

Posts : 1
Join date : 2013-05-07

View user profile

Back to top Go down

Re: SCD built from fact table

Post  Mike Honey on Tue May 07, 2013 8:00 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: SCD built from fact table

Post  ngalemmo on Wed May 08, 2013 1:03 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: SCD built from fact table

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