Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SCD built from fact table

3 posters

Go down

SCD built from fact table Empty SCD built from fact table

Post  jonahMilton 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.



Posts : 1
Join date : 2013-05-07

Back to top Go down

SCD built from fact table Empty Re: SCD built from fact table

Post  Mike Honey 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":

Good luck!
Mike Honey
Mike Honey

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

Back to top Go down

SCD built from fact table Empty Re: SCD built from fact table

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

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

Back to top Go down

SCD built from fact table Empty Re: SCD built from fact table

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum