Avoiding snowflakes

View previous topic View next topic Go down

Avoiding snowflakes

Post  hirths on Sun Oct 30, 2011 9:01 pm

Thanks everyone for their input in these forums, I have been able to answer many of my questions by just browsing what's already there.

However, I'm a bit stuck on this one.

I have a Patient dimension, which contains information like
- URNumber (business key)
- Title
- Surname
- GivenName
- DateOfBirth
- MedicareNumber
- Address

I also want to add the following information, but I already have a Location dimension containing this hierarchy
- PostCode
- Suburb
- StatisticalLocalArea
- LocalGovernmentArea
- ResidentialRegion
- State
- Country

Should I replicate this information in the Patient dimension, or snowflake from the Patient dimension to my Location dimension ?

Thanks in advance
Regards
Steve


hirths

Posts : 4
Join date : 2011-02-07

View user profile

Back to top Go down

Re: Avoiding snowflakes

Post  ngalemmo on Mon Oct 31, 2011 12:38 am

Why not have location as an FK on the fact?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Avoiding snowflakes

Post  hirths on Mon Oct 31, 2011 12:59 am

Thanks ngalemmo for your reply.

Could be a number of facts, Admission would be one of them.

I would need to keep the Location business key components (postcode/suburb) in the Patient dimension as a Type 2 SCD.
Then put something like PatientLocationID at time of Admission in the Admission fact.

Does that sound sensible ? I'm having trouble letting go of 20+ years on 3NF !!

Thanks
Steve


hirths

Posts : 4
Join date : 2011-02-07

View user profile

Back to top Go down

Re: Avoiding snowflakes

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