Dimensional Design i healthcare business with hundreds of yes/no answers

View previous topic View next topic Go down

Dimensional Design i healthcare business with hundreds of yes/no answers

Post  RogerS on Sat Oct 06, 2012 10:18 am

Hi

Piece by piece, dimensional models seems clearer by the day, but I don't understand how the following situation:

There are several complications that can happend in an operation. This is handled by the following questions:

Complication: Yes/No

On Yes, the following questions must by answered:
Minor bleeding: Yes/No
Major bleeding: Yes/No
...
Patient died during surgery: Yes/No

How do I create a dimensional model for this? I have read that a dimension has fewer lines than the fact table, but in this case, how can it be so? Every operation has the above questions to answer. I is the solution to have 100 dimensional tables? My gut feeling says, on complication dimension table shoud be enough.

I'm lost
\Roger

RogerS

Posts : 5
Join date : 2012-09-29
Age : 45
Location : Sweden

View user profile

Back to top Go down

Re: Dimensional Design i healthcare business with hundreds of yes/no answers

Post  hang on Sat Oct 06, 2012 7:05 pm

Use bridge table to turn columns into rows. So instead of hundreds of Y/N attributes, you store operation results as texts in the result dimension and group results in a bridge table with group key repeated for all the results on every patient. Your fact table will have the group FK not result dimmension FK.

When you join your fact table with bridge on the group key, the result will effectively give you the facts at the grain of result level.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Dimensional Design i healthcare business with hundreds of yes/no answers

Post  RogerS on Sat Oct 06, 2012 9:14 pm

hang wrote:Use bridge table to turn columns into rows. So instead of hundreds of Y/N attributes, you store operation results as texts in the result dimension and group results in a bridge table with group key repeated for all the results on every patient. Your fact table will have the group FK not result dimmension FK.

When you join your fact table with bridge on the group key, the result will effectively give you the facts at the grain of result level.

Thanks hang, this starts my head to spin. I never thought of using a bridge table for this. I recently found out that my problem with one to many diagnosis could use this. I have to draw the model to get my head straight.

\Thanks

RogerS

Posts : 5
Join date : 2012-09-29
Age : 45
Location : Sweden

View user profile

Back to top Go down

I have been thinking about this, is this what you mean hang?

Post  RogerS on Wed Oct 10, 2012 12:23 pm

hang wrote:Use bridge table to turn columns into rows. So instead of hundreds of Y/N attributes, you store operation results as texts in the result dimension and group results in a bridge table with group key repeated for all the results on every patient. Your fact table will have the group FK not result dimmension FK.

When you join your fact table with bridge on the group key, the result will effectively give you the facts at the grain of result level.

Is this what you mean?

Fact table
...
Fact_idcomp_bridge_id
510
10 15

comp_bridge table
...
comp_bridge_idcomp_dim_id comp_answer_text
101 Yes
102 No
103 Yes
151 No
152 No
153 No

comp_dim table
...
comp_dim_idVariablename
1Complication
2 Minor bleeding
3 Patient died during surgery

But if so, is there any advantage using this model, I mean, I have 400 variables more with variants of Yes/No. Maybe 10 or so is measures like length and weight. It will lose simplicity if I have to use a bridge table för every section. In this case if I have n complications questions, I will have a number of rows in my comp_dim table equals to the n, number of rows in my bridgetable is nx(number of surgeries). And if there is 4-5 measures in complications I have to create a 2nd dimension comp_dim_measuers?

RogerS

Posts : 5
Join date : 2012-09-29
Age : 45
Location : Sweden

View user profile

Back to top Go down

Re: Dimensional Design i healthcare business with hundreds of yes/no answers

Post  hang on Thu Oct 11, 2012 1:22 am

Close, but not quite what I meant. There should be only two FKs in bridge table as follows:

comp_bridge table

comp_bridge_id, comp_dim_id
10, 1
10, 3

I would name comp_bridge_id as comp_group_id and comp_dim_id as comp_id. You only have entries for 'Yes' in the bridge so that you would only have 2 records for group 10 in your case, even if the comp_dim contains hundreds of records. The design is Kimball's generic solution for multivalued dimensions with potentially open ended list of options (comps in your case).

You can use NOT EXISTS... comp_id IN (...) subquery to work out the fact with 'NO' answers, and (SELECT COUNT(*) ...where comp_id in (...)) = ? for 'YES’ answers, where ? represents the number of comps you want filter on.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Dimensional Design i healthcare business with hundreds of yes/no answers

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum