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.