Qlik Replicate: Replicating from SAP DB Source changing the precision on Currency Data Type to the Target

Bill_Steinagle — Thu, 14 Sep 2023 07:30:41 GMT

Currency data from SAP appears to replicate incorrectly when source values are compared to the target system. In this scenario, Japanese currency data values shown through the SAP business view for documents derived from the VBAP table were determined to be incorrect when compared to the target (Snowflake) for validation. These values were most typically seen through the SAP document view of the invoice or order where the currency values were already converted to the correct locale and format.

Environment

SAP Business View of currency value (source):

The currency value is shown below is in local currency as 378.000.

Snowflake View of the same currency value (target):

Resolution

Validation at the Data Level:

In the SAP GUI, transaction SE16 (Data Browser) can be used to view rows and columns of table data at or near the “data level”. This approach works for transparent, clustered, and pooled SAP tables.

SAP SE16 Data Browser View: As seen below, while there is still some numeric formatting applied to the value in NETWR, it can be compared correctly with the Snowflake target.

Additional Validation:

Further investigation was able to confirm that the values in Snowflake were correct. This was determined by cross checking the values in the physical database. Note that this approach is only practical when the SAP table in question is transparent.

DB2 database view:

Note that this level of investigation was possible because VBAP is a transparent table:

Conclusion:

Replication from a source to a target is focused on the accuracy of data movement at the “data level”. Validation at the data level is the safest way to compare the source to the target, must be used when equivalent business logic is not available on the target. If business logic is required to correctly format and validate the data, the customer can choose to build some of the logic into the Replication task via Transformation Expressions. Alternatively a separate project outside of Replication may be needed to create or “port” business logic from the source to the target.

article Qlik Replicate: Replicating from SAP DB Source changing the precision on Currency Data Type to the Target in Official Support Articles