How to look up technical key of dimension using its natural key?

Question

According to the Wiki:

• "The Dimension Lookup/Update step allows you to implement Ralph Kimball's slowly changing dimension for both types: Type I (update) and Type II (insert) ..."
• "To do the lookup it uses not only the specified natural keys (with an "equals" condition) but also the specified "Stream datefield" (see below)."
• "As a result of the lookup or update operation of this step type, a field is added to the stream containing the technical key of the dimension."

So if I understand that correctly, it should be possible to have the "Dimension Lookup/Update" step lookup a dimensions technical/surrogate key using a natural key. In case no entry yet exists the step could also be configured to add the requested natural key to the dimension table using a unique technical key. But for now I would like to only use the lookup functionality - no update and no insert.

Here's my setup:

This is my dimension table (SCD Type 1) named "dims":

The transformation looks as follows:

But if I run this in Preview mode I get:

What I would like to see is actually the values of id (1,2,3) next to the natural keys (a,b,c)

What am I doing wrong here?

Effectively I could achieve this using a join step - but I would like to use the advanced dimension handling functionality after I got this working.

Kind regards

Raffael

---

http://www.joyofdata.de/blog/a-stackoverflow-but-for-business-intelligence/

Answer 1

This step expects a table with 3 more attributes:

• start_date (date)
• end_date (date)
• version (int)

Check that your date settings in the „Lookup / Update“ step matches your data. Check the version field too.

Below an example:

Table:

Setting for the „Dimension Lookup / Update“ step:

Preview table (the id's that match the date are returned)