In my recent blog article about the table comparison process, I demonstrated how table comparison proceeds data, when the option Input contains duplicate keys is activated. In this one, I’d like to show you what happens when this option is deactivated. Are there any differences? Or can we just ignore this option? Let’s find out.
We can use the same data flow as in part one, consisting of flat file as a source, a query transformation, a table comparison and a target table.
We can deactivate the option Input contains duplicate keys. Everything else remains the same.
Just a reminder: The flat file consists of two columns: CustomerID (which is the BusinessKey) and Product.
Now, since the option is deactivated, the input will not be filtered before loading it to the target table. Let’s execute the job and see what happens.
This is the output we get. Every single input record.
Now, why is this happening? By deactivating the option about duplicate keys, BODS examines if each record (row-by-row select) exists in the target table. BUT: it does not transfer the data to it, until the whole input set is scanned. This means, that if the target table is empty before the job execution, every record will be added to it. Why? Because BODS compares them to the existing data amount of the target table, which is currently empty. We could add thousands of CustomerID=100 or CustomerID=101, the result would still be the same. Since there is no CustomerID=100 or CustomerID=101 in the target table, each one of the records will be added.
Now, let’s check out what happens if the target table is not empty. In this case, if the input set consists of records with same CustomerIDs as the ones in the target table, then the product column in the target table will be updated based on the values of the input set. For better understanding, let’s just have a closer look to the procedural method of table comparison. Let’s assume, that it consists of a single record.
If we execute the job, this is the result we get.
Since there is no CustomerID=100 in the target table, every record with this particular one will be added. There is a record with the CustomerID=101 in the target table though. A product update takes place for this record.
And that’s how this output is generated.
For more information about SAP Data Services, please visit https://bimanu.de/leistungen-business-intelligence/sap-data-services-beratung/.
If you wish to find out more about SAP Analytics products we invite you to take advantage of our free Workshop.
We look forward to exchange with you.
23.02.2019, Anastasios Koukothakis
We accompany you from the beginning to the end – see for yourselves – bimanu references.