Data Lookup element in Data Movement Model

[katarzynachodubska]

Hi all,

I'm new to PowerDesigner and I cannot quite understand how the Data Lookup Element in Data Movement Models works.

Let's say I have two tables PERSON_TABLE ( Person_Name, Country_ID) and LOOKUP_TABLE(Country_ID, Country Name).

I want to lookup Country name in the lookup_table.

I would expect that I have these two tables as and input to Data Lookup.

I don't get how do I set in Data Lookup that the key value in lookup_table is country_id and that it should be joined with country_id from person_table and country_name should be passed further.

Thanks and best regards,

Kasia

[Former Member · Accepted solution]

Hi Kasia,

The way this is documented, you add a "Data Lookup" object, and a flow.  On the flow into the Data Lookup object, you identify the source data (in this case, the columns coming in from PERSON_TABLE).  Then, inside the definition of the Data Lookup, you indicate (On the General Tab), Mode: Database.  This tells us that the replacement values for the source values comes from a database look-up and not some other source.  Next, on the Script tab, you select the data source that contains the lookup table, and identify the Script that will be used to populate the replacement values.  In your case, you would enter:

SELECT 'Country Name'

FROM 'LOOKUP_TABLE'

WHERE ''COUNTRY_ID' = %_%

Where "%_%" represents the value of the source column that is to be replaced with the result from the look-up table.  This is representing a real look-up scenario (replace values in the source feed with values from some other source, based on the original values in the source feed).  to represent a data projection based on the join of two tables from a source feed, you can use the projection object, and join the tables in the source select, where PowerDesigner will determine the join automatically for you.  That might be a better representation of what you are describing (there are two tables in the source, but what is to be delivered to the target is a result of the join of the two).