BlackGarlic (7/3/2013)
Thanks for your response.Yes. Original Fact visit still hold some information. Problem is, new facts I am trying to bringing in have different granularity.
For each visit, patient could have multiple lab tests, orders and procedures, and customer wants to see the per visit per transaction level data (so first look at the visit level info, then drill across all the relevant lab, medicine, procedure).
Initially, I added all transaction level fact attribute to old FactVisit table and changed granularity level. But we have some calculated values for visitLevel, and if patient have multiple transaction action, this visit level value would get repeated multiple times (sometime over 100 times!).
So, I went through some books and came up with new design 🙁
I would consider to keep FactVisit as it is - one row per visit describing the visit in general.
I would also consider creating a FactVisitEvent table - one row per event triggered during each particular visit meaning, if the Dr asked for five lab studies and a cat scan then six rows would be inserted into FactVisitEvent while only one on FactVisit.
Having said that, obviously I'm not there and I know nothing about the system, business rules, business requirements, etc but, in general I would "add" to an originally sound design rather than changing the original core structure.
Just my two cents.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.