How to setup Smart Data Access (SDA) for Near-Line Storage in SAP IQ

Hello !

We will see here how to setup SDA (Smart Data Access) to access my data in SAP IQ – NLS(Near Line Storage). Assuming that we are running an SAP HANA database (>SPS06).

SMART DATA ACCESS

In SAP BW, SAP HANA Smart Data Access is used as the source for Open ODS views and for query access to data in Sybase IQ as a near-line storage solution. With SAP HANA Smart Data Access, data can be merged in heterogeneous EDW landscapes (data federation).SAP HANA Smart Data Access make it possible to access remote data, without having to replicate the data to the SAP HANA database beforehand. The following are supported as sources: Teradata database, SAP Sybase ASE, SAP Sybase IQ, Intel Distribution for Apache Hadoop, SAP HANA. SAP HANA handles the data like local tables on the database. Automatic data type conversion makes it possible to map data types from databases connected via SAP HANA Smart Data Access to SAP HANA data types.

The following are example of activities that you can perform using SAP HANA Smart Data Access:

• Make other data warehouses transparent for the BW system’s SAP HANA database.
• Consolidate your data warehouse landscape.
• Consume SAP HANA data mart scenarios from a connected database.

When using SAP HANA Smart Data Access for query access to data in Sybase IQ as a near-line storage solution and as a source for Open ODS views, it offers the following advantages:

• SAP HANA Smart Data Access optimizes execution of queries by moving processing as far as possible to the database connected via SAP HANA Smart Data Access. The SQL queries then work in SAP HANA on virtual tables. The SAP HANA Query Processor optimizes the queries and executes the relevant part in the connected database, returns the result to SAP HANA and completes the operation. If you connect and define facts and master data from the source via associations in the Open ODS view, facts and master data are joined directly at database level at query run-time, together with operations on the navigation attributes.
• For Open ODS views, using SAP HANA Smart Data Access makes it possible to use data sources that are not managed by the BW system. It is possible when doing this to expand modeling in the BW system without significant effort, from direct consumption of the data source via the virtual table to using a persistent BW data store for ETL purposes.

Near Line Storage(NLS)

To begin with, SAP will take complete control of persistence management for its Business Warehouse and will offer an SAP owned NLS implementation for Sybase IQ. Instead of having to rely on partner solutions, customers will then receive all the components required for running an EDW directly from SAP, including the DWH application (SAP NW BW), the databases (SAP HANA DB and SAP Sybase IQ) and an intelligent NLS management solution.
SAP is pursuing a three-step color-coded persistence management concept.
Active data that has to be accessible on a permanent basis for read and write processes is referred to as ‘hot’ data. As would be expected, this data is stored in the main memoryof the HANA DB.
Warm data, which is not accessed quite so often, is best stored in the HANA DB file system. Whenever this data is loaded into the main memory for processing, it is displaced with higher priority once processing has finished.

To achieve this, the BW development team has developed a non-active data concept in order to optimize the displacement strategy for BW tables in the HANA DB. Here, tables with warm data are flagged and are prioritized for displacement from the main memory whenever they are used. These tables are normally partitioned in the default setting, and only the partitions of the table that are affected by the transaction are loaded to the main memory.

Solution Overview

Prerequisites:

• SAP HANA SPS 06 and later
• The technical content must be installed in the SAP BW system
• Need to have following authorization in SAP HANA…
  • System privilege: CREATE REMOTE SOURCE
• 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…
  • Object privilege: CREATE VIRTUAL TABLE on VIRTUAL_TABLES (SYS)
  • Object privilege: DROP on VIRTUAL_TABLES (SYS)

Procedure:

1. Create a remote source in SAP HANA Studio under System –>Provisioning –>Remote Sources –>New Remote Source
2. Create a virtual Table based on an existing Table located in the IQ Server
3. The Table is now visible in the SAP Schema of the BW on HANA System

     4. Grant Remote Source privileges to SAP user In SQL

           GRANT CREATE VIRTUAL TABLE, DROP ON REMOTE SOURCE “SAPHBW_NLS” TO “SAPHBW”;

Hope it’s worth. Thank you…Cheers !!

Ajay Kumar