Reusing a Chached Lookup component

Is it possible to reuse a Lookup component which is configured with Full chaching?

My requirement is as follows....

A input file have 2 columns called CurrentLocation and PreviousLocation. In the dataflow, values of these two columns needs to be replaced with values from a look up table called "Location".

In my package i have added two LookUp components which replaces values of CurrentLocation and PreviousLocation with the values available in the table "Location". Is there any way to reuse the cache of first lookup component for second column also?

Hi Gopi,

I guess the answer to your question is negative. It would be a really great feature if a tool allows it.

The otherway is, first 'unpivot' ur data, do the lookup and then 'pivot' it again. This way, u need not cache the data 2 times, though the # of lookups is going to be same.

|||

Thiru,

I'm not able to understand the advantage of performing "unpovit" transformation. How am i going to avoid chaching 2 times? Can you please explain in detail?

Regards,

Gopi

|||

Reuse caching would really be a good way to preserve memmory in the etl process. For instance use it in multiple packages that run parallel in a workflow with each other to load (for instance) fact tables.

Please put the reusability of a lookup on the wishlist.

|||

Hi Gopi,

Assume that your data is like this:

Id CurLoc PrevLoc

1 A X

2 A Y

3 B A

And your Location table is:

Location Id

A 1

B 2

X 24

Y 25

When you unpivot your data based on 'Id' field, it will become,

1 A

1 X

2 A

2 Y

3 B

3 A

Do the lookup and u will get result as

1 1

1 24

2 1

2 25

3 2

3 1

Now, if you pivot it with the 'Id' field, it will become,

1 1 24

2 1 25

3 2 1

As I said, still you have to do the lookup 6 times. But your caching it only once.

|||

Gopinath M wrote:

Is it possible to reuse a Lookup component which is configured with Full chaching?

There is no explicit control over it, but Lookup components do try to share the lookup cache, if they use the same datasource and columns for lookup.

You can check if this actually happen in your case by checking the number of queries in the SQL log.

|||

Thiru,

Your solution works for my case. Thanks you very much for the excellent solution.

Regards,

Gopi

|||

Michael ,

Thank you for providing information.

Regards,

Gopi