Appending data build in DM

[gatorfe]

We have a new build we created to append data in DM 10.2.1.  What is happening though is that it is creating duplicates in sql when we run the batch job that calls the build.  There are two builds setup.  The 1st one truncates the data and pushes it to a staging sql table.  The 2nd one appends the data and pushes the data to a final delivery sql table.  Keys are setup on the 2nd build.  We are not sure what we are missing.  Are there any common settings to check w/o going to much more into detail on what we have created?  Thanks in advance for any feedback.

[MFGF]

We have a new build we created to append data in DM 10.2.1.  What is happening though is that it is creating duplicates in sql when we run the batch job that calls the build.  There are two builds setup.  The 1st one truncates the data and pushes it to a staging sql table.  The 2nd one appends the data and pushes the data to a final delivery sql table.  Keys are setup on the 2nd build.  We are not sure what we are missing.  Are there any common settings to check w/o going to much more into detail on what we have created?  Thanks in advance for any feedback.

I'm not sure I'm following correctly... are you ending up with duplicate rows in your staging table or in your final delivery table?

I think you're saying:

The first build reads from a table or some tables and writes those rows to a staging table - truncating the staging table each time before it delivers the rows?
The second build reads the rows from the staging table and appends them to a final delivery table?

We need to understand where the duplicates are (staging table or final delivery table or both) to know which build to focus on. Can you advise?

Cheers!

MF.

[gatorfe]

thank you for pointing that out. the duplicates are happening only in the delivery table.  no dups show up int he staging table.

[MFGF]

thank you for pointing that out. the duplicates are happening only in the delivery table.  no dups show up int he staging table.

Ok - that makes sense. When you use the Append refresh type in a build, it will append the records being delivered to the target table without performing any sort of check to see if they already exist. As I see it you have two options:

1. Modify the second build to use Update/Insert as the refresh type. It will then update any existing rows in the target table with corresponding rows in the build, and if there is no row to update it will insert the row into the table. You'd need to specify the values that together determine uniqueness (ie the composite key) of the target table, and these values would be used to determine whether there is a row to update or whether to perform an insert.
2. Modify the second build so that it checks whether there is a corresponding row in the target table before attempting to append. You could perhaps do this by creating a lookup based on the unique key value(s) in the target table, validate against this (allowing unmatched members), set a flag where the row is unmatched, then deliver only the unmatched rows to the table (using a filter on the delivery module).

One thing to check is whether the rows are entire duplicates, or are the measure / attribute values different but the key values match? This might dictate which approach you want to opt for...

Cheers!

MF.