A data modeling question

[rrrkrishnan]

Have a question regarding the design of a party account relationship table in a financial services model.
Say there can be many parties associated with an account , and there is a role type column which defines the role a party plays on the account and there is a primary party indicator and these two can change.
So the current structure looks like this.

party
account
eff_begin_dt
eff_end_dt
role type
primary party indicator

Now when the role changes say on 2004-01-05, a new version for that relationship is created by setting the eff_end_dt of the existing record to 2004-01-05 and creating a new version with eff_begin_dt as '2004-01-05' and eff_end_dt = '9999-12-31'.
Now when a relationship expires on '2004-02-10', we set the eff_end_dt of the last version to '2004-02-10'.
So we are using the eff_end_dt to expire relationships and also to expire a version of the relationship. This is causing a problem for we cannot tell the difference between relationship expiration and version expiration without a look ahead algorithm (performance issue) to see if an unexpired version of a relationship exists.

Do you think adding an other date column like expiration date and versioning based on that will help.

Would much appreciate it if you can share some ideas, if you've come across this scenario.

Thanks!!!

 

[dinzana]

How about creating a column / flag that will indicate if a relationship is active or not.

To show current versions of active relationships you can run a query that will have a where clause that is something like this...

where eff_end_dt = '9999-12-31'
and
active_flag = 'Y'

I hope that this gets the creative juices flowing for you.

dinzana

 

[AshishVinny]

Let me get back to u. I have the perfect solution for this. As I have implemented a EDW with the same problem.

 

[AshishVinny]

Krishnan,

I believe u have two kind of changes to handle here.
1. Record updations - Time Variancy Problem
2. Business Relationship Expiration - Business Effectivity Problem.

TIME VARIANCY SOLUTION
--------------------------------
Keep 3 columns : DW_OPEN_DT, DW_CLOSE_DT, DW_CURRENT_IND
------
For INSERT cases
----------------
record (DW_OPEN_DT = Create Date, DW_CLOSE_DT = High Date (12/31/9999) and DW_CURRENT_IND = 'Y'
For UPDATE Cases
----------------
Update the old latest record as
DW_CLOSE_DT = SYS_DATE - 1 second, DW_CURRENT_IND = 'N'
and Insert the new version record as teh INSERT CASE as explained earlier.
For DELETE Cases
----------------
Update the latest record as
DW_CLOSE_DT = SYS_DATE - 1 second

BUSINESS EFFECTIVITY SOLUTION
-----------------------------
This could be adopted as generic approach for all similar issues where Business would drive the effectivity of the record through the dates.
Keep through 3 columns again:
REC_EFF_DT, REC_CNCL_DT, DW_BUSINESS_EFFECTIVE_IND
During the Inserts/Updates REC_EFF_DT and REC_CNCL_DT would represent the expiration dates. and DW_BUSINESS_EFFECTIVE_IND = 'Y' if record is still open (DW_CLOSE_DT = High Date ) and SYSDATE between REC_EFF_DT and REC_CNCL_DT otherwise DW_BUSINESS_EFFECTIVE_IND = 'N'

The turn on/off process of DW_BUSINESS_EFFECTIVE_IND should be a automatic daily process to be run every day during the start of the business hours.


Let me know if u need any explanations.....
Cheers...............
Ashish.