Symptom
- Inconsistent results are imported while running same query based on Import Data connection to OData-based service in SAP Analytics Cloud (SAC)
- During data import, there maybe be duplicate or missing rows/values
- During dimension import, some dimensions may have different unique members (less then expected) in each run
Environment
- SAP Analytics Cloud
- Import data connection to OData Service (SAP S/4HANA, SAP IBP, SAP BPC NW, SAP SuccessFactors, etc.)
Reproducing the Issue
- Log on to SAC tenant.
- Try to create a new model on Import Data connection to 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 (less then expected) in each run.
Cause
- The issue occurs due to inconsistent pagination for larger datasets.
- This happens because 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 detail from help guide).
- Note: 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 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.
Resolution
Backend Fixes (Recommended):
- Define Keys in the Backend:
- In most OData services (e.g., SAP S/4HANA or SAP Datasphere), 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>
-
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.
Workaround (If Backend Fixes Are Not Applied):
- 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: See Also
- 2569847 - Where can you find user assistance (help) for SAP Analytics Cloud to use, configure and operate it more effectively?
- Have a question? Ask it here on the SAP Community. Or reply and share your knowledge!
- 2487011 - What information do I need to provide when opening an case for SAP Analytics Cloud?
- SAP Analytics Cloud > Learning > Guided Playlists
- SAP Analytics Cloud > Learning > Guided Playlists > Getting Support
Your feedback is important to help us improve our knowledge base.
Keywords
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, multi , KBA , row values are multiplied , multiplied of single records. , measure values are multiplied of single , data are wrong imported and measure valu , LOD-ANA-AQU , Import Data Connections (Acquiring Data) , LOD-ANA-AQU-ODATA , Acquiring Data into SAC using an ODATA connection , How To
Product
SAP Analytics Cloud 1.0
- Use the Freehand Query feature of SAC to explicitly add an