Skip to Content
Author's profile photo Former Member

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. NLS6.JPG


Solution Overview

NLS5.JPG

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 SourceNLS1.JPG
    2. Create a virtual Table based on an existing Table located in the IQ ServerNLS2.JPG
    3. The Table is now visible in the SAP Schema of the BW on HANA SystemNLS3.JPG

     4. Grant Remote Source privileges to SAP user In SQL

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

          NLS4.JPG


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

Ajay Kumar

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi ajay!

      Very useful your Doc!!, please be kind an accept a couple of question:

      1- How to know the DAP which entries is warn data to be Archived?

           a- NLS/SDA automatically, when request is done

           b- we would need to do SQL script

           c- other... please comment

      2- after DAP is done, how HANA refresh data in memory?

           a- NLS into a process chain ABAP activity remove the data in memory from HANA

           b- we would need to do SQL script, then a FM called from ABAP and finally in PC

           c- other... please comment

      3- Who says to HANA when to use Virtual Table or Regular Table?

           a- the query NLS setup

           b- the NLS setup after request

           c- other... please comment

      4- Whether we must to create a remote source connection from HANA to IQ, then the solution overview image must be an arrow indicating that connection? or I'm wrong?

      5- the last one, could you please tell me how to do the same but in ECC platform?

           a- would be - NLS-SDA-HANA-IQ

           b- or SARA-SDA-HANA-IQ

           c- other... please comment

      Thanks in advance

      Regards

      Hercar

      DAP = Data Archiving Process

      PC = Process Chain

      Author's profile photo Roland Kramer
      Roland Kramer

      Hello,
      Nice Article

      See also the SAP First Guidance Document for the Implementation of the SAP-NLS Solution wich also explains the SDA setup in detail.

      Best Regards Roland

       

      Author's profile photo Martin Lubitz
      Martin Lubitz

      Hi Roland,

      trying to set up this, but getting the error "
      SAP DBTech JDBC: [403]: internal error: Cannot get remote source objects: [Sybase][ODBC Driver][SQL Anywhere]Database server not found"

      What is mean with "EngineName"

      Regards

      Martin

       

      Author's profile photo Symon Braunbaer
      Symon Braunbaer

      > grant create virtual table to sdiuser
      * 334: invalid user privilege: CREATE VIRTUAL TABLE: line 1 col 7 (at pos 6) SQLSTATE: HY000