Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Fact table or Dimension table? Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 12:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 11:32 AM
Points: 43, Visits: 289
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??
Post #1467313
Posted Wednesday, July 3, 2013 9:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1470128
Posted Wednesday, July 3, 2013 11:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 11:32 AM
Points: 43, Visits: 289
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
Post #1470176
Posted Wednesday, July 3, 2013 12:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1470189
Posted Wednesday, July 3, 2013 12:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 11:32 AM
Points: 43, Visits: 289
Thank you Pablo!

I will try to incorporate your design suggestion. We are still on design stage, so I am trying different design schema.
Post #1470199
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse