2180119 - FAQ: SAP HANA Smart Data Access

Symptom

You have question related to SAP HANA Smart Data Access

Environment

SAP HANA

Resolution

1. What is SAP HANA Smart Data Access?

Starting SAP HANA SPS 06 a key feature called SAP HANA Smart Data Access was introduced which makes it possible to access external data, without having to replicate the data to SAP HANA.

2. Where can I find information about SAP HANA Smart Data Access?

Please refer section Administration of SAP HANA Data Provisioning Technologies in SAP HANA Administration Guide
Also refer to SAP HANA Smart Data Access: Central Note 1868209

3. How does Smart Data Access function?

In SAP HANA, you can create virtual tables which point to remote tables in different data sources. Customers can then write SQL queries in SAP HANA, which could operate on virtual tables. The SAP 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 SAP HANA, and completes the operation.

4. For which use cases could I use Smart Data Access?

• Customers can access IQ as an archived storage to store cold data in IQ and real time hot data in SAP HANA
• Address Big Data challenges by connecting to Hadoop
• Create Applications by running HANA models across disparate data sources

5. Which communication protocol is being used between SAP HANA and remote data source?

The communication between SAP HANA and remote data source is based on ODBC protocol

6. Which remote data sources are supported?

As of SAP HANA SPS 10, the following remote data sources are supported:
SAP HANA, SAP IQ, SAP ASE, SAP Event Stream Processor, SAP MaxDB, Teradata Database, Microsoft SQL Server 2012, Oracle 12c, IBM DB2, Hadoop Hortonworks HDP 2.3, IBM Netezza Appliance

7. Are there any prerequisites to use SAP HANA Smart Data Access?

SAP HANA should be installed and the remote data sources should be reachable within the same network.
You have installed the drivers for the remote data source, please refer SAP Note 1868702

8. After recent upgrade to SAP HANA database remote connection through SDA fails. What could be the cause?

If you installed ODBC drivers in your HANA exe directory as per SAP Note 1868702 these ODBC drivers will be removed during a revision update and have to be installed again after the update.

9. Are special privileges required to set up Smart Data Access?

System privilege CREATE REMOTE SOURCE is required
Object privilege CREATE VIRTUAL TABLE is required
Object privilege DROP on VIRTUAL_TABLES (SYS)
It is recommended to create the remote source with the HANA SAP<SID> user.
If the remote source is not created with the SAP<SID> user but with a different database user instead, then this database user must assign the corresponding object authorizations to the SAP<SID> user.
With this sql statement you can find the owner of the remote source:
select * from ownership where object_name = '<Name of the remote source>'

10. How do I know if the necessary driver is already installed or not?

Logon to the OS of the HANA server, Go to directory /etc/unixODBC
Now open the file odbc.ini, if you have installed the driver the details should have been maintained inside this ini file.

11. Which possibilities exist to create a remote source on HANA server?

In SAP HANA Studio, there are two ways to create remote data sources, one is by GUI, another is using SQL statement.
Creation via GUI is explained in the section: Adding Remote Data Sources in SAP HANA Administration Guide
Optionally, you could create remote data source with SQL as shown below:

CREATE REMOTE SOURCE  <src_name> ADAPTER <adapter_name> [CONFIGURATION FILE '<file_name>']
CONFIGURATION <connection_info_string> [opt_credentials_clause]

12. Will .ini files be created on HANA server when remote sources are configured?

Yes, there will be .ini files created with prefix "property_<DB_Name>.ini"
You will find it on HANA server under "$DIR_INSTANCE/../SYS/global/hdb/custom/config"

13. Can I also use Smart Data Access for updating data in remote sources?
Yes, once you have configured Smart Data Access you can read as well as update the data using virtual tables in remote sources.
Virtual table can be manipulated by SAP HANA just like an ordinary table, which means the operations, such as select, update, insert, delete, and so on, are all available for virtual table.

14. While trying to create new remote source, I get the error message “Parameter: Password cannot be empty”. How can I resolve the issue?

To make the field recognize the password, you have to click on the User name after you input the password. Then click on the Password field, now try to save it.

Keywords

SDA, Smart Data Access, SAP SDA

Header Data

Released On	23.06.2015 11:51:25
Release Status	Released to Customer
Component	HAN-DB SAP HANA Database
Priority	Normal
Category	How To