topic Re: SCD type-2 integrity constraint warning in SAS Data Management

SCD type-2 integrity constraint warning

Babloo — Sun, 13 May 2018 03:35:40 GMT

I've DI job which loads data to the physical table via SCD type-2 transformation. When I ran the job I received an error like

"WARNING: Add/Update failed for data set INS.EMPLOYEE because data value(s) do not comply with integrity constraint PRIM_KEY. (Occurred 38 times.) NOTE: There were 38 observations read from the data set WORK.ETLS_MATCH.
NOTE: 0 observations added."

Business key for my job is "Employee_ID" and there is no duplicates on Employee_ID and generated key is "Employee_RK". Could someone you help me understand the likely cause for this issue?

Re: SCD type-2 integrity constraint warning

Reeza — Sun, 13 May 2018 04:00:11 GMT

Doesn’t this mean the records you’re trying to insert, the 38 employee IDs, are already in the table?

@Babloo wrote:

I've DI job which loads data to the physical table via SCD type-2 transformation. When I ran the job I received an error like

"WARNING: Add/Update failed for data set INS.EMPLOYEE because data value(s) do not comply with integrity constraint PRIM_KEY. (Occurred 38 times.) NOTE: There were 38 observations read from the data set WORK.ETLS_MATCH.
NOTE: 0 observations added."

Business key for my job is "Employee_ID" and there is no duplicates on Employee_ID and generated key is "Employee_RK". Could someone you help me understand the likely cause for this issue?

Re: SCD type-2 integrity constraint warning

LinusH — Sun, 13 May 2018 05:47:18 GMT

First: what is the definition of PRIM_KEY?
Next execute the job with SCD 2 Loader in debug mode, do that you can access the ETLS_MATCH table after execution.

Re: SCD type-2 integrity constraint warning

Babloo — Sun, 13 May 2018 22:45:37 GMT

Could you please help me understand to know how to execute the SCD
transformation with debug mode?

Re: SCD type-2 integrity constraint warning

Patrick — Mon, 14 May 2018 09:30:18 GMT

@Babloo

The primary key on your target table is {Employee_RK, <Valid_From_Dttm>}? Right?

And the Valid_From_Dttm column (or however you call it) contains a SAS Datetime value?

Re: SCD type-2 integrity constraint warning

LinusH — Mon, 14 May 2018 18:19:10 GMT

You need to set the macro variable ETLS_DEBUG to something else than 0. Try that in the pre processing for the job, or add it manually in the generated code.
One option is to set this as default in your server definition. See here for more info:
http://documentation.sas.com/?docsetId=etlug&docsetTarget=p0ygtz7di8z8kpn1ufncd1ajt1pj.htm&docsetVersion=4.903&locale=en#p0lanq85qil108n1sptv9ned3zae