Modeling suggestions

[rajeshwarr59]

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.

[zoom]

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.

[rajeshwarr59]

Thank you.

[Sponsored content]