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
Reusing a Chached Lookup component
Elvia
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
GLM
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.
karthikeyan.A
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.
Sanjy
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.
Access Denied
Thiru,
Your solution works for my case. Thanks you very much for the excellent solution.
Regards,
Gopi
MaHMouD 2006
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.
Chris2006
Michael ,
Thank you for providing information.
Regards,
Gopi