Age Lookup table to fact table

[teccum]

Scenario:

Medical Background:
Dimensions tables : Provider, DRG, Type of Service, Member, ICD9 Codes, Line Of Business, Age, Time.
Fact table: ClaimInpatientFact, ClaimOutpatientfact, ClaimPharmafact, Memberfact.
Granularity of Data: Month level.

Challenge: Decision to have the Age hierarchy(lookup Age -> Lookup Category --> Lookup Group) to link to the Member dimension table or to Memberfact table.

My idea is to have the Age lookups be linked to the Member dimension table since linking it to the Member fact table will increase the size of the fact table and also most of the queries can be answered by linking to the dimension table itself. I am also having the Member Birth Date in the member dimension table so the OLAP tool can be used always to calculate the Member's age. Here I would be able to get the member's age on the date of running the report because the member's age calculation will be current date minus the birth date.

But the requirement is, user wants to find out the Age of the member at a point of a time say memberwise, Agewise, Claim amount for year 2003. So the report should show the member age that was on the year 2003 rather than the current age. So similarly when the user chooses the year 2004 then it should show the current age at that point of time. Can having the age id from the lookup Age table in the Member fact table solve this requirement ? since the fact is stored at the month level. How do I model this and is their any other way.?

Thanks
teccum.

 

[johnherman]

Yes, I think you want to have an AgeID in the fact table as well as storing the DOB in the dimension and providing a function or calcuated field which is Age determined by providing DOB and AsOfDate

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[teccum]

Thanks John. I will join lookup Age to the Member fact table.

The reason I joined the lookup Age Hierarchy (lookup Age, Lookup Age Category, Lookup Age Group) to the Member dimension table is that, it will not increase the size of the Member fact table and also I can create 1:M relationship between member dimension table and the Age Lookup table by populating only the members for the Age records where they have a claim amount. Their by If they want to find out the age of the member then by drilling up from the member dimension table to the Age lookup table the users will get the appropriate age at that point of time when the claim amount was paid etc.

This is something like populating only the appropriate age records for the members from the member dimension table into the Lookup Age table and also adding a column for the Month field. I understand that I have to validate seperately against the claim amounts and other facts just to populate the Lookup Age table. By this way If their is a report requirement then I use a filter against the month and age field in the lookup Age table.

I did these steps because I do not want to increase the size of the fact table and also under the blind default assumption that the Age should always be related to a member or a dimension table and not to a fact table.

Thanks
teccum.