Help with true one-to-many relationship
2 posters
Page 1 of 1
Help with true one-to-many relationship
I have been fortunate (or unfortunate?) to only have encountered one to one dimension/fact relationships in my previous DW experience. However I am now looking at medical claim payments, and have a service line fact. It relates to several dimensions (procedure, time, office, status, payer) which are all one-to-one.
It now also has to relate to procedure modifiers. In the OLTP there are four columns for modifiers for each service line. The office can put them in any order, they are not hierarchical and any modifier can relate to any procedure. There can be 0-4 modifiers per line.
Business users will need to be able to select the modifier dimension and view all facts for those modifiers. I am struggling with how to model this due to lack of direct experience on this type of case.
Any help is much appreciated.
I am using Msft stack if it matters.
It now also has to relate to procedure modifiers. In the OLTP there are four columns for modifiers for each service line. The office can put them in any order, they are not hierarchical and any modifier can relate to any procedure. There can be 0-4 modifiers per line.
Business users will need to be able to select the modifier dimension and view all facts for those modifiers. I am struggling with how to model this due to lack of direct experience on this type of case.
Any help is much appreciated.
I am using Msft stack if it matters.
elgabito- Posts : 3
Join date : 2010-05-25
Re: Help with true one-to-many relationship
This is a multivalued dimension. Kimball discusses it in his books and it has been discussed at length in this forum.
The basic modeling technique is as follows:
You have a Modifier Group dimension which is basically a junk dimension to capture every unique combination of modifiers you encounter. Since sequence does not matter, you sort the modifiers in ascending sequence when identifying the particular collection to keep the group dimension as small as possible.
The modifier group key goes on the fact table.
You have a bridge table with the modifier group key and the modifer key referencing the modifier dimension. There is one row for each modifier in the group. You may include other values in the bridge, such as the number of modifiers in that group, should you need to do allocations for some reason. (Although I doubt that would be needed in this particular case).
When a user queries, they select a modifier from the modifier dimension. This joins to the bridge and the bridge joins the the fact. The result would be to select facts that have groups that have that modifier.
The modifier group dimension is there to identify unique groups and is seldom, if ever, used in a query. Doing this helps keep the bridge table as small as possible.
You would do the same sort of thing for diagnoses, althogh most of the time the business is usually only interested in the DRG... however clinical research may be interested in specific diagnosis combinations.
The basic modeling technique is as follows:
You have a Modifier Group dimension which is basically a junk dimension to capture every unique combination of modifiers you encounter. Since sequence does not matter, you sort the modifiers in ascending sequence when identifying the particular collection to keep the group dimension as small as possible.
The modifier group key goes on the fact table.
You have a bridge table with the modifier group key and the modifer key referencing the modifier dimension. There is one row for each modifier in the group. You may include other values in the bridge, such as the number of modifiers in that group, should you need to do allocations for some reason. (Although I doubt that would be needed in this particular case).
When a user queries, they select a modifier from the modifier dimension. This joins to the bridge and the bridge joins the the fact. The result would be to select facts that have groups that have that modifier.
The modifier group dimension is there to identify unique groups and is seldom, if ever, used in a query. Doing this helps keep the bridge table as small as possible.
You would do the same sort of thing for diagnoses, althogh most of the time the business is usually only interested in the DRG... however clinical research may be interested in specific diagnosis combinations.
Similar topics
» Need an Entity-Relationship (ER) Diagram (that describe the relationship between entities)
» many to many relationship help
» many to many troubles
» Bridge table for patient diagnosis
» Should a True/False value be a dimension
» many to many relationship help
» many to many troubles
» Bridge table for patient diagnosis
» Should a True/False value be a dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum