Combination lookup-update "Lookup Functionally", does it really exits?

[jmelo]

Hello everybody,

I'm new on this forum but i'm a Kettle fan.

I am working on a Data Warehouse project here in Brazil, and using the step "Combination lookup-update" to load some dimension tables.

Then i figured out that I could use this step to lookup the surrogate key of a dim table and bring it to a fact table, or even get the key of a "demography dim table" and bring the key to a "person dim table". But, i did not found how to use the "Lookup Functionality" of this step. (This functionality is present on the step "Dimension Lookup-Update" unchecking the check-box named "Update the dimension?")

I just could not find the check-box that give this functionality to the step. And the more strange is that this check-box is present on the wiki page of this step on the link:http://wiki.pentaho.com/display/EAI/...+lookup-update

I tried to find this check-box, with any success, on the following kettle versions: 3.0.4, 3.2.0 and 4.0.0, all GA versions.

Could someone help me on that?

Thanks already, and sorry if my english is not the best

[slawomir.chodnicki]

Hi there,

the Combination lookup/update step works like this:

First it looks up the dimension fields from the stream

- If it finds a dimension entry, it puts the key field to the row stream
- If it does not find the dimension entry, it creates one and puts the (fresh) key field to the row stream

If you check the "Remove lookup fields?" checkbox it also removes the dimension lookup fields from the stream (but it always leaves the key field in place)

So you can *always* expect the step to return a key. If it finds the lookup fields you are giving it, you get an existing key. If you send in something new, it will create an entry and return the new key. So if you know for a fact that the lookup fields are already there you will be doing lookups only. If you are not sure, it might extend your dimension table.

I hope the explanation makes sense

Cheers

Slawo

[gutlez]

jmelo,

I don't see the checkbox in 4.0RC1 (I should really grab 4.0GA!)

My reaction would be to file a Jira on it.

[MattCasters]

The check-box is absolutely there in 4.0-RC1 and 4.0-GA.

[jmelo]

Hello Matt,

This must be a strange thing, but I downloaded the Kettle 4.0 GA in the url from sourceforge: http://sourceforge.net/projects/pent.../4.0.0-stable/,
and i did unzip the file and ran the kettle. But the check-box "Update the dimension?" is not there, on the interface of the Combination lookup-update step. That is very strange o.O. i'm posting one snapshot of my screen (hosted on ImageShack):http://img197.imageshack.us/i/combin...kupupdate.jpg/

[jmelo]

Slawo,

Thank you for your response, that was a good explanation, but what i want is not to insert rows into the dim table, i want to lookup the values in the dimension, if the compare matches, i want to get the surrogate key, and remove the fields that were part of the lookup (enabling the check-box "Remove lookup fields").

[slawomir.chodnicki]

My copy of 4.0 GA on mac os also does not have the checkbox in the "Combination lookup/update" step.
"Dimension lookup/update" has the checkbox, but it had that in 3.2. too...

I just checked the 4.0. GA sources for the "Combination lookup/update" step and it looks to me like the the step always performs an insert, when the lookup is not successful. I also don't see the checkbox in the dialog sources....

Cheers

Slawo

[slawomir.chodnicki]

jmelo,

if you only need a regular lookup only, maybe the regular "Database lookup" step will work for you?

Cheers

Slawo

[jmelo]

Slawo,

yeah, i'm using the regular "Database lookup" step, but what i want is do the lookup, and automatically remove the lookup fields. Then, I will not need to remove that fields in a "Select Values" step (what i guess could slow down the transformation, by reconstruction the entire row again), and if the step "Combination lookup-update" gives this functionality, why not use it? But the problem is that this functionality (that is very useful on my point of view) is missing in my kettle .

[slawomir.chodnicki]

Hi jmelo,

I see, you are hoping for a performance boost :-)

Then, I will not need to remove that fields in a "Select Values" step (what i guess could slow down the transformation, by reconstruction the entire row again)

I think this is what I'd do in this context, as well. The performance will not degrade much in my experience. The "Select Values" step usually performs quite well and is not the bottleneck of a transformation.

You may also be able to speed things up by launching multiple instances of the "select values" step. This post explains how and when this can be useful:

http://type-exit.org/adventures-with...ansformations/

Gotta go back to the soccer games... ;-)

Cheers

Slawo