Tips to handle Long Processing Time in Electronic Bank Statement Upload
You may have noticed that the upload by batch job of big sized Electronic Bank Statements takes longer than expected. This may impact the time window that the business users have for subsequent reconciliation in FEBAN.
You are using SAP S/4HANA System and the full license for SAP Cash Management, i.e, you do NOT have Cash Scope as “Basic Scope” in Configuration SPRO > Cash and Liquidity > Define Basic Settings.
The purpose of this blog post is to provide tips to be able to assess where to focus efforts on the shortening of processing time, in case of long running background jobs for Electronic Bank Statement upload.
We here provide an example analysis we have performed on an SAP S/4HANA 1809 System, in Software Component S4CORE release SAPK-10306INS4CORE.
Electronic Bank Statements can be uploaded automatically with SAP Transaction FEB_FILE_HANDLING, Bank Statement Processing: Import and Forward (Automatically), which takes the bank files stored in a specific path in SAP Transaction AL11.
The Program performs the file import, table updates, search patterns via search string configuration, interpretation algorithms for Note to Payee, and posting (FB01 or FB05, depending on the configuration of the Posting Rules). This is a background Job.
Also, they can be manually uploaded using FF.5/FF_5 via Program RFEBKA00, which can then run online or in background.
In our example, the file contained 27.513 lines. Note, the number of lines is not the same as the number of records passed to the SE37 Log of FEB_FILE_HANDLING or of RFEBKA00-EL BANK STATEM, or to the number of Memo Records in FEBAN. In our case, we are talking about a file that contains 27.513 lines, which has 5.513 transactions that do on average two (2) FB01 postings each (11.026 FB01 postings, sometimes FB05, depending on the Posting Rule configured). The Job FEB_FILE_HANDLING took around 2 hours to finish.
We first analyzed whether the processing time behaved in a linear way compared to the number of records in the file. It did. The more records in the file the longer the job took to finish processing, in a linear way.
The upload itself without posting took around 3,5 minutes for a file of 27.513 lines. This upload without posting can be achieved by setting in SPRO posting parameter “Only upload” or by running FF.5/FF_5 in batch with the parameter “Do not post”.
During the posting process, SAP standard transactions FB01 or FB05 are called, which took the mentioned 2 hours minus the 3,5 minutes for the upload. Additionally, the part of FQM Flow will be processed, where the biggest contribution to runtime will be the part of the Liquidity Item derivation.
Derivation of Flow Type and Liquidity Item happens in CL_FQM_DERIVE_BSEG => derive () during posting. The call to the FQM within the Bank Statement is called from Function Module OPEN_FI_PERFORM_00002810_E > CL_FQM_ADAPTOR_BS=>PROCESS_ITEM2FQM.
During the 3,5 minutes of the upload process, we ran an ABAP trace ordered by Net time and saw that 29,5% of the time was spent in “CALL FUNCTION PAMA_MATCH” (Pattern Matching). It was invoked 1.539.437 times but was performed in under 1 minute. The number of calls of function PAMA_MATCH depends on the size of the payment notes (Table FEBRE), the so called “Note to Payee”. All those data need to be analyzed by this function. This function is for the Search String functionality as configured in SAP Transaction OTPM > Search String Use. So, one general remark would be to only assign Search Strings in configuration where it is really needed, or to ensure they are really used. But, as we are talking 1 minute in our example, this will not help much to shorten the processing time.
We then evaluated processing time of Bank Statement upload with NO “One Exposure Update”.
This can be switched off in your test system in SE38 > FQM_ACTIVATE > Set to Deactivate for the Company Codes for which you are uploading the Bank Statements. We switched OFF, FI and BS, ‘Financial Accounting’ and ‘Bank Statement’ application sources only. Others we left in.
The Job with the 27.513 lines file, took in our test system 1h 20 min instead of the 2h in production.
So, One Exposure Update (Table FQM_FLOW) took 40 minutes out of the 2 hours for this file size (33%).
To complete the analysis, we evaluated processing time of Bank Statement upload with NO “Liquidity Item Update”.
This can be set in SPRO > Cash and Liquidity > Define Basic Settings and set Cash Scope to “Basic Scope” or, alternatively, by removing he Queries and FLQINFACC configuration in SPRO > Cash and Liquidity > Cash Management > Liquidity Items > Derivation Rules for Liquidity Items > Define Liquidity Item Derivation Settings for Company Code by creating a Function Module to assign a dummy Liquidity Item and by ensuring that the queries and FLQINFACC configurations are not evaluated. We took the first approach.
The Job with the 27.513 lines file, took in our test system 1h 30 min instead of the 2h in production. This Job was run switching ON One Exposure update in Transaction FQM_ACTIVATE, FI and BS (Financial Accounting and Bank Statement Application Sources) for Company Code ES08 and switching OFF Liquidity Item Update in SPRO > Cash and Liquidity Management > Define Basic Settings > Cash Scope “Basic Scope”.
So, Liquidity Item Update took 30 minutes out of the 2 hours for this file size (40%).
Our testing shows that on average there are around 2,18 FB01 postings per second.
In the traces we took, this shows as follows:
So, the obvious candidates to optimize the performance are “CALL TRANSACTION FB01” and “COMMIT WORK” with 78,4 and 16,2 of the Gross Processing Time (%).
On the first part of the trace, “CALL TRANSACTION FB01” was executed 11 times with a total execution of 1.287 milliseconds (117 milliseconds per execution).
On the second part of the trace, “CALL TRANSACTION FB01” was executed 9 times with a total execution of 919 milliseconds (102 milliseconds per execution).
These values did not seem excessive and where also similar for all the postings.
Regarding the “COMMIT WORK” calls, they also did not seem excessive. However, we did some checks on the I/O subsystem level of the database and they were satisfactory.
The 19 update tasks were also traced, the longest one took 187 milliseconds, all combined took 2,6 seconds. All these 19 traces were similar, the call that usually took more time was “SELECT COUNT(*) TBTCO”:
In the above case, it took 23 milliseconds, 20% of the overall runtime of the update process.
TBTCO is the table that has all the background jobs history and it is looking for SAP_FCLM_FLOWBUILDER_TRIGGER event driven jobs which are either ready or running:
Table TBTCO should be part of the maintenance tasks and all the jobs should be deleted from the system after a reasonable period, example 2 weeks.
On all executions there was an additional job automatically triggered: FQM_BNKACCT_BAL_RECONCILIATION. SAP Note 2831114 “One Exposure: Bank Account Balance Reconciliation) explains it: “The balance reconciliation is triggered automatically when importing electronic bank statements, the manual bank statement does not trigger reconciliation.”
We researched on the possibility of parallelization or using dialog mode for big statements, i.e. upload of file in FF.5/FF_5 online, but have the following comments:
Update tasks generated from background sessions can only be processed in synchronous mode, this means that the job will wait for the end of the update task before it starts processing the next item. So, by executing it in background there will not be more than one update work process anytime.
From the performance trace taken on the smallest file, we were able to see that the system takes ~138 milliseconds to process each update task. And each update process task took ~453 milliseconds to be prepared (times taken with trace active).
So, even in dialog mode, there will not be more than one update work process anytime.
But we can still see some parallelism by calling the program in dialog mode. As the call to the update process is performed with a “COMMIT WORK” statement, then an asynchronous update call is performed, and the dialog process will not wait for the end of the update task to proceed:
On the other hand, when we called the program in dialog mode, we noticed a lot of time spent on roll-in and roll-wait situations (this does not occur on background processing). The trace introduced a significant overhead, but probably the roll-in and roll-wait times are lower than the improvement achieved with the parallelization of the dialog and update work processes.
From a technical perspective, the processing time is within the expected ranges as defined by SAP, by the average number of FI Documents posted per second. On average, there are around 2,18 FB01 postings per second, which is within our benchmark for customers using FQM and Cash Management.
The obvious candidates to optimize the performance are “CALL TRANSACTION FB01” and “COMMIT WORK”.
We did not identify any action that could have a significant reduction on the overall processing time. Instead, we have identified small technical improvements that would not significantly reduce the overall processing time.
From a functional perspective, the bank statement itself (file import, table updates, interpretation, search pattern) without posting (SAP Transaction FB01) needs only 3,5 minutes (out of the 2 hours when posting).
The entry that you see in the traces with ‘CALL TRANSACTION FB01’ is the SAP Transaction FB01 itself and all programs and applications which are called within FB01, e.g. FQM.
If, for example, you have 13.000 items, FB01 will be called at least 13.000 times, depending on the Posting Rule.
40 and 30 min respectively out of the 2 hours (25 to 33%) are taken because of FQM and Cash Management activation:
0,55 seconds per document with Cash Management.
0,36 seconds per document without Cash Management.
The reasonable benchmark for number of FB01 per second is approx. 3 FI Documents per second.
In our example, the system posts 2,81 FB01 Documents per second, so it appears to be nearly within benchmark limits.
We do not see, in our example, any major potential for improvements on the bank statement side of the programming (file import, table updates, interpretation, search pattern and posting (SAP Transaction FB01)). One could think here on the possibility to of setting the “Compression” indicator in the configuration of the Posting Rules (SAP Transaction OT59), which was not set in our example. This would group items in the file according to fields KUKEY, VALUT and BATCH and maximum 900 items would be compressed. This would of course, reduce the number of postings, but on the other side would involve changing the subsequent business process, depending on your configuration. For example, the F.13 Automatic Clearing Program later would clear the itemized amounts in case FB05 in Posting Area 2 is not configured, but instead you use FB01 to a Bank Clearing Account.
SAP Help: “Posting rules can combine several bank statements items in one single posting item. Thus, the items can be posted collectively in a compressed way as one posting item. Compressions of bank statement items are usually done in G/L postings”.
So, the functional conclusion is that area to investigate in more detail to help reducing processing times is the FQM / Cash Management part.
From SAP Support side, in case you are considering creating an SAP Incident, we are talking about SAP Component FI-BL-PT-BA for the first part without posting (file import, table updates, interpretation, search pattern), SAP Component FI-GL-GL-A for the FB01 posting part and SAP Component FIN-FSCM-FQM for the One Exposure / Cash Management part.
SAP Development in the FQM side of the calls within the posting part (SAP Component FIN-FSCM-FQM / One Exposure), has already evaluated Bank Statement upload performance in different customer systems and set ups and they are already aware of the contribution from FQM to the overall processing time.
SAP Development from the FQM side, is already aware that there are different aspects that contribute to runtime which need optimization, not just technical but also design aspect changes, which will not be a target in lower releases.
From the FQM side, the biggest contribution would come from Liquidity Item derivation, particularly in Function Modules FLQ_CHECK_GL_INFO and FLQ_READ_QUERIES.
For Function Module FLQ_CHECK_GL_INFO, SKB1 Table is read out twice, one for From-To Range account entry, one for single account entry from FLQACC_INFO_APP and it could be combined.
For Function Module FLQ_READ_QUERIES, 3 tables (FLQQR, FLQQRRG, FLQGRP_QR) in one join statement may cost time, and it could be changed into two steps selection to avoid HANA calculation
Our major advice, also by talking to SAP Development from the FQM side, is to check if the recommendations provided in the SAP Note 3079234 One Exposure: Avoiding Liquidity Item derivation for Bank Statement Postings will help to reduce the overall runtime by means of testing.
SAP Note 3079234 One Exposure: Avoiding Liquidity Item derivation for Bank Statement Postings
Here below an extract from the Note:
Please take into consideration that this possible optimization will only reduce, in our example, some of the last 30 minutes of the overall processing time.
Furthermore, please check if the implementation of the following SAP Notes additionally would reduce processing time by means of testing.
SAP Note 2752432 – Expensive LOOP at L_T_INFO_POS in FM FLQ_CHECK_GL_INFO
From the traces we took, SAP Note on L_T_INFO_POS is relevant, but it will not reduce so much the processing time as it accounts for 5,2% of the gross Database time and 6,6% of the gross ABAP Time.
SAP Note 2965742 – One Exposure: Performance optimization for flow category derivation in BS Adaptor
(Note: this SAP Note came with S4CORE release 103 SAPK-10306INS4CORE).
SAP Note 3066974 Performance improvement for deriving BSEG-FQFTYPE
(Note: This Note is only available from S4CORE release 104 onwards. If you are in a lower release, you cannot implement it manually).
SAP Note 2921603 – One Exposure: Ensure KUKEY value is fulfilled in Bank Statement Adaptor
(Note: this SAP Note came with S4CORE release 103 SAPK-10305INS4CORE or with release 104 SAPK-10403INS4CORE).
SAP Note 3066974 – Performance improvement for deriving BSEG-FQFTYPE
(Note: this SAP Note came with S4CORE release 104 SAPK-10405INS4CORE or with release 105 SAPK-10503INS4CORE).
We hope you found this blog post helpful, and please let us know your thoughts or the result of your testing.
Brought to you by the SAP S/4HANA Regional Implementation Group (RIG).