Symptom
When importing data into SAC from an external datasource (such as Datasphere) using Odata, data inconsistencies (such as duplicate or missing data) can occur if the data is not ordered deterministically (ie. missing ORDER BY) or if the data changes during the import run.
Environment
- SAP Analytics Cloud (Enterprise) 2024.1
Cause
When the data is imported into SAC, it can potentially require more than one batch call to import the data. In that case, if there is no consistent ordering of the data then the ordering of the data can change in between batches which will lead to inconsistent data being imported.
Resolution
The problem can be avoided by applying ordering to the result set. The problem cannot be prevented if data changes during the execution of the import run or if no ordering is applied to the resultset.
This ordering can either be applied on SAC or on the Datasphere side (If using datasphere as the source).
SAC
During model creation, select Freehand Query instead of using the Query Designer.
A Freehand Query is an OData query that SAC uses to import the data. The Freehand Query always starts with the Entity to be imported, for example: ProductView. This corresponds to the name of the object be imported from Datasphere.
The query is further parameterized by the following parameters:
- $select=<column1, …>: the attributes (columns) that shall be imported, eg. ProductName. Multiple attributes to be selected must be separated by a comma (,). Attributes must correspond to their technical name on Datasphere side.
- $filter=<condition>: allows to filter the resultset based on attribute values. Please refer to the Datasphere documentation for the set of supported filtering operations. Not all data types support all filtering operations.
- $orderby=<column> [<asc|desc>]: Specifies the order in which items are returned. Unless otherwise specified, the default ordering is ascending. Adding this parameter is crucial to ensure correct ordering of the resultset!
Parameters are added with ? to the Freehand Query. When using more than one parameter, & must be used for all subsequent parameters.
Special characters such as spaces, quotes must be escaped using percent-encoding like the following:
space | # | $ | & | ^ | ‘ | ( | ) | * | + | , | / | : | ; | = | ? | @ | [ | ] |
%20 | %21 | %23 | %24 | %26 | %27 | #28 | %29 | %2A | %2B | %2C | %2F | %3A | %3B | %3D | %3D | %40 | %5B | %5D |
When filtering for string values, values must be quoted with a single quote (using %27).
For example, the following Freehand Query obtains all Products with a price of more than 100 together with their name and SKU, ascendingly ordered by the Product ID:
ProductView?$select=Name,SKU&$filter=Price%20gt%20100&$orderby=ID
Note that the order the import query does not determine the final ordering in SAC.
Datasphere
It is also possible to apply ordering on Datasphere side for all operations accessing the object. This can e.g. be done when creating a SQL view using standard SQL syntax (ORDER BY), see help.
It is not possible to define a default ordering on analytical models. For analytical models a Freehand Query must be used instead.
Resources
Datasphere OData Parameters: Link
Datasphere OData integration: Link
See Also
- 2569847 - Where can you find SAC user assistance (help) to use, configure, and operate it more effectively?
- Have a question? Ask it here and let our amazing SAP community help! Or reply and share your knowledge!
- 2487011 - What information do I need to provide when opening a case for SAP Analytics Cloud?
- 2511489 - Troubleshooting performance issues in SAP Analytics Cloud
- Search for SAP Analytics Cloud content using SAP for Me, Google or Bing:
- https://me.sap.com/servicessupport/search#?q=SAP%20Analytics%20Cloud%20OR%20SAC&tab=All
- https://www.google.com/search?q=site%3Ahttps%3A%2F%2Fuserapps.support.sap.com+SAP+Analytics+Cloud
- https://www.bing.com/search?q=site%3Ahttps%3A%2F%2Fuserapps.support.sap.com+SAP+Analytics+Cloud
- Note: Add relevant text or warning/error messages to the text search field to filter results.
- SAP Analytics Cloud Connection Guide
- Getting Started with SAP Analytics Cloud Expert Community page
- SAP Analytics Cloud Get More Help and SAP Support
- Need More Help? Contact Support or visit the solution finder today!
Your feedback is important to help us improve our knowledge base.
Keywords
Planning, datasphere, sort, order, inconsistent, error , KBA , LOD-ANA-DES , Story Design & Visualizations , LOD-ANA-AQU , Import Data Connections (Acquiring Data) , Problem