Help with true one-to-many relationship

View previous topic View next topic Go down

Help with true one-to-many relationship

Post  elgabito on Fri Jul 09, 2010 9:38 am

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.

elgabito

Posts : 3
Join date : 2010-05-25

View user profile

Back to top Go down

Re: Help with true one-to-many relationship

Post  ngalemmo on Fri Jul 09, 2010 11:35 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

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