2816755 - Inconsistent results while importing data using an OData service in SAP Analytics Cloud

• Inconsistent data is imported while running the same query based on the Import Data connection to an OData-based service in SAP Analytics Cloud (SAC).
  • During data import, there may be duplicate or missing rows/values.
  • During dimension import, some dimensions may have different unique members (fewer than expected) in each run.

SAP Analytics Cloud 2025.14.17 

1. Log on to the SAC tenant.
2. Try to create a new model on the Import Data connection to an OData-based service.
   • When the same query runs several times, the result differs after each query run if the result set is big.
   • Some dimensions may have different unique members (fewer than expected) in each run.

The issue occurs due to inconsistent pagination for larger datasets. This happens because the datasource/service fails to keep track of the order of the data when processing batches. By default, the batch size is set to 50,000, and you can now configure a batch size from 1,000 to 50,000 (see details from the help guide).

Note: the custom batch size feature isn't designed for freehand query based on an OData connection, which will retrieve data in batches of 1000. At the moment, it is only supported for the query builder.

By fetching the $metadata of the OData service, you will notice that no <Key> elements are defined under the entity. The absence of keys causes the backend to return untracked data, leading to inconsistent results during pagination.

Order the query by the primary/sort key(s) (Best practice):

1. In the QRC4 2025, a new option has been added to the pagination settings:
   • When creating a new model or creating/modifying a query in an existing model, the "Please select the primary/sort key(s) for the query" option has been introduced to the pagination settings.
   • Upon opening the dropdown, all members from the selected data source that are eligible for ordering are displayed.
   • The value helper supports multiple selection. This is particularly useful in cases where the dataset does not contain a single unique identifier. Multiple fields can therefore be combined to establish a unique and stable ordering of the data.
   • More information can be found in the following KBA: 3684376 - QRC4 2025 - Avoiding OData inconsistencies when importing data in SAP Analytics Cloud

Backend fixes (solid workaround):

1. Define Keys in the Backend:
   • In most OData services, this issue can be resolved by defining proper keys for the entity in the backend.
   • Keys ensure that the data is uniquely identified and consistently ordered.
   • Example of metadata with Keys:
     
     <EntityType Name="EntityName">  
       <Key>  
         <PropertyRef Name="KeyField"/>  
       </Key>  
       <Property Name="KeyField" Type="Edm.String" Nullable="false"/>  
     </EntityType>
     
     

2. Configure nextLink for Server-Side Pagination:

   • If defining keys does not fully resolve the issue, configure nextLink for proper server-side pagination.
   • This ensures that the server handles pagination consistently, eliminating duplicates or missing records.

Note regarding the nextLink configuration: 

For SAP Datasphere as backend system, NextLink is automatically added by the API when there are more records than the current page returned. In this case, the nextLink property is returned using the $skiptoken.

When client-side paging is not used, this involves the $top parameter, which limits the number of records. When $top is used, it is assumed that the client will handle pagination, so nextLink does not appear.

Workaround (if backend fixes don't work):

1. Use Freehand Query in SAC:
   • Use the Freehand Query feature of SAC to explicitly add an $orderby clause in the query.
   • This forces the backend to sort the data on the server side, ensuring consistent pagination.
   • Freehand query syntax is available under section "4.5. Filter System Query Option ($filter)" here: https://www.odata.org/documentation/odata-version-2-0/uri-conventions/
   • Example of freehand query: EntityName?$orderby=KeyField
     => If multiple fields are required to be Keys, it will be EntityName?$orderby=KeyField1, KeyField2
     
     
2. Avoid Data Changes During Import:
   • Ensure that the data remains static while the import job is running in SAC.
   • If the data changes during the import process, inconsistencies or duplicates may still occur, even with the $orderby clause.

Key Points to Note

• Defining keys in the backend is usually sufficient to resolve the issue.
• If keys are defined but inconsistencies persist, server-side pagination with nextlink should be implemented.
• Freehand Query is a workaround and not a substitute for fixing the underlying issue in the OData service.

• 3684376 - QRC4 2025 - Avoiding OData inconsistencies when importing data in SAP Analytics Cloud
• 2569847 - Where can you find SAC user assistance (help) to use, configure, and operate it more effectively?
• 2487011 - What information do I need to provide when opening a case for SAP Analytics Cloud?
• 2511489 - Troubleshooting performance issues in SAP Analytics Cloud
• SAP Analytics Cloud Connection Guide
• SAP Analytics Cloud: Get More Help and SAP Support
• Need More Help? Contact Support

Your feedback is important to help us improve our knowledge base.

SAP Cloud for Planning, sc4p, c4p, cforp, cloudforplanning, EPM-ODS, Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, Cloud 4 Planning, BOC, SAPBusinessObjectsCloud, BusinessObjectsCloud, BOBJcloud, BOCloud., SAC, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics,  rows, number, members, Odata, missing, lost, unique, different, $skip, $top, $orderby, orderby, freehand, order, batch, duplicate, multiple, multirow values are multiplied, measure values are multiplied of single, multiplied of single records, data are wrong imported and measure value. , KBA , LOD-ANA-AQU , Import Data Connections (Acquiring Data) , LOD-ANA-AQU-ODATA , Acquiring Data into SAC using an ODATA connection , How To