The data universe is constantly expanding with a myriad of data sources and topics from standard IT applications to Hadoop deployments to Internet of Things data stores, and so on. While the data may be ever-changing, we at SAP want to ensure that you and your organization can successfully navigate the data universe. Besides real-time in-memory data management and hybrid transactional/analytical processing (HTAP), SAP HANA users must be able to effectively operate in the world of rapid data virtualization.
What does data virtualization with SAP HANA allow you to do? It helps you locate, access, and manage whole sets of diverse data stores for end-users, virtualization tools, and custom data applications all in a single environment. And one of SAP HANA’s tools to help you successfully connect and understand your data is HANA Smart Data Access.
HANA Smart Data Access, or SDA, enables data virtualization by allowing you to bring a real-time snapshot of your data into an in-memory environment while leaving the original data set where it natively resides. This preserves the integrity and security of the data because SDA doesn’t copy or modify the original data set.
How do we do that? We identify the remote source and map the relevant data as virtual tables. Here’s what the basic syntax looks like:
CREATE REMOTE SOURCE <remote.source.name> ADAPTER <remote.adapter>
CONFIGURATION 'DSN=<name>'
WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=<user_name>;password=<password>';
CREATE VIRTUAL TABLE [<schema>.]<virtual_table_name> AT
<remote_source>.<database_name>.<schema>.<table_name>;
From here, we can access data from our remote data source. Of course, sometimes we need to deal with situations where we have hundreds, if not thousands, of data sources. Creating virtual tables for each data source can quickly become cumbersome. Therefore, SDA offers the
linked database feature. This feature eliminates the need to define virtual tables one at a time. Instead, you query the remote server directly, saving time and effort as your data landscape increases. With the linked database approach, we can implicitly see all the tables in the underlying database. Let’s look at this example:
CREATE REMOTE SOURCE <remote.source.name> ADAPTER <remote.adapter>
CONFIGURATION 'DSN=<name>'
WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=<user_name>;password=<password>';
GRANT LINKED DATABASE ON REMOTE SOURCE <remote.source.name> to <user>;
CREATE CREDENTIAL FOR USER <user> COMPONENT 'SAPHANAFEDERATION' PURPOSE '<remote.source.name>
TYPE '<password> USING 'user=<user>;password=<password>';
Once you connect to the remote source and grant your user the proper permissions and the proper credentials, you can access multiple remote sources without defining individual virtual tables. Call your remote sources like this:
SELECT * FROM <remote_source1>.<remote_schema1>.<remote_table1> <remote_source2>.<remote_schema2>.<remote_table2> ...;
With the SAP HANA 2 SPS 03 release, linked database now features
optimized mode for SAP HANA to SAP HANA workflows. When optimized mode is enabled, the referenced remote objects are cached locally. Metadata and statistics for remote objects are fetched and refreshed automatically resulting in better query performance. To enable this mode, set the
linkeddatabase_mode property when creating a remote source. Here’s an example:
CREATE REMOTE SOURCE <remote.source.name> ADAPTER <remote.adapter>
CONFIGURATION 'DSN=<name>;linkeddatabase_mode=optimized'
WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=<user_name>;password=<password>';
As we have seen, the SDA features allow you to run queries against an entire database (with linked database), or at a granular level (with individual virtual tables). With these flexible approaches, coupled with parallelized data access to the remote data sources, SDA provides your business with an efficient and comprehensive method of querying heterogeneous data.
SAP HANA and SDA will be an architectural cornerstone for successful organizations navigating the expanding data universe. As these organizations move to connect data between on-premise and cloud deployments, and connect within and across cloud infrastructures, the ability to successfully interpret and manage it all will be key. With SAP HANA and SDA as your guides and means to understand your data universe, the
data-rich future will look much more welcoming.
