With the new release of Solution Manager 7.2, a new feature called Expert Filters was introduced in the Cross-Database Comparison (CDC) application. Expert Filters introduce new options for filtering including filter sets and simple aggregations.
Motivation
The CDC application is used to compare data sources with a complex structure or hierarchy across different systems. By doing so, you check whether the data between source and target system is consistent, for example, whether updates in the source system have been correctly replicated to the target system. Examples of complex data sources are sales orders with several items or a master data records distributed across several tables. You can read more about CDC here.
In customer engagements, several additional requirements regarding filtering functionality in CDC arose. As an example, after the first run of a new CDC comparison, the customer recognized combinations of entries in the source systems which should not be part of the comparison. Up until now, it was not possible to exclude multiple combinations of field values from the selection in the source system. Filter sets now introduced with Expert Filters will fulfill this requirement.
Prerequisites
Expert Filters are available for source types ABAP (generated ABAP extractor function module), ABDY (generic ABAP extractor function module), ADBC (remote database connection) and HANA (HANA database). Solution Manager 7.2 SP03 is recommended.
Example
John is a business manager who would like to check the system landscape for data consistency. In detail, he would like to check if the Business Partners between ERP and CRM are replicated. In the last weeks, there were several consistency issues related to a specific set of Business Partners.
After checking the consistency issues, Jane from the IT department identifies that two sets should be checked: Business Partners with type Person and Partner Type 0001 and Business Partners with type Organization and Partner Type 0002. She starts setting up the CDC comparison in Solution Manager.
Jane decides to use the generic ABAP extractor (ABDY) as both source systems ERP and CRM are ABAP-based.
After entering the connection details she sets up the data model. The tables CDC_DEMO_BUT000 and CDC_DEMO_KNA1 will be used. The data contained in fields CDC_DEMO_BUT000~PARTNER and CDC_DEMO_KNA1~KUNNR should be compared to see whether every business partner exists in the other system. Furthermore Jane adds the fields TYPE and KIND in the CRM source system to fulfill the filter requirement.
Now Jane marks both fields as Variable Filters of type “STRING for character-like”. After marking both fields as variable filters, Jane sees the known filter maintenance in the comparison above the data model.
:!: The Expert Filters functionality is only available for Variable Filters, not for Fixed Filters.
In order to maintain Expert Filters, she will switch the user interface by pressing button “Expert Mode”.
The new maintenance for Expert Filters is based on the original filter maintenance. You have several options to define the filter. The filter value can now be selected with a value help which access the source system directly and returns the values for the selected filter field.
In order to define the two sets of values for the Business Partners, Jane will use the Filter Set functionality introduced with Expert Filters.
In the Expert Filters maintenance, a new column was introduced called “Set”. Using this column, you can define filter sets which were already mentioned in the motivation of this blog. Filter sets can be used to group individual filters. Depending on the grouping, the individual filters will be evaluated differently during the comparison run.
In the simple filters mode, all filters are combined using the logical OR operator. In case of all filters being defined as exclusions (f.e. filter is defined as include (I) not equal (NE)), the filters will be combined using the logical AND operator. With the Expert Filters, the individual filters of one filter set are combined using the logical AND operator. The filter sets will be combined using the logical OR operator. In case of exclusion in all filters defined, the filter sets will be combined using the logical AND operator.
Jane will define two Filters Sets (0001 and 0002) according to the requirement she initially created.
During the comparison run, the maintained filter logic will select Business Partners from CRM source system which are of type Person (“1”) and have Partner Type 0001. Furthermore the logic selects Business Partners of type Organization (“2”) and Partner Type 0002. Business Partners with type Person and Partner Type 0002 will not be selected.
When running the comparison and checking the result, Jane recognizes that the correct Business Partners are selected. She hands over the comparison to John. John now uses the comparison to monitor the data consistency.
In addition, John has another use case for a comparison. He would like to monitor the replication of Contracts between CRM and ERP. The Contracts are stored with a version in CRM and only the latest version is replicated to ERP. Therefore the comparison should compare the latest version from CRM to the Contract stored in ERP. Furthermore only specific types of Contracts are replicated from CRM to ERP.
With this requirement, Jane starts to setup the comparison. Again, she uses Expert Filters and defines two filter sets. Furthermore in order to model the requirement to only compare the latest version, Jane uses the aggregate functionality introduced with Expert Filters.
As an additional feature, the aggregates maximum and minimum can be used as filter values in Expert Mode. In order to use an aggregate, the keyword $MAX (or $MIN) has to be used in the filter value. During the comparison run, the aggregate value for the individual filter will be calculated and afterwards used in the extraction from the source system.
The aggregates will be selected depending on the key. Please find the example below to demonstrate the logic.
Key Value
1
1
1000
2
1
2000
3
1
300
4
2
500
5
2
2500
6
2
3000
Using the aggregate maximum ($MAX) on field Value in a filter set together with Key equal to 1 will return line 2. When using the minimum aggregate ($MIN) and Key 2, the line 4 will be returned.
In case you require more sophisticated aggregations in you comparison, I would recommend to read the upcoming blog about the Aggregates functionality introduced with Solution Manager 7.2.
Jane will use the maximum aggregate ($MAX) together with the Contract type in a filter set. This will restrict the comparison on CRM side to only select the latest version of the Contracts and then compare them to ERP.
Links
For more information regarding Data Consistency Management, please use the following links to reach the DCM wiki and the DCM blogs.
Data Consistency Management Wiki
Data Consistency Management Blog
- SAP Managed Tags:
