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
SAP Hana EIM Administration Guide SP10
SAP Hana EIM Configuration guide SP10
179583 – SAP HANA Enterprise Information Management SPS 10 Central Release Note
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
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:
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
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.
Another great document, Williams. Keep it up, man.
Hi Williams Ruter
While importing the HANA_IM_DP Delivery Unit, I am getting the following error
"A SQL error occurred while creating a SQL view" .
Kindly let me know the solution.
Thanks and Regards,
Which version of Hana are you on ?
If you are on SP10 use the DP from the "install and upgrade path"
If you are on SP9 use the DP SP00 from "support pack and path"
I was successful in importing the DP SP00, but when i try to register my Adapter to the agent i get the following error.
Thanks and Regards,
Make sure hana and the dp agent server talk to each other with hostname not ip.
Please le me know if you still have issue.
Whilst importing the HANA_IM_DP Delivery Unit, I'm getting the following error "A SQL error occurred while creating a SQl view".
The import of DU HANA_IM_ESS is also ending with errros.
The HANA version is SP9 (1.00.097).
What do you suggest in this case?
Which package did you download ? because if you are on SP9 use the DP SP00 from "support pack and path".
Please let me know if any issue.
Thank you for the fast reaction.
The import of DU HANAIMDP00_0-70000027.zip (suppport packages and patches) worked. The SP1 - HANAIMDP01_0-70000027.zip must also be imported?
The import of DU HANA_IM_ESS is still not working.
Any magic left?
Don't install SP01, it's belong to SP10. ESS is only necessary for ADP so you don't need to deploy it.
Thank you. Now I have another issue when registering the adapter:
The adapter is installed on Linux and the underlying DB is an Oracle 11g.
Did you install the jdbc library of Oracle into the /lib folder of the DP Agent ?
Could you please clarify the following things for me:
1. Oracle JDBC library -> are you reffering to the JDBC driver(.jar file ) or the JDBC oracle library (Kernel library)
2. is Oracle 11 supported?
3. Step "Create remote source": is smart data access used?
1 - I'm referring to the "Oracle JDBC driver" upon your db release that you can find from Oracle website
2 - Oracle 11 is supported
3 - you create your remote source form SDA
Let me know if you have any issue
Which DP Agent should be installed for HANA SP9?
You need to go in "support pack and patch" and pickup the following on
I've installed the DPAgent SP9 but I'm getting the following error message when trying to deploy the adapter:
Hello Marian,You want to deploy a new adapter or register an existing one ?
I want to deploy the JDBC library for Oracle (like specified in your post above: JDBC/UCP Download Page). The jar file was copied in the /lib dir.
If the jar. file are in the lib directory so you are good, also according your print screen above it appear that your oracle adapter are already registered and deployed.
The deploy adapter button is if you want to deploy customer adapter that you have created.
When creating the Remote source I'm getting the following error:
SAP DBTech JDBC: : internal error: Cannot get remote source objects: exception 151040: No agent was found for adapter "XXX".
Also when expanding the newly added remote source, the same error is visible.
The error is based on which adapter ?
The error is based on the Hana Adapter.
If I would select another adapter name I would not be able to select the agent in the field Source Location.
can you send me your framwork.log and configtool.log please.
Could you please provide me your email address?
there it is : firstname.lastname@example.org@sap.com
Thanks for this blog.
I have done all the required things to use SDI with SQL Server remote source.
I was successful to 'connect to HANA' from dp agent without using 'Hana on Cloud' option but using the 'db tunnel' or cloud connector. Or in other words, using the TCP protocol and creating the inbound firewall rule for the port 5050.
But actually my HANA is on cloud (HCP). Also it is not always possible to open port in customer db server
Hence I followed your blog and downloaded & imported HANA_IM_DP (To be exact, HANAIMDP00_0-70000027.zip as I am on SPS09).
Now I need to connect to HANA by using 'HANA on cloud' so that no port needs to be open.
Could you please let me know below 2 things related to 'Connect to HANA' in dp agent,
1) Where to get proxy details like (Port name, Port, User & Password)
I can access 'https://myaccount.hana.ondemand.com/sap/hana/im/dp/proxy/dpproxy.xsjs' but it gives no required info. It gives the below;
2) Is HANA hostname to be provided with account-name (acb12.hana.ondemand.com where acb12 is account-name) or without it
For the first point, the proxy information is mandatory only if you need to go through a proxy to access your system over the web, in this case this information needs to be provide by your network team overwise uncheck the box.
For the second point, you just provide your hana hostname, user name, port and password. Do not use "acb12.hana.ondemand.com"
Feel free to let me know if you have any issue or question
Thanks Williams for the details.
I tried as below but ended up in error ,
In webdispatcher.ini, 'icm/server_port_0' is set as 'PROT=HTTP,PORT=8000,PROCTIMEOUT=600'
Make sure the port is open between your agent and Hana, use telnet command or http to test it :
telnet hana.ondemand.com 8000
Please let me now if any error or question
Thanks Williams for the reply.
Got the below response for telnet hana.ondemand.com 8000
"Connecting To hana.ondemand.com...Could not open connection to the host, on port 8000: Connect failed."
hana.ondemand.com being the landscape host, I am not sure it will ever allow this connection. Are you sure we can connect with just HANA Host name as 'hana.ondemand.com' and without proxy details for 'HANA on cloud'? Then what is the need of importing the HANA_IM_DP and that proxy service ('https://myaccount.hana.ondemand.com/sap/hana/im/dp/proxy/dpproxy.xsjs')?
My webdispatcher.ini is attached below. Also I am not very sure what port to be used in this case for the 'Connect to HANA' in dpagent.
First change you webdispatcher configuration, because you will point nowhere if the value is "0", reverse it to the default port or provide a custom open port.
If you reverse to the default port "8000" please try to connect on your hana instance over http by usining it., if it works then the proxy parameter is not needed.
The DU HANA_IM_DP provide roles in order to allow you to make the setup over http protocol.
Let me know the result.
I have successfully connected over https following the document provided by SAP support.
Point to be noted is that, I didn't change anything on the webdispatcher.ini. It got connected on port 433 also you need to provide <account>.<hcp host> (say abc.hana.ondemand.com) as host name on dpagent config tool. There were no proxies required in my case.
As the document was quite long, I am not sharing it here. As per the SAP support, they are in process of updating documentation for dpagent connection over https.
I am facing the same issue. I want to connect the dp agent to HANA on cloud but it says 'Connection to <account>.hana.ondemand.com refused'.Can you share the document from which you were able to resolve the issue?
If you want to connect you DP agent to Hana on Coud you will have to first get the signed certificate from the Hana and load into your DP server by keytool command.
1) From the Hana Webdispatcher interface copy the text content your SAPSSLS.pse
2) On your DP agent server create a SAPSSL.cer file
3) Copy the content text into the SAPSSL.cer
4) Use keytool command to add it into the cacerts
5) On the DP agent config specify the Keystore password and check "Is SSL enable on Hana server"
Thanks Williams. We have not setup the Webdispatcher interface on our HANA server.
Hence, I followed this guide for downloading the signed certificate (Download the SAP HANA Server Certificate - SAP HANA Enterprise Information Management Administration Guide - SAP Library). I loaded this certificate into DP server but I get the same error.
Is it necessary to setup the Webdispatcher interface and get the signed certificate from there?
You don't need to go over the webdispatcher is you already have access to your Hana instance by https.
So if this is the case, you just need to import your certificate into the DP Agent server, can please tell me what error message you have ?
Thanks for the quick response Williams,
I am seeing the following error:
Can you confirm that you can first connect on the Hana cockpit over 443 ? if yes please confirm that you import your certificate with the following command :
keytool -importcert -keystore cacerts -storepass <password> -file SAPSSL.cer -noprompt
One important point is format used to import it.
Please let me know if you have any issue
Thanks this is a great blog!
We are on SPS9 and are planning to install EIM.
In your blog you mention installing HANA_IM_ESS.
In one of your replies you state that you only need HANA_IM_ESS for ADP.
In addition, I have looked at the EIM administration guides for SPS9 and SPS10.
In the SPS9 guide ESS isn't mentioned.
In the SPS10 guide ESS is mentioned, but explicitly only for SPS9.
Now I'm confused. Can you clarify this point for me?
ESS provide a semantic search feature required for SAP Agile Data Preparation which allows business analysts to search for data sources by name or content, so if you just want to replicate data and transform it, it's not mandatory.
Thanks for your explanation, Williams.
I still have another question. When I looked at Support Packages and Patches | SAP Support Portal for HANAIMESS00_0.zip, I couldn't find it.
I searched under SAP HANA SDI 1.0. But there I could only find HANAIMDP and the DP agent. I searched under HANA Platform. Didn't find it there either.
I also tried "HANAIM*" as a search term. But I only got HANAIMDP00 and HANAIMDP01.
Could you describe the correct path or search term?
Thanks in advance
You need to be licensed for to download it, this is why you only see :
HANA DP and HANA DP Agent
Thanks for this blog.
I am facing difficulty in registering an adapter with my HANA server:
Please help how to proceed.
Which version of Hana are you on ? also please make sure to have the necessary credential to register your adapter in Hana.
My system version is 1.00.096.00.1432206182.
I think I have all necessary credentials. I at least checked system privilege ADAPTER ADMIN for my user.
Please suggest what could be the issue.
Update on 5th Jan 2016 >> The issue does not occur if I specify IP address in agent hostname while registering the agent.
The next hurdle that I now face is while creating a remote source using file adapter. I have created a remote source using file adapter but do not see the data files as remote source tables under provisioning remote source in HANA Studio.
I have my data & CFG files in correct directories specified while configuring the DP agent. My DP agent is installed in the same windows box. But, I am not sure if index server must be specified as source location for my remote source; I do not get another option to choose (agent?).
Am I missing any configuration step?
You should be able to read it, the adapter name should appear and not indexserver.
Can you please have a look at the log file and/or send it to me please ?
Thanks a lot, Williams. I will try to register another adapter as suggested by you.
Alternatively, I was able to create remote source through SQL console.
Is it IM_DP SP2 for HANA SPS11?
And also SP2 for the DP Agent?
The DP Agent (IMDB_DPAGENT100_02) and IM_DP (HANAIMDP02) are actually made for Hana SP11, however this version is also backward compatible with Hana SP10.
For more clarity Martin you can also refer to SDI PAM
Let me know if you have further question.
Thanks for this blog.
I am facing difficulty in registering an agent:
Please help me to proceed.
Amit Kumar Pathak
Are you still facing the error ? if so please send me your log and detail the step you proceed.
Did you solve this issue.?? if yes how.??
I am getting quite a few activation errors when installing the ESS DU.
Example error message:
A transaction rollback may have left the object in an inconsistent state.
We will be evaluating ESS with ADP. Any guidance on correcting this would be appreciated.
Which version of ESS DU are you deploying ?
As it turns out I was deploying the incorrect version for our HANA SPS level (102.3) we are on. The Admin Guide is not clear on this. I discovered it via the PAM.
I did an uninstall of those components which did get installed and then installed the correct DU version (HANAIMESS01P_2-70000759) and it was successful. Thanks for your follow-up.
Can someone guide about which version of dp agent should be used for SPS09?
You must use "HANA DP AGENT 1.0" the first line in your picture.
I cannot register TwitterAdapter with HANA server. I am using the HANA on cloud option. I am getting the following error:
Please check the dpagent_service_evenlog and check the connectivity between the agent and Hana, look if you have the following error :
# Receive Ping reply message from DP Agent.
# No agent existing!!!
# Error #10061 in connecting socket.
# Error #10057 in ping agent.
# Error #10057 in receiving from agent.
# No agent existing!!!
In some case you just need to restart the dp agent.
Let me know
I checked the dpagent_service_evenlog file and I see the errors multiple times:
# Error #10061 in connecting socket.
# Error #10057 in ping agent.
# Error #10057 in receiving from agent.
# No agent existing!!!
I tried to restart the agent but the error is still the same.
Can you please unregister your agent and re-register it again, it should works.
Thanks for the information. It worked but the next issue I am facing is that I cannot create a remote source using TwitterAdapter. I am getting the following error:
The source location of your adapter must be the name of the adapter you created for the Twitter.
From your screen i can see "index server" which is wrong.
Can you check if you adapter shows up in the drop down list
Unfortunately I can only see 'index server' in the Source Location dropdown list. What could be the possible issue?
Yes it is because it belong to your adapter. Please create an additional adapter and make the check.
Thanks for your document with detail explanation.
While I am trying to importing the delivery unit getting the below error.
Please suggest me to resolve this issue.
Thanks & Regards,
According your screen picture you seems to deploy DP and ESS version for SP9, can you please confirm which version of Hana you working on ?
I am using SP09 version of HANA only.
Thanks & Regards,
Uninstall both previous DU left off, them re-install the DP first by selecting "Overwrite inactive version" and "Activate objects"
I am trying to setup SDI , i have installed the DP Agent on to my laptop and was able to connect to my HANA server. But when i tried do Agent Registration i am getting the error below.
when i click on the register agent and give a name to my agent i am getting the error below. please advice on what needs to be done.
Which OS your laptop is running on ? because the dp agent must run on server (SLES 11 or RHEL)
Please send me the dp_agent log and framework too.
I recently tried to install the DP Agent version 220.127.116.11.0 but I get the following error when I install the software:
Please check the Java version on your machine where you install it.
I have recently installed HANA SPS12. I installed the DP Agent on a Windows server - DP AGENT SP03. Everything, including registering the Twitter adapter went just fine.
But when I tried to create a remote source, after entering my consumer token and access token keys, I got this message -
SAP DBTech JDBC: : internal error: Cannot get remote souce objects: connect timed out.
Can you help me?
Can you check if the firewall of your windows server is stop ? also please can you send me the dp_agent log and framework log too ?
Great Blog you have here!
For example, assuming that this tool will be used moving forward to all HANA/S4 implementation and assuming the existing client is currently using BODS and would like to migrate to this tool (SDI)), what are some of the factors/things that we can consider in migrating objects/flow. Are there any tool(s) for migration from BODS to SDI or it would be an effort like creating new flow to SDI base from BODS?
This is a great question, in term of effort (flow based), if a customer wants to move from DS to SDI, since no tool (as far as know) exist to migrate the current flow build in DS; the logic will need to be rebuild in Hana directly.
Since there is no portability of the flow, the customer will have to evaluate the way the logic is built in DS vs the way he wants to do it in Hana.
Let me know if you have other question
Thanks for that informative response.
Are there or do you have any guide questions for this type of approach considering that there's no available tool to migrate it.
With this initially, i am considering that a flow consist a source(s), the logic (dev work in SDI) and the target. and I also defined it into 3 areas of complexity like simple, medium and high. Do you have like high level definition how these complexities are defined and more likely how long to develop a simple, a medium and a high base from your expert advice.
I'll come to you on it, i gather the information and will publish it.
Thanks and looking forward to it?
Any suggestions on how to fix this error ..
CREATE AGENT TEST_AGENT PROTOCOL 'TCP' HOST 'sst' PORT 5050
I get the same error
from your agent server, are able to resolve the name of you hana server and vise-versa ?
Please run some nslookup command, if it failed them you will need to add entries in hosts file of both server
I have DP agent installed on my windows 10 laptop and i could able to connect to HANA server . but getting the same error as above when i try to register the Agent. tried both from DP Agent and HANA server using SQL command. i could able to ping the HANA server from my laptop and came to know that Server needs to be able to communicate with the agent (laptop) on port 5050. So the security guys opened it on the server one way (server to dp agent). anything else i am missing.?? does port 5051 needs to be opened as well.? bith 5050 and 5051 are listed on the laptopn.
I need to understand, can SDI/SDQ can be used for address standardization and duplicate check in MDG?
Do we have a use case of SDI/SDQ supporting MDG?
MDG instance is running on HANA.
Thanks and regards
Thank so much for this blog and for keeping posting comments.
My question is about : OracleEccAdapter Credentials.
Do i need a special user from the database when using the OracleECCAdapter
or a Sapgui’s User would suffice as it is for the AbapAdapter ?
Thanks in advance,
You are very welcome, yes you will need a specific user in Oracle because you will need to run a script where a dedicated oracle user needs to be defined.
Read my other document where i explain it :
Let me know if you have any question
good blog! Can I install more than one DP agent on a windows machine? Right now, I am running into issues that I have to uninstall the existing oneto have another one installed. I would like to use this system to serve more than one agents serving more than one HANA.
On Linux, you can install multiple agents on a single host machine but on Windows, only a single
DP Agent installation is supported per host.
Do you still having issues ? sorry my account was inactive for awhile 🙁