Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

1 to many relationship

2 posters

Go down

1 to many relationship Empty 1 to many relationship

Post  Jeff Smith Wed Feb 17, 2010 3:22 pm

I work for a health related insurance company. A member can belong to multiple Groups. A member can have only 1 primary care provider. I am creating a fact table to be used to tell Groups the Frequency at which their members are seeing their provider for maintenance visits regarding their illness. I want to use the same data to tell Providers the Frequency of their patients are coming for maintenance visits.

I figure I have 2 choices: 1) Create a fact table with Member, Group, and Primary Care Provider. Either add a column to weight Members based on the number of Groups they belong to when rolling up the data for Providers (sum Weighted Count) or Count Distinct. Or 2) Created a bridge table with Group and Member and have only the Member and Primary Care Provider. If I create the Bridge Table, I either need to include the Year Month (we are aggregating the data monthly) or create a Begin and End date for the relationship between the Member and Group.

Which option would have the best performance? Option 2 with Begin and End dates feels like the correct answer but I'm wondering if Option 1 would have the best performance. In the Reporting Tool, create "Provider Measures" that multiple the actual measures in the Fact Table by the weighting factor.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

1 to many relationship Empty Re: 1 to many relationship

Post  ngalemmo Wed Feb 17, 2010 5:03 pm

I assume by group you mean employer group. Your first choice is the more traditional enrollment model. You would still have effective dates and weighting factors as you mentioned for you second choice as you still have a member-group relationship. The first structure will perform better than the second. I don't see the need to go beyond that.

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

Back to top Go down

Back to top

- Similar topics

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