Smart Data Access- A new feature by HANA
In Today’s world, Companies are facing challenges to optimize cost and processes in order to sustain in this grim economic condition. Business has to be dynamic and agile to keep pace with the market and technology. Business has to get information in real time to make quick decision on time and at the same time, we need to keep control over cost for IT and Technology.
Keeping Business need in view, SAP has recently introduce Smart Data Access in SAP HANA which is a Virtualization Technique. This feature is introduced from SPS6 in SAP HANA. Smart Data Access enables SAP HANA to combine data from heterogeneous sources like Teradata, Sybase IQ, SAP Sybase Adaptive Service Enterprise and Hadoop.
Smart Data Access is a technology which enables remote data access as it they are local tables in HANA without copying data into SAP HANA. 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 effect 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.
There are few 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.
For more information about Smart Data Access in HANA, you can refer to SAP note 1868209.
As of SAP HANA SPS7, there are some major changes for Smart Data Access which are as follows:
- SAP Oracle Database 12c, SAP Sybase Event Stream Processor (ESP) 5.1 SP04 and MSSQL server 2012 version 11 are supported as data sources.
- SDA now supports insert/update/deletion for remote databases with some limitations.
- SDA now supports BLOBs and CLOBs as column data type of virtual table.
- SDA has a new feature which will help to check whether SDA installation is done successfully or not.
- SDA supports remote caching on Hive.
For more information, please refer to What’s New in the SAP HANA Platform SPS7
Disclaimer: This Blog is the personal Blog and only contains my personal views, thoughts and opinions. It is not endorsed by my employer nor does it constitute any official communication of my employer.
Nice blog Sunny!
Good Information on Smart Data Access.
It is very useful when we are cross checking the data.
Nice info.. 🙂
Thanks for doc Sunny. I think this loks like more of integration feature and may not use any In memory features except columner tables (virtual tables)?
It will in fact help enterprise to analyze data from different sources at real time and without effecting HANA because data will not be in HANA system which will not impact performance of HANA Box.
Is there any documention or SAPNote informing customers to choose the charset/collation in HANA and in IQ carefully?
IQ is created with collation 1251CYR (single byte coding)
HANA is created with collation Unicode (multi-byte coding)
In both databases, I have a table with 1 column of varchar(14).
If I have a value of 14 cyrillic charaters in IQ and transfer them to HANA, I will only get 7 caracters in HANA.
This is because a cyrillic char is coded on 1 byte in 1251CYR and on 2 bytes in Unicode.
For collation compatibility and convenience, I would strongly customers
to have the same collation for the databases between which data are transfered.
What do you think? Do you have other advises?
I have complains from a customer that no doc can be found on this.
Thanks for your help!
SAP AGS Product Support for IQ
Any more information on how Data Federation works? Does it have any clever caching capabilities?
Busienss Object's Data Federator has been included within the BI4 platform (within the unx layer) - will HANA include any of this functionality?
Thanks sunny good informatiomn to understand SDA
This is great information. I'm searching for the appropriate authorization and privileges required when using smart data access virtual tables. I currently have a SDA setup that is pulling in virtual tables. I am trying to build a calculation view on these tables and I continue to receive an error message stating that the create scenario failed and the user is not authorized (2950). Do you happen to know which privileges are required for me to complete this development? Thanks for your help!
Did you get a chance to look at SAP KBA 1761917 which describes this issue?
Smart data Access feature , Can we suggest it on top of OLTP system. Will it affect the transactions anyway? (Deadlocks).
Is there an updated list of supported DBs as remote sources in HANA?
Thanks in advance for your answers!
First of all: Thank you Sunny for this very usefull article.
Does you or anyone else know how to prepare a virtual table for changes (update, insert, delete). The external view in our system has currently the maintenance status "read" and you can not enter the change mode in the system to adjust this option.
Any solution to INSERT into virtual table ?
Thanks for the information.!
Excellent information Sunny !!