June 25, 2013 at 12:11 pm
Hi all,
We are trying to expand existing datamart for patient visit monitoring, and I would like to get some suggestion or recommendation from DW experts here on SQL sever central 🙂
This is our current DM design:
- DimPatient
- DimProcedure
- DimLab
- DimMedicine
- DimLocation
- DimPatinetCondition
- FactVisit
Now challenge I have here is, my customers now wants to get detail information regarding Medicine administered, lab test result, Procedure performed for each patient visit.
So, now we are tying to redesign/recreate DM, and not sure what would be the best design/practice to accommodate user request...
This is my new design:
- DimPatient
- DimProcedure
- DimLab
- DimMedicine
- DimLocation
- DimPatinetCondition
- DimVisit
- FactLabRecord
- FactMedicineRecord
- FactProcedureRecord
factVisit is converted to degenerative Dimension (we have visit id).
Does this look okay??
July 3, 2013 at 9:30 am
BlackGarlic (6/25/2013)
Hi all,We are trying to expand existing datamart for patient visit monitoring, and I would like to get some suggestion or recommendation from DW experts here on SQL sever central 🙂
This is our current DM design:
- DimPatient
- DimProcedure
- DimLab
- DimMedicine
- DimLocation
- DimPatinetCondition
- FactVisit
Now challenge I have here is, my customers now wants to get detail information regarding Medicine administered, lab test result, Procedure performed for each patient visit.
So, now we are tying to redesign/recreate DM, and not sure what would be the best design/practice to accommodate user request...
This is my new design:
- DimPatient
- DimProcedure
- DimLab
- DimMedicine
- DimLocation
- DimPatinetCondition
- DimVisit
- FactLabRecord
- FactMedicineRecord
- FactProcedureRecord
factVisit is converted to degenerative Dimension (we have visit id).
Not sure about the reason of converting FactVisit into a degenerate dimension - looking from outside I would say that ...
- a patients visit is still a visit, still a fact
- FactVisit was part of the original design, I'm sure is was/is serving some purpose
- I'm pretty sure FactVisit can be usefull to link together everything that is related to such a visit including location, time, Doctor, requested tests, medical insurance, etc. etc.
_____________________________________
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.July 3, 2013 at 11:36 am
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 🙁
July 3, 2013 at 12:13 pm
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.July 3, 2013 at 12:32 pm
Thank you Pablo!
I will try to incorporate your design suggestion. We are still on design stage, so I am trying different design schema.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply