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

http://help.sap.com/hana_platform
 

Overview Architecture

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

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

Check mark ok

 
Execute

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

With the following syntax

Enable Data Provisioning server for SDI

When Hana is installed, by default the DP server is note activate, in order to have the ability to use SDI it needs to be enabled. The value needs to be change to 1

Enable Script server for SDQ

To take advantage of the SDQ functionality the script server value needs to be change to 1

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.

Once downloaded decompress it at the following location:
/usr/sap/<SID/SYS/global/hdb/IM/reference_data

 
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)

Upload from the studio

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

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

 
Run and installed it as needed

Once installed open the cockpit agent

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

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 ‘Driver=libdbodbc16_r.so;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

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

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

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

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

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.

Williams.