Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create Fact table or Dimension table?


Create Fact table or Dimension table?

Author
Message
BlackGarlic
BlackGarlic
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 316
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 Smile

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??
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
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 Smile

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.
BlackGarlic
BlackGarlic
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 316
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 Sad
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
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 Sad


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.
BlackGarlic
BlackGarlic
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 316
Thank you Pablo!

I will try to incorporate your design suggestion. We are still on design stage, so I am trying different design schema.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search