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

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

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 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

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_id	comp_bridge_id
5	10
10	15

comp_bridge table
...

comp_bridge_id	comp_dim_id	comp_answer_text
10	1	Yes
10	2	No
10	3	Yes
15	1	No
15	2	No
15	3	No

comp_dim table
...

comp_dim_id	Variablename
1	Complication
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?

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.