Wednesday 23 December 2015

SAP Hana EIM (SDI/SDQ) setup

In my documentation I’ll explain how to setup and configure SAP Hana SP10 EIM (SDI/SDQ) with Sybase IQ database and ERP on Hana database schema source system to replicated data for realtime data replication.

I will show in detail step and configuration point to achieve it.

In order execution
  • Create Sybase IQ database
  • Enable DP server for SDI
  • Enable Script server for SDQ
  • Install SDQ cleanse and geocode directory
  • Install DU HANA_IM_DP (Data provisioning)
  • Install and Register Data Provisioning Agent
  • Create remote source
  • Data replication and monitoring
Configuration required on SP9
The xsengine needs to be turn to true (if not done)
The statistic server needs to be turn to true (if not done)
The DU HANA_IM_ESS needs to be imported
Guide used
SAP Hana EIM Administration Guide SP10
SAP Hana EIM Configuration guide SP10

Note used
179583 - SAP HANA Enterprise Information Management SPS 10 Central Release Note

Link used

Overview Architecture

SAP HANA EIM and Certifications

Starting Hana SP9 the new features call SDI (Smart Data Integration) and SDQ (Smart Data Quality) has been introduce.

The purpose of these new features is to leverage an integrated ETL mechanism directly into Hana over SDA

To make it simple:
  • Smart Data Integration provide data replication and transformation services
  • Smart Data Quality provide an advanced transformation to support data quality functionality
Create Sybase IQ database

In order to have a dedicated database to work with I’ll create my own database into IQ server:

From the SCC go to administration and proceed as follow


SAP HANA Certifications and Material

SAP HANA SDI/ SDQ and Certifications

SCC agent password: the password define during the IQ server installation
Utility server password: auto fill do not change it
IQ server port: use an unused port, I already have 2 db running so I pic the next number
Database path: <path where the db is stored><dbname>.db
IQ main dbspace path: <path where the dbspace is stored><dbname>.iq

SAP Hana EIM (SDI/SDQ) and Certifications

Check mark ok



SAP Hana EIM (SDI/SDQ) and Material

SAP Hana EIM (SDI/SDQ) Tutorial

SAP Hana EIM (SDI/SDQ) and Certifications

My database now available I’ll create 3 simple tables for this test, I’ll use interactive SQL

SAP HANA Certifications and Material

With the following syntax


Install SDQ cleanse and geocode directory

The Cleanse and Geocode nodes rely on reference data found in directories where we download and deploy to the SAP HANA server.

To download those directories go on the SMP and select the one you need
You can download several directories depending on what you are licensed for.

SAP Hana EIM (SDI/SDQ) and Certifications


Once downloaded decompress it at the following location:

SAP Hana EIM,SDI/SDQ and Certifications

Install delivery unit HANA_IM_DP (Data Provisioning)

The specific delivery unit needs to be downloading and upload from the studio or the web interface, this will provide you:
  • The monitoring functionality
  • The proxy application to provide a way to communicate with the DPA (cloud scenario)
  • The admin application for DPA configuration (cloud scenario)
SAP Hana EIM (SDI/SDQ) and Material


Upload from the studio

SAP Hana EIM (SDI/SDQ) and Certifications

Once done assign the monitoring role and add the view from the cockpit

SAP Hana EIM (SDI/SDQ) and Certifications

SAP HANA EIM, Certifications and Material

Install and register Data Provisioning Agent

The Data Provisioning Agent is used to make the bridge between Hana and source system where the driver can’t be run from Hana (DPS) over a pre-build adapter, in some case it allow Hana to write back data into source system.
Use the DPA allow live replication.

The agent is part of the package download earlier

SAP HANA EIM, Smart Data Integrations

Run and installed it as needed

SAP HANA EIM And Material

Once installed open the cockpit agent


Make sure the agent is started, connect and register it to Hana with the necessary adapter

SAP HANA Tutorial, Material and Certifications

Let create the source system in Hana now.
Create remote source

Now that my IQ db is in place and my Hana adapter is installed I will create my source system in SDA where I need to get the data from.
Let start with my IQ database, before create the connection in SDA install and set the lib on Hana server. To create my connection I will use the following statement:

create remote source I841035 adapter iqodbc configuration ';ServerName=HANAIQ03;CommLinks=tcpip(host=usphlvm1789:1113)' with CREDENTIAL TYPE 'PASSWORD' USING 'user=I841035;password=xxxxxxx';

Once done refresh the provisioning


And create the ERP on Hana schema source system by selecting the adapter added earlier

SAP HANA Tutorial and Certifications

SAP HANA EIM, Certifications, SDI/SDQ and Material

And check the remote subscription form the cockpit


Data replication and monitoring

My remote source connect I will now define which table I want to replicate and how I want it to look like once loaded.
Make sure your user schema is part of _SYS_REPO with “CREATE ANY” granted. 
From the development workbench go to “Editor” and select your package and create a new replication task

SAP HANA EIM And Certifications


And fill the necessary information, target schema, virtual table schema, table prefix and so on.
From detail perspective several option are possible

Add/remove/edit table

SAP HANA EIM and Certifications

Set filter

Define the load behavior in order to have a certain level of detail on the change that encore on the table.


Partition data for better performance


Once you preference are set, save the configuration to activate it.


From the monitoring side check the task log

SAP Hana EIM (SDI/SDQ) and Certifications

Once activate go on the catalog view and check if the procedure is crated as well as the virtual tables/views and table, and invoke the procedure to start the replication


I did repeat the same procedure for my ERP on Hana schema, once the procedure is invoked on the remote Hana db we can see additional table created and trigger for the relevant table replicated


From a monitoring side, I did add 4 additional user and we can see the apply count

SAP Hana EIM (SDI/SDQ) and Certifications

The replication is now operational, in my next document I’ll explain how to configure several datasource and construct one realtime report with the input of different table.


No comments:

Post a Comment