2631131 - Employee Central: Ad Hoc Reports Take a Long Time to Execute or are Timing out

Symptom

When trying to run any of the following Employee Central Ad Hoc reports, where there are many JOINS and a lot of data, the reports take a very long time to run or the report will time out and fail.

Person and Employment (As of Date)
Job Information (Date Range)
Recurring Compensation Information (Date Range)
Non-Recurring Compensation (Date Range)

In some cases, a BIRT Error similar to this may appear when running the report Online:

org.eclipse.birt.report.service.api.ReportServiceException: Error happened while running the report
Caused by: org.eclipse.birt.report.engine.api.EngineException: Error happened while running the report
Caused by: com.successfactors.analytics.queryengine.exceptions.QueryEngineException: Error encountered in executing the query. SAP DBTech JDBC: [260]: invalid column name: GO_JOB_CODE.$rowid$: line 8 col 7724 (at pos 8249)

Running the report Offline may also encounter unexpected results such as blank output files and error messages such as "Excel can't open the file. Report name or extension is invalid."
If you edit the report and click Preview, you will may also get an Application Error with fingerprint=7af84dfabaddd815f78b53e7e708718f2c5d845a:

Error encountered in getting the data.com.successfactors.analytics.queryengine.exceptions.QueryEngineException: Error encountered in executing the query. SAP DBTech JDBC: [260]: invalid column name: GO_JOB_CODE.$rowid$: line 8 col 7724 (at pos 8288)

Environment

SAP SuccessFactors Employee Central - Ad Hoc Reports

Reproducing the Issue

Running any of the following reports takes a lot of time to preview or generate in the UI - and in some cases fails (no data is returned). This can happen with the follow 4 Employee Central Ad Hoc reports:

Person and Employment (As of Date)
Job Information (Date Range)
Recurring Compensation Information (Date Range)
Non-Recurring Compensation (Date Range)

Cause

The more complex the report (where multiple joins are used or the target population of the report is growing) the more likely you will run into performance issues.

While forming an adhoc query we create an SQL query involving all fields for a logical table, irrespective of the fact that the field is selected in the UI or not. If a field is configured as visible (view or both) in Data Model, then we will consider the field in the ad hoc query.

This results into:

Fetching data of a field which is not required in the final resulting (outer) query
Extra join if the field is FO or GO.

The SQL query formed has unnecessary JOINS with different FO and GO related tables. It increases the size of the query causing higher parsing time and slower "execution / result set fetching" time due to the higher number of JOIN conditions between tables.

Go to in Provisioning
scroll down to: Disable EC Ad Hoc Query Trimming

Resolution

1802 Release:

We've introduced a new feature switch for Employee Central Ad Hoc Reports named "Disable EC Ad Hoc Query Trimming" in Provisioning > Company Settings to reduce the size of SQL query, which helps to reduce the query parsing time. Apart from that it will involve a smaller number of JOINS as well, eventually improving the execution time.

Please enable this feature if you are experiencing performance issues whilst running Employee Central Ad Hoc Reports.

1808 Release:

NOTE: This switch will be removed in the 1808 release as the feature is now Universal. Instead the above switch in Provisioning is replaced with a switch to disable this feature

SPECIAL NOTE: AdHoc Query Trimming or Disable EC Ad Hoc Query Trimming supports only the following Ad Hoc Reports (Employee Central):

Person and Employment (As of Date)
Job Information (Date Range)
Recurring Compensation Information (Date Range)
Non-Recurring Compensation (Date Range)

NOTE: As You'll require access to Provisioning page to implement this solution .

Please contact SAP Cloud Support team if you wish to have this feature enabled in your environment or request Your Partner to refine your configuration.

Customer must provide a written approval for below feature in provisioning

Employee Central Support Engineer Team Action:

Go to in Provisioning
Scroll down to: Disable EC Ad Hoc Query Trimming
Uncheck switch :Disable EC Ad Hoc Query Trimming

Keywords

ad hoc, ec, employee central, person and employment, Person and Employment (As of Date), Job Information (Date Range), Recurring Compensation Information (Date Range), Non-Recurring Compensation (Date Range), Person and Employment Audit, Person and Employment Export, performance issue, slow, long running, BIRT error, org.eclipse.birt.report.service.api.ReportServiceException, Error happened while running the report, Enable adhoc Query Trimming, performance, Adhoc report, Disable EC Ad Hoc Query Trimming, Adhoc Report , KBA , LOD-SF-EC-REP , Reporting Data (EC core only) , How To

Product

SAP SuccessFactors Employee Central all versions ; SAP SuccessFactors HCM all versions