Demystifying Smart Data Access in SAP HANACategory: SAP HANA Posted:May 08, 2017 By: Ashley Morrison
The major challenges that today’s businesses encountering are receiving real-time information to take a quick conclusion on time and controlling over cost for technology and IT. Business is in need to be agile as well as dynamic to retain pace with the technology and market. When it comes to business view, SAP has introduced Smart Data Access (SDA)in SAP HANA that is nothing but a virtualization technique. SDA facilitates the HANA to integrate data from various heterogeneous sources like Sybase IQ, Teradata, Hadoop and SAP Sybase Adaptive Service Enterprise. In order to demystify SDA in the SAP HANA, it is essential to have a brief knowledge of SAP HANA data access.
SAP HANA is defined as the column-based relational DBMS with new in-memory expertise. The memory access is faster than the disk access. Because the entire data is retained in memory, it is 10 to 1000 times faster when compared to the conventional disk-optimized databases. This technique facilitates the high processing of high-volume real-time data such as transactional and analytical. It offers several different layers, which cover the whole solution of the company within a solo installation; hence, there is no wonder of stating it as a platform instead of just a database. The following figure illustrates the concept of SDA:
The HANA can be utilized in a typical 2-tier and 3-tier architecture. The 2-tier architecture includes an application server kept into the SAP HANA Extended Application Services (SAP HANA XS) and the 3-tier architecture includes a client, a database layer, and an application layer. In addition, to the full-featured application server, HANA Extended Application Server embeds a development environment and a Web Server within the HANA appliance itself. HANA Extended Application Server is not a separate software; it is actually an addition, which is closely merged into the HANA. It is available as in the Cloud, On-Premise, as Platform-as-a-Service (PaaS) or as a Hybrid, which integrates On-Premise and Cloud instances. Let us explore deep into the SAP HANA SDA.
SAP HANA SDA
The SAP HANA SP6 version comes with a key feature called Smart Data Access which allows the customers to integrate data from SAP HANA with other heterogeneous sources.
SDA, an expertise that facilitates remote data access in a way as local tables in the SAP HANA without duplicating data into SAP HANA. That technology allows the data from the other sources persist in virtual tables. Hence, the virtual tables will socket to remote data tables in various sources. This feature will enable to access the real-time data effortlessly regardless of its location without affecting the SAP HANA database. Customers can write the SQL queries to access the data on the SAP HANA virtual tables. In addition, they can execute the particular part of the SQL query in the database, proceeds the query results to SAP HANA and finishes the operation.
Apart from exposing data from various remote sources in the form of virtual tables, SDA allows to combine these data with data resides in the HANA physical tables using HANA models. The feasibility to apply HANA planning, predictive as well as text search algorithm with the combined dataset provides an outstanding benefit to the customers and business. The benefits of this capability include operational advantages, cost advantages as well as support in the analytical application development and deployment that needs the capability to access and combine data from various systems in the real-time irrespective of the source and location of the data.
Data types of the virtual tables are mapped to HANA data types. They’re then handled as the local tables of the SAP HANA. Once the connection to the remote source has been initiated, the queries can execute against the virtual tables and compile with the other available physical tables of the SAP HANA or other Smart Data Access sources.
Smart Data Access – Key Ideas
SAP HANA Smart Data Access is nothing but a technology that enables data access from the various alternative sources as if they’re local tables in the SAP HANA without replicating them into HANA. It is based on the local virtual tables, which maps to available objects on the alternative data source site. The following are the supported remote sources by HANA SDA as for HANA SPS 11:
- Sybase IQ: Version 15.4 ESD#3 & 16.0
- Oracle 12c
- Microsoft SQL Server 2012
- IBM DB2 LUW Version 10.1
- Teradata Version 13, 14
- Sybase Adaptive Service Enterprise: Ver 15.7 ESD#4
- Netezza Ver7
- SAP Sybase ESP 5.1, SP04
- SAP MII Ver 15.0 (SAP Note 1984859)
- SAP HANA (Suite on HANA, BW on HANA)
- Spark SQL
- MaxDB 7.9
Limitation on the Usage of the SDA
- Initially, it is not possible to modify the data present in the virtual tables. The operations like update, delete and insert are not applicable to the virtual tables. Selecting data in the virtual tables and putting the data on the SAP HANA table is the only actions that are supported by SAP HANA Smart Access Data.
- The virtual table does not support BLOB/CLOB data type. The workaround for this issue is to make a view on the isolated table by the way of excluding BLOB/CLOB type column and then make a virtual table with the support of the view.
- Virtual tables can’t be utilized in the multi-node SAP HANA cluster. Though, we can access the virtual tables from one of the SAP HANA server to the other HANA server.
- Some of the analytical view doesn’t support the virtual tables.
Based on the HANA SPS7, the following major changes are made for the Smart Data Access:
- SAP Sybase Event Stream Processor (ESP)5.1 SP04, SAP Oracle Database 12c and MSSQL server 2012 Version 11 are supported in SDA as data sources.
- Insert, update and deletion operation on the remote databases are now supported with some restriction.
- Now SDA supports CLOBs and BLOBs as the column data type in the virtual table.
- SDA includes a new feature that will support to evaluate whether installation of SDA is completed successfully or not.
- SDA now provisions remote caching on the Hive.
Activities that can perform with SDA
Here are some of the sample activities, which can be performed with the HANA SDA:
- Consume data mart scenarios of the SAP HANA from the connected database.
- Consolidate the data warehouse landscape.
- Create another data warehouse absolutely transparent for the SAP BW/4 HANA database of the BW system.
Advantages of Smart Data Access
When utilizing HANA SDA for accessing query to the data present in Sybase IQ as a source for ODS views and as a storage solution, it provides the following benefits:
- SAP HANA SDA improves the execution of the queries by shifting processes farther to the database associated through SAP HANA SDA. Then the SQL queries function in HANA on the virtual tables. The query processor of the SAP HANA improves the SQL queries as well as executes the relevant portion of the associated database, completes the operation and returns the result to the HANA.
- In case you connect as well as define master data and facts from the source through a connection in the open ODS view, master data and facts are merged directly at the level of the database at query runtime, organized with functionalities on the steering attributes.
- For the view of Open ODS, SAP HANA Smart Data Access creates it feasible to use data sources, which are not handled by the BW system. It is applicable when performing this to enlarge modeling in the SAP BW system without any important effort, from direct data source consumption through the virtual table to utilize a determined BW data for the purpose of ETL.
Generic Adapter Framework
With the support of Smart Data Access, SAP HANA can interconnect with the remote data sources who provisions the ODBC protocol. However, the supported data source of HANA Smart Data Access has still includes some restriction now. For the supported dedicated data source, SAP HANA has offered native code to provision their functions. However, HANA SDA cannot guarantee the other data sources to function well. The main factors that limit SAP HANA SDA from provisioning more ODBC data sources include some configuration and operation of these ODBC data sources cannot be processed by the interface of the standard ODBC. For instance, preparing a transaction for the Sybase ASE needs some additional code that is not comprised in the standard ODBC protocol. Since HANA offers such code, the function of the Sybase ASE is maintained.
In the motive to reduce the influence of this problem, SAP HANA SDA put in the Generic Adaptor Framework to instrument the communication with the unsupported ODBC data sources as a substitute of calling the dedicated native code for the SAP HANA data source. With the support of the Generic Adapter Framework, it is possible to customize the action and feature of the data source by tuning configuration file. For instance, it is possible to specify a function mapping, supported operations and data type mapping in the configuration file. Once the data source is created, HANA Smart Data Access will describe the relevant property config file, entire features, data type mappings and function mappings. In addition, the other properties will be associated organized with the data source and effects the communication between the data source and SAP HANA.
Common Use Cases for SDA
The following are some of the common use cases for the SDA:
- The main use cases for the SAP HANA SDA is archiving data in a different source and retaining active data in the SAP HANA.
- It is possible to access IQ in the way of archived storage in order to store real-time hot data in the HANA and cold data in the IQ.
- Create applications by the way of running SAP HANA models across different data sources.
- Overcome challenges of Big Data by linking to Hadoop by means of Hive interface.
The major key technology components, which support this combination, are features like functional compensation that allows aggregations, filter pushdowns and semi-joins to the heterogeneous sources; hence, the most of the operations are carried out at the remote or heterogeneous source and only minor datasets are moved across the remote network for joins in HANA. In addition to this, the functional translation offers the capability to handle the syntax variations between the remote source and SAP HANA.
By extending SAP HANA studio in order to support security requirements and virtual tables for accessing these remote sources, the customers of HANA will be capable of extending available data models and creating new data models to experience the benefits of this new aspect. The monitoring aspects of the Smart Data Access offer statistic details on how long it acquired for the SQL query to execute on the remote or heterogeneous source. It also offers Query Plan in order to visualize the plan to access the remote source.
Smart Data Integration
The Smart Data Integration abilities of SAP HANA are utilized for data migration, analytics and real-time or batch integration from various multiple sources. The real-time duplication is also accessible and supported by an RSS adapter. The transformation of Smart Data Integration comprises basic as well as advanced SQL oriented transformations, code execution transformation, and transformation, which handle the data movement life cycle. Advanced transformation of Smart Data Quality provisions data quality functionalities like standardizing, parsing and enriching the details on the dimensional attributes and global address cleansing. Apart from this, it also supports geocode enrichment for nations worldwide.
The flow graph is nothing but a development object, which is utilized for making data flows with Smart Data Quality transformation and Smart Data Integration. Modeling of flow graph begins with its formation and ends with activation as well as the execution of a stored procedure or generated task.
Because of its powerful capabilities and appearing as an in-memory solution, SAP HANA SDA fully surpasses conventional RDBMS and facilitates the business of the forthcoming years. SAP HANA Smart Data Access along with SAP Smart Data Quality and SAP Smart Data Integration features simplify Information technology environment as well as stimulates the performance considering the whole thing is completed under the seamless platform and is offered by the strong capabilities of the SAP HANA.