Smart Data Access : virtual table on top of remote calculation view

[Tdelaloy]

Hi,

I'm part of SAP Global IT and we are trying to establish a Smart Data Access connection between 1 HANA system and 1 HANA BW Corporate system.

The SDA connectivity is working fine and we are able to import tables as virtual tables. Nevertheless we are facing 2 issues :

* The list of schema is quite long and we reach the limit of objects. We get the error message : 'Remote objects limit (1000) reached'. is it possible to extend the limit like it is possible for table list via preferences > SAP HANA > Runtime > Catalog ?

* We would like to create a virtual table on top of Calculation view. The CL is materialized as a column view in the _SYS_BIC Schema. We tried to create the virtual table using SQL statment:

CREATE VIRTUAL TABLE "CORP_PUBLIC"."VT_BWX_ ADRM_FORECAST_TEMPLATE_HISTORY"  AT "AAX_BWX_CORP"."NULL"."_SYS_BIC"."corp.adrm.reporting/CL_ADRM_FORECAST_TEMPLATE_HISTORY";

Here we get this error message:

Could not execute 'CREATE VIRTUAL TABLE "CORP_PUBLIC"."TEST2" AT ...' in 328 ms 182 µs . SAP DBTech JDBC: [476]: invalid remote object name: Unable to retrieve remote metadata for _SYS_BIC.corp.adrm.reporting/CL_ADRM_FORECAST_TEMPLATE_HISTORY: line 0 col 0 (at pos 0)

the technical user used by SDA has the authorization to perform a data preview on the calculation view on the remote system.

We also tested this same statement but with a table instead of a calculation view:

CREATE VIRTUAL TABLE "CORP_PUBLIC"."TEST" AT "AAX_BWX_CORP"."NULL"."CORP_PUBLIC"."corp.adrm.prediction::PREDICTIVE_REVENUE";

This is working fine on our system so we assume the issue is related to the fact that we are trying to create a virtual table based on a calc view. Do we need additional authorization for metadata (SYS_BI) ?

We also tested to create a virtual table based on a calc view in another private system and it’s possible. So maybe here the issue is related to authorizations.

We have HANA 102.01 on both sides

Could you please help us ?

Thomas

[jino_jose · Accepted solution]

Hi Thomas,

Please refer note 2184030 for increasing remote object size limit.

As per my understanding ,SP10 doesn't support column views as a source object for  virtual tables, The note 2154347 and 2171760 holds the details. I think the feature brought back again as part of SP11.

In the thread you have mentioned, you could generate a virtual table using calcview, what's the version of that system?

Hope it helps.

Thanks,

Jino.