SAP Knowledge Base Article - Public

2418819 - Reports Best Practices & Restrictions for Ad Hoc - ORD (Online and Offline capabilities)

Symptom

This KBA will cover the the following topics:

  • Best Practices for Online Report Designer (ORD) and Ad Hoc reports
  • Hard Limits for ORD and Ad Hoc reports
    • What are the timeout rules when running Online or Offline Reports?
    • Are the timeout limits customizable/flexible?
    • What are the row limits in CSV/XLS/XLSX?
    • What is the rows limit for report queries?
    • Why Ad Hoc reports are taking a long time to start running offline?

Important Note: The SuccessFactors reporting tools should be used ONLY for generating human readable content. It is not intended nor designed to be used as a tool for data extraction to external systems.

Further details available via the Reporting or Integration document on the Help Portal.

Environment

SAP SuccessFactors Workforce Analytics

Resolution

To safeguard the overall application there are some limits implemented in the application.

The Best Practice sections provides some recommendations to follow based on specific scenario.

Best Practices for ORD and Ad Hoc reports

The application is designed to work optimally under these scenarios:

  • Calculated Columns
    • Conditional statements should be nested no more than 10 statements deep

  • Pivot Tables
    • No more than 10-15 measures should be used on any one pivot table
    • Ensure the List Query data source for the Pivot Table only contains the columns necessary to be used in the Pivot
    • Pivot tables over 20,000 rows may impact performance.

  • Pivot Charts
    • To display chart, the Pivot Chart takes more time than Pivot Table. Once timeout limit is crossed for Pivot Chart then it shows an error.

  • Data Volume
    • List Queries in excess of 200,000 records may impact performance (time outs etc.)
    • Pivot Tables in excess of 100,000 records may impact performance (time outs etc.)
    • Any tables in excess of 64,000 records should be exported to CSV format
      Note that there are many variables that affect query performance, and to take the above as a generalised guideline only.

  • Query Design
    • Default People Scope is Team View – Direct Reports
    • Default Preview is 10 rows per page (maximum of 100 rows in Preview) – query should be used in a report to show more rows

  • Report Design
    • Reports containing multiple components should consider the data volume across all components
    • Ensure user prompted filters are consistent across all components/queries
    • Reports that are defined in BizX Ad Hoc can be run in ORD. This is called "Adhoc 2.5" i.e. half way between Ad Hoc 2 = BizX, and Ad Hoc3 = ORD/WFA Live Data.
      We don't recommend it; you cannot edit the query in ORD, or apply filters and the adhoc report needs to be shared with each user who wants to run the report.

  • Exporting
    • Position List Components on a separate page to Chart/Pivot tables when PDF/PPT/Word export is required

  • Report Sharing
    • Report menus are dynamic depending on user access
    • Share reports to BizX Roles, not individual users

      Document reference available in attachment section.

Hard Limits

To safeguard the overall application the below hard limits are implemented for Offline Data export and when these limits are exceeded the respective reports/jobs will be terminated as outlined below.

When these limits are exceeded reports/jobs will be terminated as described below.

  • Timeout Rules to run ONLINE or OFFLINE Reports
    Timeouts are set on the entire Data Center for all products, this are general rules put in place to prevent performance issues. For this reason, you will have to determine the amount of data to be exported and fetched in your reports and determine whether to use different modes or solutions.
    • Run Reports ONLINE Timeout:
      • Exporting Ad Hoc Reports: will be forcibly terminated after 5minutes
      • Exporting ORD Queries or Reports: will be forcibly terminated after 5minutes
    • Export Offline/Scheduled report Timeout:
      • Business Hours (05:00 – 19:00): will be forcibly terminated after 2 hours
      • Outside Business hours (20:00 to 04:00): will be forcibly terminated after 4 hours

  • Are the timeout limits customizable/flexible? 
    Timeouts are set on the entire Data Center, so any customizations would affect all instances/customers. Rather than simply increasing the time out, we are working on several efforts to improve overall performance and display better time out messages with each release. These type of efforts aim to reduce the amount of time needed to run reports. Long term we are also exploring ways to automatically re-route online reports to an offline mode if the report takes a long time to generate online. However if we still see a valid need to increase time out values, we would always consider it.

    Note: if you are looking for Data dump export, you should use our Integration tools for this. Report is not intended nor designed to be used as a tool for data extraction to external system. Output are generated for a human to read. KBA reference - Link

    If you have further questions, please contact our Product team using our community website. https://community.successfactors.com/t5/Reporting-and-Analytics/

  • What are the row limits in CSV/XLS/XLSX?
    There is native limitations with Microsoft Excel Product but timeout rules will still be applied.

    Type CSV XLS XLSX
    Microsoft Excel limitation in rows* No limit 65536 1,048,576

    *It represent the technical limit imposed by the format.

    Our recommendation for huge data set is to use OFFLINE and CSV or schedule it outside business hours.

    NOTE: We do not recommend using Ad Hoc reports for tracking/auditing purposes. Since this data may increase exponentially over time, kindly use other incremental extraction methods for the same. For example: Use the incremental load in provisioning (Ad Hoc Report Export Offline) option which is more suitable for such auditing requirements.

  • What is the rows limit for report queries?
    When the threshold value of 5 Million Number of rows is reached by report queries

  • Why Ad Hoc reports are taking a long time to start running offline?
    The Scheduler Framework framework is intended to run 10 jobs at a time from each job server. When the jobs exceed this number, it is configured to run from emergency queue were we have 6 job nodes to pick the jobs. If some jobs are running and it is taking long time to finish the job execution it will make a delay to pick the next job. There might be some long running jobs which can the delay. At the moment the jobs finish, it will take the new jobs. 

Keywords

  • CSV
  • XLS
  • XLSX
  • Export
  • Adhoc
  • Spreadsheet
  • ORD
  • Advanced Reporting
  • WFA
  • Audit Framework
  • Share
  • Report Distributor
  • Exporting
  • Query Design
  • Calculated collumn
  • Pivot
, KBA , LOD-SF-ANA , Analytics & Reporting (Ad Hoc, ORD) , Problem

Product

SAP SuccessFactors HCM Core all versions ; SAP SuccessFactors Workforce Analytics all versions

Attachments

ORD Performance Guidelines v3.pptx