Smart Data Access

[Former Member]

Hi,

We need to consume views created in one HANA system into another HANA system.

We explored Smart Data Access functionality for this but while defining Remote Source we do not see HANA in the adapter drop-down.

My queries are - can Smart Data Access be used between HANA systems, what are the components needed and their availability.

We are on HANA SP6, Revision 61.

Pl assist. Thanks.

[Former Member · Accepted solution]

Provided you stick to simple ANSI SQL, you can make SDA work to a HANA system for the purposes of testing. Just use the HANA ODBC drivers and use the IQ configuration. This will not be a supported configuration.

As of HANA SP68, only Sybase IQ, ASE and Teradata are available. Hadoop support was dropped out the GUI for reasons I can't explain.

It is in the roadmap to support generic federation.

John

[vivekbhoj · Accepted solution]

Thanks for the update John

Regards,

Vivek

[Former Member · Accepted solution]

Hi John,

I was able to create the Remote Data Source using SQL Scripts and using ASE (ODBC) driver.

While previewing (using SQL and GUI) data of the Virtual Table I get the following error -

SAP DBTech JDBC: [403]: internal error: Error opening the cursor for the remote database [SAP AG][LIBODBCHDB SO][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "/": line 1 col 3343 (at pos 3343)

I tried to view the contents of a BW Fact Table into Enterprise HANA and used the following script -

select top 10 * from "schema_name"."table_name".

Any ideas on this error .. ?

[Former Member · Accepted solution]

Try one of the other drivers e.g. IQ. I suspect the BW fact table syntax /BIC/FXXXX must work differently between HANA and ASE.

Otherwise, I hear there may be more support for this in the SP07 release, which was announced this week. Hopefully that will arrive next month.

Sorry I forgot to mention earlier but SDA is new in HANA Rev.60 and is pretty buggy until Rev.64. I'd recommend you update to the latest version as there were several problems in Rev.61.

John

[vivekbhoj · Accepted solution]

Hi Saurabh,

As John mentioned there might be more support for this in  HANA SP07 release.

With BW 7.4, there is support for Smart Data Access, check this document on BW 7.4 on HANA:

http://scn.sap.com/docs/DOC-48044

Regards,

Vivek

[Former Member]

Hi ,

I have been stuck on the following error, while replicating tables from Hive to HANA. Any help would be appreciated.

(dberror) 2048 - column store error: "ADMIN"."SDI::hivetest.START_REPLICATION": line 3 col 0 (at pos 110): [2048] (range 3): column store error: task framework: [403] internal error: Error opening the cursor for the remote database Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.tez.TezTask. Vertex failed, vertexName=Map 1, vertexId=vertex_1439347078879_0043_1_00, diagnostics=[Vertex vertex_1439347078879_0043_1_00 [Map 1] killed/failed due to:ROOT_INPUT_INIT_FAILURE, Vertex Input: sdi::hivetest.vt_interactions initializer failed, vertex=vertex_1439347078879_0043_1_00 [Map 1], java.io.IOException: No input paths specified in job

at org.apache.hadoop.hive.ql.io.HiveInputForm for query "SELECT "SDI::hivetest.VT_interactions"."mandt", "SDI::hivetest.VT_interactions"."contact_key", "SDI::hivetest.VT_interactions"."id_origin", "SDI::hivetest.VT_interactions"."id", "SDI::hivetest.VT_interactions"."comm_medium", "SDI::hivetest.VT_interactions"."ia_type", "SDI::hivetest.VT_interactions"."timestamp", "SDI::hivetest.VT_interactions"."cms_order_id", "SDI::hivetest.VT_interactions"."client_cust_id", "SDI::hivetest.VT_interactions"."order_number", "SDI::hivetest.VT_interactions"."web_order_number"...

Regards,

Geet

[Former Member]

Hello Saurabh,

We have a similar requirement in our project where we need to get data from one model in one system and use it in union with data from model in another system. We have set up SDA between the systems. However I am unable to see _SYS_BIC schema from the remote system. Is it possible to use the view from _SYS_BIC schema as virtual table?

I would be interested in know how you overcame the challenge.

Thanks,

Aamod.

[abanip]

Hi Aamod,

You can access DB objects (Tables and Views) from a remote system using SDA. The objects under _SYS_BIC schemas are Calculated Scenarios, which are not available through SDA.

I'd suggest you expose the DB tables (used in your view) as virtual tables and build models in the other system on top of these virtual tables.

Regards

Abani

[Former Member]

Abani,

I have SAP ECC and CRM sitting on 2 separate HANA databases and I am interested in cross system reporting.

So how I understand this is that I cannot use the SAP HANA Live views which are calculation views, from SAP CRM onto my SAP ECC using SDA , however I can see the CRM tables (if exposed as virtual) and build models on my SAP ECC system ?

Can I also do joins and unions between the virtual table from CRM and the ECC HANA Live view ?

Is there any other method to achieve what I am trying to do ?

Thank you!

[abanip]

Hi Naser,

Yes, your approach is correct. You can expose the CRM tables as virtual (SDA) tables in the ECC system and build HANA calc views on the SDA tables. You can also use UNIONs & JOINs as required.

Here is a document, I wrote on heterogeneous report using SDA.

Regards

Abani

[Former Member]

Abani,

As the post wasnt started by me I couldnt mark i tas a helpful answer but truly appreciate your response.

Thanks.

[abanip]

Not a problem. I'm glad it worked for you.

[Former Member]

This is very helpful Abani thanks! 

Extending that scenario a bit; I currently have BODS jobs on my HANA sidecar that snapshot SLT replicated BSEG data from ECC Hana.   Assuming I expose the BSEG table as a virtual table in my sidecar using SDA, do you imagine I can point the BODS job to the virtual table rather than the replicated table?

Thanks, much,

Eric

[abanip]

Hi Eric - I'm not sure, if BODS recognizes virtual tables. If it does (It should), then you should be be able able to use in BODS job.

However, be aware of the volume of data transfer across the network.

Regards

Abani

[Former Member]

Understood, it can't be worse than SLT-ing the entire table, which is what we are trying to avoid.   When we do system overlays it takes 4-5 days to re-replicate BSEG currently.

I'd rather just re-run a snapshot job.

Thanks much,

Eric 

[Former Member]

This message was moderated.

[former_member184174]

Hi Saurabh,

with SPS 07 there is support for HANA DBs. Please see HANA Acedemy for configuration.

SAP HANA Academy | SAP HANA

Best wishes,

Christoph

[rainer_winkler]

Hi Christoph,

thank you for this information. It is definitevely correct, with SP07 there is support for HANA DBs. I would have been misleaded without your post, because the Releasenotes for SP07 do not contain this information. I confirmed it via a support message to SAP.

With kind regards

Rainer

[vivekbhoj]

Hi Saurabh,

Smart Data access can be used to connect to Sybase, Teradata and Hadoop.

I am not sure if HANA can be connected using Smart Data Access.

Check the below document on How To Install Database Drivers for SAP HANA Smart Data Access:

https://websmp203.sap-ag.de/~sapidb/012006153200000561122013E/HANA_smart_data_access_drivers.pdf

Check the below SAP Notes on Smart Data Access:

1868209 - SAP HANA Smart Data Access: Central Note

1868702 - SAP HANA Smart Data Access: Remote Datasource Drivers

1879294 - SAP HANA smart data access SP1

1872652 - SAP HANA Smart Data Access: Statistics for Virtual Tables

Regards,

Vivek

[sunny_pahuja2]

Hello,

I don't think Smart Data Access can be used between two HANA systems. It is designed to access database from heterogeneous databases to HANA. Please refer to SAP note 1868209 & 1868702.

Thanks,

Sunny