Skip to Content
Author's profile photo Sunny Pahuja

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:

  1. SAP Oracle Database 12c, SAP Sybase Event Stream Processor (ESP) 5.1 SP04 and MSSQL server 2012 version 11 are supported as data sources.
  2. SDA now supports insert/update/deletion for remote databases with some limitations.
  3. SDA now supports BLOBs and CLOBs as column data type of virtual table.
  4. SDA has a new feature which will help to check whether SDA installation is done successfully or not.
  5. 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.

Assigned Tags

      17 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Juan Reyes
      Juan Reyes

      Nice blog Sunny!

      Author's profile photo Former Member
      Former Member

      Good Information on Smart Data Access.

      It is very useful when we are cross checking the data.

      Author's profile photo Former Member
      Former Member

      Nice info.. đŸ™‚

      Author's profile photo ramesh vankudoth
      ramesh vankudoth

      Nice document....

      Thanks,

      Ramesh

      Author's profile photo Viren Pravinchandra Devi
      Viren Pravinchandra Devi

      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)?

      Regards,

      Viren

      Author's profile photo Sunny Pahuja
      Sunny Pahuja
      Blog Post Author

      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.

      Thanks,

      Sunny

      Author's profile photo Lyna TANG
      Lyna TANG

      Hi Sunny,

      Is there any documention or SAPNote informing customers to choose the charset/collation in HANA and in IQ carefully?

      For ex:

      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!

      Lyna TANG

      SAP AGS Product Support for IQ

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Abhijit Gujar
      Abhijit Gujar

      Thanks sunny  good informatiomn to understand SDA

      Author's profile photo Former Member
      Former Member

      Sunny,

         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!

      Anand

      Capture.PNG

      Author's profile photo Sunny Pahuja
      Sunny Pahuja
      Blog Post Author

      Hi Anand,

      Did you get a chance to look at SAP KBA 1761917 which describes this issue?

      Thanks,

      Sunny

      Author's profile photo Former Member
      Former Member

      Sunny,

      Smart data Access feature , Can we suggest it on top of OLTP system. Will it affect the transactions anyway? (Deadlocks).

      Author's profile photo Ariel Manrique
      Ariel Manrique

      Hi all!

       

      Is there an updated list of supported DBs as remote sources in HANA?

       

      Thanks in advance for your answers!

       

      Author's profile photo Former Member
      Former Member

      Hi!

      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.

       

      Thank you!

      Author's profile photo Anastasia Maher
      Anastasia Maher

      Any solution to INSERT  into virtual table ?

      Author's profile photo Luis Olivera
      Luis Olivera

      Thanks for the information.!

      Best regards.

      Author's profile photo Sagar Neil Barman
      Sagar Neil Barman

      Excellent information Sunny !!