lookup problem - urgent ?

[vikind]

I have a target table with Member_key and Month field, I want to update the other fields in the target table when both the member_key and the Month match.
if there is no unique combination of member_key and month existing in the target then i want to insert a new record into it.
my target table MEM_COST should be something like

MEMBER_KEY | Month | Cost | ...
100 3 500
110 3 600
100 4 800

and if i get a new source record with member_key as 100 for month 3 with Cost 300 then the target table's record for the Month 3 and Member_key 100 should be updated with a new value of 300.

I tried to use a lookup with both the conditions for member_key(source) = member_key(target) AND month(source) = month(target) but it inserts duplicates for the same month..instead i shud have a unique combination of a member_key for a month..
ie.. member 100 shud only appear once for April . he can appear again only if the month is May or any other month..

thanks for ur help !!

 

[dinzana]

Vikind,

It sounds like you need to implement an Update Strategy with a condition that looks something like....

if(MEMBER_KEY isnull and MONTH isnull, DD_INSERT, DD_UPDATE)



The above MEMBER_KEY and MONTH are the result of the lookup, they should be connected to the Update Strategy with the COST from the source connected to it as well.

Then connect all three to the target table.

I hope that this helps,

dinzana

 

[vikind]

dinzana thanx for ur help..

i tried that

however i still am not able to update the record in my target table based on my member_key and month. I guess it is because my target table has no primary or foreign keys. There are few things i needed to clarify..which might help solve my problem.

as i keep getting duplicate member_key and month in my target table

1. Can i do an update on my target table which has no primary or forge in key ?

2. If i use a dynamic lookup with update strategy does it require a primary key in target table ?

I also tried to check the update else insert option at session level and source as data driven but still it gives duplicates.

 

[blom0344]

1. Update on tables without primary key are NOT possible.
2. Adding a primary key to the target will lead to rows failed due to primary key violation if you do not change the mapping structure in your case.
3. Failed rows will either cause wrong data to be loaded, but in any case will be a performance-drain, cause the engine will need to use resources to write all the logging about it to the log files!

T. Blom
Information analyst
tbl@shimano-eu.com

 

[dinzana]

blom0344 has hit the nail right on the head!

1. In order for the Update Strategy to work properly, your target table should have a composite primary key of (MEMBER_KEY and MONTH).

2. Although, with this composite PK in place, your mapping / session should return 0 failed rows.

The LU / Update Strategy, in a nutshell does this....

a) 'Find out if MEMBER_KEY 100, MONTH 3 exists in the target and if so, go to that row and Update the cost column'

and

b) 'IF MEMBER_KEY 100, MONTH 3 does not exist in the target, then insert MEMBER_KEY 100, MONTH 3 with the cost value'

I hope that this helps,

dinzana

 

[vikind]

hi dinzana and Blom,

thanx for your valuable feedback.. i will probably include the composite key for my member_key and month in
the target table.

I have one more question based on the same lines..i have to do updates on a FACT table say CLAIMFACT
and the CLAIMFACT table does not have a primary key as per the star schema design. however it does have some
forigen keys for different dimesion tables.

now if i want to do an update on the CLAIMFACT table based on the CLAIM_ID which is not a primary or a foriegn
key. is it possible to update the CLAIMFACT table based on CLAIM_ID and update other columns..

CLAIMFACT table:
---------------

CLAIM_ID | MEMBER_KEY | DISCHDATE_KEY(fk) | LENGTHOFSTAY |....

thanks and appreciate your help !

 

[blom0344]

I repeat:

"1. Update on tables without primary key are NOT possible."

T. Blom
Information analyst
tbl@shimano-eu.com

 

[vikind]

thanks i got the point. will incorporate primary key
in the fact table..

appreciate it!