Modeling suggestions

View previous topic View next topic Go down

Modeling suggestions

Post  rajeshwarr59 on Tue Jul 21, 2015 1:14 am

I am trying to develop a dimensional model for member eligibility and their benefits.

So far, based on the requirements I have I was able to come up with few dimensions like:

member dimension - member demographic information
contract dimension - contract id, contract effective date, contract end date
group dimension - group demographic information
plan dimension - plan name, plan class and details of what's the deductible amounts etc
member fact - contains attributes like foreign key to contract dim, group dim and few metrics included like membership count.contract count.
benefit fact - which acts as a bridge table which links back to member, plan, product and benefit dim tables.


Requirements I have around member eligibility: what's the medical membership counts, pharmacy membership counts, track enrolled members, termed members etc. Requirements around benefits: what are all the benefits available by plan, contract, group and what are all the active plans etc.

Am looking at designing the member eligibility fact that has attributes like member id, effective date and expiry date of the member, contract id, group id, and metrics like membership counts and contract counts. The part that I cant get to wrap my head around is if I should really be including the member effective and expiry dates on this member eligibility fact table or should these be included on contract dimension table? And if I add flags to indicate what type of coverage a member has(medical, pharmacy etc) will I be messing up the membership counts? I am thinking of making it more like an aggregate fact table which provides membership/contract count metrics (along with foreign keys to other dim tables) but unable to figure out a better way of designing this eligibility/coverage part. And assuming I have plan table, benefit fact/dim tables built, do I still need to include this eligibility information(eff dt/term dates of members) on member eligibility fact or is this something that can be addressed by the plan, benefit fact/dim tables and where can I have this medical/ pharmacy etc coverage flag columns added?

I am pretty new to data modeling so any help is greatly appreciated.

rajeshwarr59

Posts : 21
Join date : 2015-06-26

View user profile

Back to top Go down

Modeling suggestions

Post  zoom on Tue Jul 21, 2015 8:37 am

Data modeling is done around the business process. Please ask your business users about the relationships about those dimensions. You would assign those diminutions correctly when you understand their relationships to each other. A contract effective and expire date has nothing to do with a person/member insurance plan effective and expire date. Many people tie to a contract. If a person move from one plan to another plan and change their insurance company, then you cannot expire that contact because other people still tie to that contract.
So you do need effective and expire date in your Fact table. I think your Fact grain is 'a member'. Counting the rows in the Fact table for a specific contract where Fact expire date is Null ( or you can assign expire date as 1/1/3500 in fact table to find open membership) will give you current number of members. To answer what kind of coverage they have, that can be done having medical and pharmacy flags in the Fact table. Once you have this Fact table, a report can aggregate counts accordingly.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Modeling suggestions

Post  rajeshwarr59 on Thu Jul 23, 2015 10:30 pm

Thank you.

rajeshwarr59

Posts : 21
Join date : 2015-06-26

View user profile

Back to top Go down

Re: Modeling suggestions

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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