Symptom
- When previewing Remote table in SAP Datasphere, the generated remote query is not optimized, resulting in a longer execution.
- In Remote query monitor, the remote statement does not contain WHERE clause, JOINs or other SQL capabilities.
- Data preview with a remote table query like select count(*) from <remote table name> gives a timeout when using the ABAP adapter.
Environment
SAP Datasphere
Reproducing the Issue
- Previewing data of remote tables
- Previewing data of views consuming federated data
- Executing SQL Statement in Datasphere's HANA Database Explorer
Cause
According to each DP Agent Adapter in use, there is a list of capabilities which are supported or not.
This can be verified using a Database Analysis User (do NOT use Database User on Space-Level) as examples below:
SELECT * FROM "SYS"."ADAPTER_CAPABILITIES" WHERE "ADAPTER_NAME" LIKE '%Oracle%' AND IS_SDA_SUPPORTED='TRUE';
SELECT * FROM "SYS"."ADAPTER_CAPABILITIES" WHERE "ADAPTER_NAME" LIKE '%ABAPAdapter%' AND IS_SDA_SUPPORTED='TRUE';
Other Adapter examples can be checked in DP Agent documentation: Configure Data Provisioning Adapters
According to the capability, the feature can be supported or not.
For ABAPAdapter, these are some known limitations:
- Comparison operators like ">", "=>", etc. are supported only for ABAP tables and not supported for ODP extractors;
- Only "=", "LIKE" and "BETWEEN" (and their negations) are supported for ODP extractors;
- Features such as GROUP BY, DISTINCT, COUNT are not supported.
Resolution
If possible, adjust the SQL Statement in order to avoid the unsupported Adapter's capabilities.
- If using ABAPAdapter and >= is required, reframe the ">=" filter into a BETWEEN.
e.g.: "FISCYEAR >= 2022". Transform it into "FISCYAER > 2022 AND FISCYEAR < 2999", where the 2999 is some maximum value greater than the range of interest.
This should satisfy the selection limitations imposed by the ODP extractor framework mentioned above.
See Also
- Community Post Hana Adapter SDK - Adapter capabilities
- HANA SDI Help Capabilities
Keywords
fetch, execute, to_date, dynamic, filtering, functions, casting, cast, inner join, ABAP adapter, count(*), data preview , KBA , DS-DI-RT , Remote Tables , DS-DI-CON , Connections , HAN-DP-SDI-DS , SAP Datasphere specific SDI issues , HAN-DP-SDI , SAP HANA smart data integration (SDI) , Problem