SAP Hana Smart Data Access (SDA) – Basic Configuration

In this post, I have explained step by step configuration of  Smart Data Access for Hana Database with error resolutions.

One of main challenges that companies are facing today are

1. Get information in real time to make quick decision on time

2. At the same time, we need to keep control over cost for IT and Technology.

By Keeping Business requirement in mind, SAP has introduced Smart Data Access in SAP HANA which is a Virtualization Technique. This feature is introduced from Hana 1.0 SPS 6 in SAP HANA.

What is Smart Data Access?

SAP HANA smart data access enables remote data to be accessed via SQL queries as if they are local tables in HANA, without copying the data into SAP HANA.

Not only does this capability provide operational and cost benefits, but most importantly it supports the development and deployment of the next generation of analytical applications which require the ability to access and integrate data from multiple systems in real-time regardless of where the data is located or what systems are generating it.

Key Concept of SAP HANA Smart Data Access

Smart Data Access is a technology which enables remote data access as if they are local tables in HANA without copying data into SAP HANA.

It is based on local virtual tables that maps to an existing object at the remote data source site.

Data required from other sources will remain in virtual tables. Virtual tables will point to remote tables in different data sources. It will enable real time access to data regardless of its location and at same time, it will not affect SAP HANA database.

Customers can then write SQL queries in SAP HANA, which could operate on virtual tables. The HANA query processor optimizes these queries, and executes the relevant part of the query in the target database, returns the results of the query to HANA, and completes the operation.

With the SAP HANA 2.0 SPS 00 release, virtual tables have been integrated with result caching in HANA. Static caching has been available as of SAP HANA 1.0 SPS 11, what is new in this release is the support for virtual tables. If you choose to enable the caching feature on virtual tables you can look forward to improved performance by means of avoiding redundant computation of identical and frequent queries. You can enable this feature either directly via the ini file or by using SQL. Also keep in mind that you need to define a cache staleness restriction when configuring.

Restrictions on usage of Smart Data Access:

Data in virtual tables cannot be modified i.e. insert, update and delete operations are not possible on virtual tables. However, we can do select on virtual table and put data in HANA table.

Virtual table does not support BLOB/CLOB data type. Workaround of this problem is to create view on remote table by excluding column of BLOB/CLOB type and then create virtual table with the help of view.

Virtual Tables cannot be used in multi node HANA cluster. However, it is possible to access virtual table from one HANA server to other.

Some analytical view does not support virtual tables.

Need to add below entries in odbc.ini file

[HDB]

Driver=/hana/shared/<SID>/hdbclient/libodbcHDB.so

ServerNode=<hostname>:31113

->Expand the  folder Provisioning<-we see the option Smart Data Access.

Expand  the tab Remote Sources, there you will find all different sources connected remotely

Expand remote sources<-select new remote source

Click on adapter name<-we can connect to any of the following adapter names

Right click on remote source<-select new remote source

Provide source name, adapter name, server name, port number, user name and password

If you connect with system user, use the port 3xx13 (xx->Instance No)

->From Hana 2.0 SP00  by default it contains 1 tenant (Multi Tenant), so use the port 3XX41 (xx- instance no)

Click on save

Once it is connected, we see the source system connected as shown below.

Click on save this editor

Right click on remote source<-click refresh, now we can see new source “HANA_LOCAL_INDIA5” is created

Go to catalog<-BEST schema<-tables<right click on tables<-click on new virtual table

Give the table name and schema type and then click on browse

Once you click the button browse, expand source system<-null<-schema<-select sales and click on OK

Click on save this editor

One it is saved we can see table – HANA_LOCAL_INDIA5_SALES HANA_LOCAL_INDIA5

Now, we need to use the source table in the calculation view, in order to create calculation view, we need to create a package first, so right click on content->new->click on package

Provide details and click on ok

Right click on package “SDA111”<new<-select calculation view

Provide details and click on finish

Drag and drop the source table onto the work space

Drag and drop projection onto workspace

Drag and drop the source system into the projection

Select region_id, prod_id and sales_amt in the source system table

Link a join between projection and aggregation

Click on aggregation and select the columns from the table

Click on semantics

Save and validate<-save and activate

Assign objects to change and click on finish

Click on data preview

Click the button Raw Data

The data in cache memory is empty, so calculation view request data  from virtual table. The virtual table gets data from source table to connection, then to calculation view through connection itself.

Troubleshooting

1. 2397816 – SAP DBTech JDBC: [403]: internal error: Cannot get remote source objects: Unable to load driver manager on IBM Power

2316463 – * -10709: Connection failed (RTE: [89006] System call ‘connect’ failed, rc=111: Connection refused

Cause: This error is usually caused by using the wrong port for the connection

Resolution:

2. Error: SAP DBTech JDBC: [403]: internal error: Cannot get remote source objects: [SAP AG][LIBODBCHDB SO][HDBODBC] General error;10 authentication failed

Resolution: SAP System is on Hana 2.0 SP01 so by default it contains 1 tenant (Multi Tenant), use the port 3XX41 (xx- instance no)