Cyclic or linear dependencies and SK generation

[sbass1]

Since a picture is worth 1000 words, here are pics of my job design:

http://docs.google.com/View?docid=dcdxx ... gd=1&hl=en

http://docs.google.com/View?docid=dcdxx ... gd=1&hl=en

I know the problem: I'm trying to write to the same hashed file I'm using for lookup.

My question is, is it possible to use a hashed file for lookup (not loaded into memory), yet update it with new values, so that subsequent lookups get a "hit"?

If my incoming data has the NK's:

A
B
C
B

and the target table is empty, I want the generated SK's to be:

1 A
2 B
3 C
2 B

99% of the time I'll have select distinct so this issue is moot, but I'm still curious whether the above design criteria can be accomplished.

Thanks...

[chulett]

Sure, just use two separate hashed file stages with the transformer, not a single one with two different link types.

[sbass1]

Sure, just use two separate hashed file stages with the transformer, not a single one with two different link types.

Thanks Craig. Just to confirm, the two separate hashed file stages can reference the same physical hashed file?

[chulett]

Yes, not just can but must for the technique to work properly. And as you noted, the caching of the reference lookup needs to either be disabled, or 'locked for update' if you do cache it. I prefer the former, Ray seems to prefer the latter.

[ray.wurlod]

Actually I prefer locked for update and NOT cached. But it depends, primarily on how the cache is established.

[chulett]

Ah... I stand corrected.