In this article you will learn:
- About Int4 IFTT Database Comparison Rules
- All the functional and technical details explained precisely
- How to regression test SAP end-to-end scenario with 100% certainty
- How to configure step-by-step the Int4 IFTT Database Comparison Rules
One of the standard and most important Int4 IFTT functionalities in terms of SAP end to end testing is the database comparison process.
In this blog, I would like to explain the functional and technical details, to clarify what are the Int4 IFTT database comparison rules and how to use them.
What is the use case of database comparison rules?
So, let’s start with the explanation of the situation when the database comparison rules are essential.
We have a B2B integration scenario of a sales order document sent from a business partner to our SAP S/4 HANA system. It can be a scenario like in the graphic below.
The inbound message type to SAP S/4 HANA, can be any type, like IDoc, proxy, Web service, or any other because in this scenario we are going to focus on the actual posted data in the SAP S/4 HANA back-end.
With Int4 IFTT you are able to regression test not only the inbound/outbound related message but also the posted data in the back-end. By that means, we can validate business documents by their actual Database entries. This way we have a 100% certainty that we compare the actual posted data.
What types of documents are supported?
The validation is being performed on the actual posted data in the SAP database tables. You can compare any SAP document by comparing its database entries. It is possible to test even custom documents in custom tables!
How do the database comparison rules work?
During full SAP end-to-end testing of an application interface, the final document that was created in SAP back-end will be validated against the reference one stored during the test case creation. From the technical point of view, the validation is performed through the comparison of the selected database tables and table fields of both documents: the reference and the new one created during the test case execution. To achieve this, we create a business document’s database model representation and choose the tables, relations, and fields we want to be compared. Optionally we can configure rules for matching original and new document lines and join conditions in case of more complex object models.
Int4 IFTT Database Comparison Rules Configuration
Database Comparison Rules are defined per database table and each one will be considered as a separate step. The database comparison rules combine the following four configuration elements:
- Selection Criteria – used to retrieve relevant records from the specified tables. For each table it must be specified how the rows should be selected based on values stored in variables or retrieved from records from preceding steps. Technically the configuration is used to prepare WHERE conditions for database queries. The queries that will be generated will be used to retrieve reference and newly created documents.
- Join Table – optional setup used to configure SQL join conditions in order to filter the selected data, for the current step table configuration. You can validate the table entries of the join table, but it must be declared in a parent step, or a proper predefined database view should be used in the current step instead of the database table. The joined table might be also used for selection criteria as well as for additional fields to compare.
- Matching Criteria – optional configuration for tables that contain multiple items (like sales order items). When reference and new records will be compared, they will be first matched by these specified fields (matching criteria). If no matching criteria are defined, items are compared based on index (first position from the original document with first position from new document etc.). It can lead to false-negative results in case positions in new documents are posted in a different order.
- Comparison Details – used to determine the fields that will be compared and the type of rule in case of differences. The comparison details need to be specified based on table and field level. More technical details are provided in the Step by Step Configuration section.
Following the consequent steps, you will be able to achieve the needed configuration. The subsequent example screenshots correspond to rules for a Sales Order document validation. The database tables used in the examples and are correlated with Sales Orders are the following:
- VBAK – Sale Document: Header Data
- VBAP – Sales Document: Item Data Table
- VBKD – Sales Document: Business Data
Step by Step Configuration
For each of the configuration, elements let me provide you with some technical details and an example.
Database comparison rules configuration is performed per Int4 IFTT configuration object, so we need to create a new Int4 IFTT configuration object or edit a corresponding one.
It is recommended to create one, common object definition for all similar scenarios and then re-use it as Reference Object (Reference Object for DB Rules)
For more information about Int4 IFTT configuration objects, please follow the link.
Now open edit mode of the Int4 IFTT Automation Object and go to DATABASE Comparison rule node.
Example 1 – SALES_ORDER_01 Int4 IFTT Automation Object
First, you need to provide the database tables that should be compared.
The tables are processed in sequence based on Step number. Each step has the following parameters.
After populating the table names with the additional parameters, select the table you want to configure and double click on Selection Criteria.
Example 2 – DB Comparison Rules, table definition
Selection criteria consist of the following parameters.
In our example, we want to fetch the data from table VBAK based on BSTNK (PO Number) field. We will use PO_NUMBER variable which will contain the actual test case PO number. In order to learn more about Int4 IFTT variable processing, please follow the link.
Example 3 Selection Criteria, Variable Reference
This entry will generate the following SQL statement:
SELECT * FROM VBAK WHERE BSTNK = PO_ORDER_NUMBER.
Example 4 – Selection Criteria, DB Parent ID Reference
Example 4 shows you how to fetch the data from a table based on the actual database values of the parent step.
This entry will generate the following SQL statement:
SELECT * FROM VBAP where VBELN = (Sales order number fetched from step 1, example 3. Technically the value that was stored in the VBAK~VBELN field).
If the conditions are built on the queries that use JOIN then the field name must be specified with the table name, like for example VBKD~BSTKD.
Next, continue with the Join Tables parameters. In this step, the only requirement is to provide the additional Join Tables and Join Field Names.
- Add table name, save and mark the added row
- Double click on Join Conditions
- Add the fields corresponding to the initial and joined tables
Example 5 – Join Tables
We provide a Join Table the VBKD in order to validate against additional Business Data information.
Example 6 – Join Table, Join Conditions
Example 5 and 6 together are generating the following SQL statement:
SELECT * FROM VBAK JOIN VBKD ON VBAK~VBELN = VBKD~VBELN.
One more optional configuration element is the Matching Criteria. Let me provide you at the beginning with an example in order to make clear how Matching Criteria works.
Example 7 – Matching Criteria
Example 7 represents a configuration matching Sales Order lines based on purchase item number (POSNR).
Practically, let’s say that we have a situation where the reference document contains 3 items and the newly created document contains only 2 items (due to some error).
If matching criteria will be set to field POSNR:
If matching criteria is not set:
Last Configuration Element in Database Comparison Rules is the Comparison Details.
For each step and corresponding table from Example 2, the fields need to be specified.
- Select row with a particular step.
- Double click on Comparison Details.
- Define a list of fields and the treatment when the difference is found. Please define for each database field the following attributes:
Example 8 – Comparison Details
In the example above you can see the comparison details populated with the additional validation logic regarding table VBAK.
Following those four steps, you will be able to configure even the most demanding database comparison rules.
Of course, in the end, you can create a new proper test case and run it, hopefully successfully!
Int4 IFTT Database Comparison Rules will help you to validate SAP back-end posted data 100% successfully.
In this article, you learned functional and technical details about the Database Comparison Rules and you should be able to create such a configuration on your own, following the given examples.