Maintaing Relationship Through Fact

View previous topic View next topic Go down

Maintaing Relationship Through Fact

Post  rohanf on Sat Apr 05, 2014 4:06 pm

Hi All,

I have identified below dimensions so far:-
1: Client
2: Role (of the client on a policy e.g. Policy Holder, life 1 , Life 2) [Static list of Benefits on the system]
3: Application
4: Policy
5: Benefit (Static list of Benefits on the system)

Initially thought of having the relationship between these to be stored in the fact table since a fact helps to resolve the many to many relationships (Correct me if I am wrong).  The measures are at Client->Application->Policy->Benefit->Role level also at Policy --> Benefit level. In case I store the relationship between all dimension in a fact , particularly Policy & benefit , I am not able to understand how will I be able to view the active relationship at a given time?

I mean in the fact table the relationship is as below:-
Policy has benefit A in first month. -> Gets benefit B in 3rd Month (A is still active and has history of changes if any) -> Then a Benefit C is added in say 6th Month (A & B is still active and has history of changes if any).
Now in 6th Month how will I get all the Active relationship? All active relationships span across a period of time and a join through active policy record will return me multiple historical records from the fact (for A & B)?

Alternatives that I thought.
1: Denormalize Policy and Benefit and put them in the same Dimension:- But want a conformed Policy Dimension for other Datamarts
2: Create a Policy and Benefit Relationship Bridge table and connect that to the Fact table. :- But there are other relationships as well like Policy has Many roles too, Application has Many policies, This would mean create a bridge table for all these cases. Is this the right approach?

Would appreciate any help!

Thanks & Regards
Rohan

rohanf

Posts : 13
Join date : 2014-04-02

View user profile

Back to top Go down

Re: Maintaing Relationship Through Fact

Post  BoxesAndLines on Sun Apr 06, 2014 2:10 pm

Create a daily snapshot fact. This will record the current state of the business each day.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Maintaing Relationship Through Fact

Post  rohanf on Sun Apr 06, 2014 2:39 pm

Yes that would be helpful, but we are processing daily changes and a benefit within a policy may not change since its inception and thus may not be part of source feed that gets changes. i.e. the incoming data is incremental and not a snapshot data.

rohanf

Posts : 13
Join date : 2014-04-02

View user profile

Back to top Go down

Re: Maintaing Relationship Through Fact

Post  ngalemmo on Sun Apr 06, 2014 3:00 pm

From what you describe, I get the impression you are trying to accomplish tracking state as well as measures associated with the policies in the same fact table. Considering the different grains, its not possible.

You need to break your model down into what each fact is supposed to do and set the grain as appropriate.

As far as the state of policies goes, a factless fact table with all the necessary dimensions will work fine. All you need to do is add effective and expiration dates as degenerate dimensions to handle the time period of the relationship.

For measures, these would be carried in the same or other facts depending on the grain of the measure.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Maintaing Relationship Through Fact

Post  rohanf on Sun Apr 06, 2014 6:04 pm

Thanks, that makes sense, I had thought of a relationship table/factless Fact table (fact_1) with following columns:-
1: Client fk
2: Application fk
3: policy fk
4: Benefit fk
5: role fk
6: Start date
7: End Date

and a second fact (fact_2) with a fact_1_fk having the same granularity but more frequently changing (detailed) than the fact_1. I had below questions related to this:-

Question 1: Is it appropriate to apply SCD2 on the fact_1 (Even though its a factless fact) to capture the active relationship and past history? Assuming source does not have a start and end date!

Question 2: Since its an incremental load and we get new or changed relationships but not relationships that now cease to exist. In this case how would we close these relationships that were once active and now are not?

Question 3: The approach of a fact_1_fk in fact_2 may lead to multiple records for the same fact_1_fk value in fact_2. This would definitly create a problem unless the query is always accompanied by a date? Is there a better way?

Appreciate your help and time!

Regards
Rohan

rohanf

Posts : 13
Join date : 2014-04-02

View user profile

Back to top Go down

Re: Maintaing Relationship Through Fact

Post  ngalemmo on Sun Apr 06, 2014 6:26 pm

1. Yes you can. It would capture the version of the dimension at the time the relationship is established. You would not add new rows every time a dimension changed.

2. That is a problem. The source should tell you when something expires. An alternative may be to calculate the expiration date based on the inception date and the term of the policy or benefit.

3. You should not have foreign keys referencing other fact tables. Facts are combined by aggregating them across common dimensions.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Maintaing Relationship Through Fact

Post  rohanf on Mon Apr 07, 2014 4:41 am

Thanks for the addressing all the points!

regarding point one:-
In order to avoid adding "new rows every time a dimension changed", would it be correct to add a superkey (not sure what to call it) (Its a key acting as a natural key that will remain constant across all dimension records for a Business Natural key). Would then use these key's as FK in fact_1 rather than actual primary surrogate key.
I think this will enable us to isolate the factless fact from changes to the dimensions!!! But would require us to query based on dates or only active records to avoid multiple records to be returned from the join.

Would this be a correct approach, do you forsee any hidden challenges that I am overlooking?

All this will be handled in the ETL (generating the super key etc). I am planning to put this for below dimensions:-
1: Application
2: Client
3: Policy

Please suggest!

Regards
Rohan



rohanf

Posts : 13
Join date : 2014-04-02

View user profile

Back to top Go down

Re: Maintaing Relationship Through Fact

Post  ngalemmo on Mon Apr 07, 2014 9:33 am

The techique is to store two keys on the fact: the true type 2 key and the 'stable' key you mention. You would use the stable key to locate the current row for the member (filtering on the current flag).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Maintaing Relationship Through Fact

Post  Sponsored content


Sponsored content


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