An alternative join mapping

oldforum

• July 16, 2007 00:00

• Answered

Follow

Hello,

First let me congratulate on this excellent project; I can truly see this being an enterprise leading open source project.

My query is would it be possible (difficult?) to have an XML “join definition” for join components as I have had a few issue by matching up source[0] & source[n] to the target[0]. The issues have been mainly being with the mapping of 100 columns in each of the input nodes (relational db tables).

My thoughts where that you could also introduce simple sub-transformations within the defined join

For example;

<JOIN MAPPING>
<mapping one_to_one source=”source1.address” target=”target1.fullAddress”/>
<mapping transform source=”transform_concat(“source1.firstName”,” “, source1.lastName)” target=target.fullName” />
</JOIN MAPPING>

Could this also be an approach for cloverDB field mapping to the logical output database field names?


A short term simpler way forward to the issue would be to have the ability to lookup the joins values & target field on the metadata defined name and not on the position within the metadata file. Would that be possible?

Thanks in advance for any advice/guidance.

Many Regards,

Ian.

• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  Hello Ian !
  
  The better/easier way transformations development/generation is definitely on our to-do list.
  We would like to approach it from two sides:
  1.) have some wizard in GUI which can help in specifying the mapping between source and target.
  
  2.) extend the current "language" used in EXT_FILTER component so it can support defining mappings.
  
  Your suggestion with whaving some XML based definition of mapping from input records to output records is also in discussion.
  
  The plan is to have it developed within three months - sometimes in february 2006.
  
  As for the names versus positions - every method with accesses field's value through field's index within data record has sibling which accepts field's name insted of index.
  
  See this example:
  *************************************
  public class reformatJoinTest extends DataRecordTransform{
  
  public boolean transform(DataRecord[] source, DataRecord[] target){
  
  target[0].getField(0).setValue(source[0].getField(0).getValue());
  target[0].getField(1).setValue(source[0].getField(1).getValue());
  target[0].getField(2).setValue(source[0].getField(2).getValue());
  if (source[1]!=null){
  target[0].getField(3).setValue(source[1].getField(0).getValue());
  target[0].getField(4).setValue(source[1].getField(1).getValue());
  }
  return true;
  }
  }
  *************************************
  versus
  
  *************************************
  public class reformatJoinTest extends DataRecordTransform{
  
  public boolean transform(DataRecord[] source, DataRecord[] target){
  
  target[0].getField("OrderID").setValue(source[0].getField("OrderID").getValue());
  target[0].getField("CustomerID").setValue(source[0].getField("CustomerID").getValue());
  target[0].getField("EmployeeID").setValue(source[0].getField("EmployeeID").getValue());
  if (source[1]!=null){
  target[0].getField("EmployeeID2").setValue(source[1].getField("Employees").getValue());
  target[0].getField("LastName").setValue(source[1].getField("LastName").getValue());
  }
  return true;
  }
  }
  *************************************
  
  The second version is slightly slower when executed as it involves hashtable which translates field names to field ordinal numbers.
  
  We will try to sneak into simple improvement into
  next release (which is almost ready to be released) which will take field names and translate them into field numbers - to make the
  execution faster. But this will be only temporal
  remedy.
  
  Best regards,
  
  David

Please sign in to leave a comment.