Duplicates from ERP going to SQL through DM

[gatorfe]

Hello!  I bring in data from a BPCS ERP System into Data Manager and send it to an SQL warehouse.  Recently we have been having issues with duplicates from the ERP causing jobs in DM to crash as it does not accept duplicates.  Does anyone have any suggestions on how I can make DM take the duplicates it finds and put them in a duplicates table lets say.  That way the job can still run successfully.  Thanks in advance for your time.

[MFGF]

Hi,

The default duplicates handling in a Fact Build for Data Manager is to allow them.  My guess is it is passing the duplicates through the build, and these are then violating unique indexes or constraints in the SQL database you are delivering to, causing the database to trigger an error.

You can change the duplicates handling in any build quite easily - just go to the properties of the build, and on the Input tab, set the behaviour to be either "reject records with duplicate keys" or "merge records with duplicate keys".  The items it recognises as making up key values are the ones defined in the Transformation Model as Dimension elements.

Do be aware of the memory/build time issues this may introduce, though.  When your builds allow duplicate keys, they read and deliver a single row at a time.  When you choose to reject or merge duplicates, your builds must then read all rows into memory and then deliver them all at the end of the build.

Regards,

MF.