Decision Stream-Rejecting records as Dups that aren't

[donapesc]

I have an issue with a Decision Stream job rejecting records as duplicates which aren't really duplicates.

We have a Trans_Master table that is delivered via a DIM build that generates an SO_DOC_SID field.

The fact build is to deliver another table - TRANS_CHILD - that could have multiple records that relate back to each record on the Trans_Master table. For each TRANS_MASTER record, there could be one or more TRANS_CHILD table records.

The SQL table definition for TRANS_CHILD has CHILD_SO_DOC_SID field and CHILD_SEQ_NO field defined as a composite key. There are several more fields on the TRANS_CHILD table -- one of which is ITEM_NBR.
*** the ITEM_NBR field is >> NOT << part of the key in SQL or Decision Stream.

In Decision Stream, the job to deliver the TRANS_MASTER table runs first, correctly generating SO_DOC_SID field values via a DIM build.
Then, the job to deliver the TRANS_CHILD is run. In the TRANSFORMATION section, the CHILD_SO_DOC_SID is filled in via a DIM LOOKUP. The CHILD_SEQ_NO field comes directly from the source table.

This works for all data where the ITEM_NBR is different.

When I have the following situation:
CHILD_SO_DOC_SID CHILD_SEQ_NO ITEM_NBR
12345 1.0 B23009
12345 2.0 M50660
12345 3.0 B23009

Decision Stream will successfully load the 1st 2 records into my FACT table, but then it REJECTS the CHILD_SEQ_NO 3.0 record as a duplicate.

In SQL, the key field "12345-1.0" is NOT a duplicate of "12345-3.0" -- The SQL table would accept this as a unique key. There is a single digit that is different that makes the key unique. It accepts the "12345-2.0" as not a duplicate.

I can not figure out WHY Decision Stream rejects THIS RECORD ??

 

[donapesc]

I got the answer from Tech Support --
Decision Stream uses any fields defined as DIMENSION in the TRANSFORMATION block of the Fact build to determine duplicate, regardless of the KEY definition of the table.

In my case, the CHILD_SO_DOC_SID and the ITEM_NUMBER were DIM look-ups, resulting in DUPLICATION.

I had to Convert the CHILD_SEQ_NO to a DIMENSION attribute in the Transformation section. Now it uses all 3 pieces of data to check for duplication and delivers all my data.

I do not think I would have ever figured that out from the DS documentation.