0

Migration Strategy feedback request

Charles Kelsoe

February 24, 2012 17:54

None

Follow

I am currently using the community version of Centerprise with an eye towards the professional version. I could use some practical direction on what I am trying to do in the community version and a better understanding how the professional version would make this process simpler.

What is the smartest strategy to convert the following tables? There are
identifiers that will enable me to keep track of which child record belongs
to the parent record.

*The Source Tables*

ROH - The header table
ROITEM - The child table with 0 to may records relating to the ROH
table.
There is a field value in this table that will determine
which target table the data will go in

 

*The Target Tables*

ROS - The header table
    JOBS - Another sub header table - there may be 0 to many records relating back to the ROS table
          JOBFEES - A child table with 0 to many records relating to the JOBS table
          JOBLABOR - A child table with 0 to many records relating to the JOBS table
          JOBPARTS - A child table with 0 to many records relating to the JOBS table
         JOBSUBLETS - A child table with 0 to many records relating to the JOBS table

 

I know I can create a DataFlow for each table but I would like to combine
it into one process as much as possible. I have the Parent Accounts and
Customers tables in another DataFlow working as expected thanks to your
TimeStamp function.

I also have a VEHICLES table that relates back to the ROS, JOBS, and the
JOBxx tables. Yeah poor DB design by someone else.

Thanks,
Charles

• Facebook
• Twitter
• LinkedIn

12 comments

• 0
  
  TechSup1 February 25, 2012 01:27

  Are these database tables or this data is in a file. If this is a file, you can use hierarchical delimited file option to accomplish this. If this is a fixed length file, you can use report mining feature to read data from it. If these are database tables, you can use either relational join transformation or data model source to read data from these.

• 0
  
  Charles Kelsoe February 25, 2012 01:35

  I believe the data model source is not available to me in the community edition. I would have to wait until I can acquire the professional version to use that.

  They are all in a database. So with the join transformation I could link all of the tables according to their relationship and achieve the desired conversion of remapping the related tables to a new related table structure in another database? 

  Would that also allow me to take a series of related records in various tables and transform the record ID to a new ID? In my data I have a record with an ID of 0 that I want to remap to a new number such as 9999.

  And all of that is can be done in the community version?

• 0
  
  Charles Kelsoe February 25, 2012 04:54

  I have attached a simple diagram to illustrate the source and target data. I know I can do each target table one at a time. I am interested in doing all tables at the same time if that is possible. I am thinking it is with the Join mentioned above but I am not sure. Where do I place the joins? 

  
  
  
  schemas.png
• 0
  
  Charles Kelsoe February 25, 2012 14:06

  Ok - I found the Tree Join Transformation video on YouTube. I built out my tree join by adding the related tables to the tree in the hierarchical layout like the video showed. However, when I go to the  properties I do not have the ability to select the join type. When I try to preview I get an error that states "TreeJoin1 : TreeJoin must be linked to two transformations. This join is linked to <4> transformations. How do I resolve this?

• 0
  
  TechSup1 February 25, 2012 18:30

  Use tree join to join two tables and then use another tree join to join the first tree join and the third table and so on...

• 0
  
  Charles Kelsoe February 25, 2012 18:32

  So that is different than the function of the tree join shown in the video. Is the difference between the Community version and the paid versions?

• 0
  
  TechSup1 February 25, 2012 18:41

  Tree join works the same in both editions. You can do all this in a single dataflow. Just join the result of first join to another table and at the end of the chain you will have a hierarchical structure. take a look at this dataflow with northwind tree join sample.

   

   

  
  
  
  NorthwindTreeJoin.df
• 0
  
  Charles Kelsoe February 25, 2012 18:48

  Thanks. I will experiment that in my model once I finish what I have to for a deadline. Now learning how to combine transformations to get the results I need. This is a great program - I which I had found it a year ago. It would have saved countless hours of programming time. Now if I could get the MySQL connection figured out - that is where I need to be but moved the data to SQL Server to get the project done.

• 0
  
  Charles Kelsoe February 25, 2012 19:10

  Nested IF's - I am unable to get this to work: If(INVOICEDATE = "", If(CANCELEDDATE = "",ConversionDate,CANCELEDDATE), INVOICEDATE). Is there a simple way to get this logic to work?

• 0
  
  Mike O'Quinn March 01, 2012 19:34

  Nested IFs should work fine.  What is the error you are getting?  Make sure all of the fields are of the correct type.

• 0
  
  Charles Kelsoe March 01, 2012 19:49

  I cannot recall the exact conditions that failed but this is something similar as an example: If(IsValidSqlDate(COMPLETEDATE)=false, If(IsValidSqlDate(COMPLETEDATE) = false,"In Progress","Completed and Invoiced")). I get "Invalid arguments for function <if>

• 0
  
  Mike O'Quinn March 01, 2012 20:17

  In this example, you're just missing the last parameter.  e.g.:

   

  If(IsValidSqlDate(Now())=false, If(IsValidSqlDate(Now()) = false,"In Progress","Completed and Invoiced"),"Default")

   

  I personally, like to put line breaks in there whenever I'm nesting like this.