How to use and configure sap hana smart data integration?
Sap hana SDI is very easy to understand and simple to put in place. Its features gives many opportunities for companies to produce reporting from external data but also to migrate external source of data into sap hana databases.
SDI configuration consists of installing an agent ( A DP Agent ) and an adapter which will establish a link between sap hana and the external source.
A dedicated SDI Administrator user manages SDI landscape environments. He will be responsible for remote sources, packages, and developer user privileges installations. This site gives technical tips how to establish a SDI environment.
Sap Hana SDI can be described as a bridge between sap hana and external data sources. Among other features, it would also be used as a tool to produce report from external data and to migrate external data to sap hana.
When links are established you can replicate external data to the sap hana database environment. External data Source can be either files or links to external databases such as Oracle. With SDI you have the ability to reshape data before producing any report or before integrating it into the sap hana database itself.
[ To see more details and explanations @sap ]
Smart Data Integration Architecture
Smart Data Integration overview demo
What are the sap hana smart data integration adapters for?
SDI adapters are like the old ODBC. Some adapters will deal to specific type of databases connexions such as Microsoft SQL Server or oracle, other types will read data such as flat files located to other platforms..
Here are different adapters available.
Real-time enabled adapters:
SAP ECC based on Oracle, DB2, SQL Server or ASE
SAP (Sybase) ASE
Microsoft SQL Server
File (delimited and fixed width)
How to install and configure Data Provisioning Agent and Adapter?
The data provisioning agent contains all SDI Adapters. Its role is to maintain communication between the sap hana environment and the adapter. It enable external data replication and data transformation. Adapters are written in java and can be installed on any platform. The DP Agent installation tool is "hdbsetup.exe". The installation process is easy. It is a succession of windows guiding you through the installation.
In the example : Download was done on windows. Directory created for the purpose : d:\sap\dpagents d:\sap\dpagent\hdbsetup.exe
Which roles and privileges to assign for sap hana smart data integration?
First of all, do not use SYSTEM user or any advanced system administration user to develop and manage a smart data integration project.
SDI Environment should be managed from a dedicated SDI Administrator. In other word, the system administrator user should create the SDI user with all the relevant privileges to administer the new project environment.
The following specific group of tasks are required for a SDI project:
- Environment Administration : Creates and configures all aspects of the SDI project. - DP Agent : Install and configure DP agent and Adapters. - Development : Create and Develop reports - Transport : export / import objects from one environment to another. - Execution : Execute flowgraph.
On one hand, it is advised to work with 4 different user type. Each one with specific responsibilities. However, in many cases due to small team, you can manage your project with 2 type of users. ( A project administrator and Developers ) In most cases, it will be more convenient that way than having different users for transport and execution.
The Project administrator user will be able to perform everything within the project from implementing SDI up to performing transport and execution.
Developpers will be dealing with the overall development tasks around the replications.
Any way, the best thing is to declare and define clearly the 4 main tasks with related privileges behind. Afterwards, you decide who is doing what and assign related roles to users.
All privilege categories for sap hana smart data integration dev and admin?
The following privileges are not especially needed when working on a SDI project. However they give more advanced capacities to developers and administrator. WebBased tools such as Live cycle management can be access and facilitate the SDI development process.
Important Note : Via hana studio, check privileges are available in your tenant database. If not SDI delivery unit has to be imported into your sap hana tenant database before assigning privileges. You will get an error of that type for most privileges otherwise : [ example: invalid user privilege: sap.hana.im.dp.monitor::Monitoring ]
Which step to prepare a sap hana smart data integration project?
There are few steps to follow in order to succeed in the smart data integration implemention. This section concernes preparing the database environment when using SDI. The sap hana system administrator should create a SDI administrator user. This new powerful SDI user will make sure the SDI project is self sufficient and that users can perform their work without calling the main system administrator.
To do so, the first step is to create different and distinct roles. SID Administrator role, DP agent role, transport role, execution role, and the developer role are the 5 main groups of privileges required to run the SDI. Each role will be assigned to individual or to groups depending of team work facilty.
Note : Individual role with more advanced privileges will be details further down this page.
The following example shows SDI roles assigned to 3 differents users type. The Admin, the Dp agent and developpers.
Sap Hana System Administrator
Create project users, roles and privileges
ex project name for HR SDI reports: HrRep User Names : CREATE USER HrRep_PETER PASSWORD ToBechanges01; CREATE USER HrRep_JOHN PASSWORD ToBechanges01; CREATE USER HrRep_PAUL PASSWORD ToBechanges01;
Role name examples: CREATE ROLE SDI_ADMIN_ROLE; CREATE ROLE SDI_ADMIN_DPAGENT_ROLE; CREATE ROLE SDI_TRANSPORT_ROLE; CREATE ROLE SDI_EXECUTE_ROLE; CREATE ROLE HrRep_PACKAGE_ROLE; CREATE ROLE HrRep_REMOTE_SOURCE_ROLE; CREATE ROLE HrRep_DEV_ROLE;
Grant privileges to Role .... Grant < All sdi roles > to Admin role ....
Grant Roles to users GRANT SDI_ADMIN_ROLE TO HrRep_PETER; GRANT SDI_ADMIN_DPAGENT_ROLE, HrRep_DEV_ROLE TO HrRep_JOHN; GRANT HrRep_DEV_ROLE TO HrRep_DEV_PAUL;
GRANT REPO.READ, REPO.EDIT_NATIVE_OBJECTS, REPO.ACTIVATE_NATIVE_OBJECTS, REPO.MAINTAIN_NATIVE_OBJECTS ON "public.PKG_HrRep" TO HrRep_PACKAGE_ROLE;
GRANT HrRep_PACKAGE_ROLE TO HrRep_DEV_ROLE;
SDI Project Administrator
ex : CREATE SCHEMA "HrRep_SCHEMA_1";
SDI Project Administrator
Assign privileges on schema to Developpers:
GRANT SELECT, CREATE ANY ON "HrRep_SCHEMA_1" TO HrRep_DEV_ROLE;
SDI Project Administrator
Grant _SYS_REPO authorizations to activate flowgraphs and replication task:
GRANT SELECT ON SCHEMA "HrRep_SCHEMA_1" TO _SYS_REPO WITH GRANT OPTION; GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE "RS_HrRep" TO _SYS_REPO;
SDI Project Administrator
Grant privileges to execute flowgraphs and replication:
In this example: Privileges given to Developers. GRANT SELECT, EXECUTE, ALTER, INSERT, UPDATE, DELETE ON SCHEMA "HrRep_SCHEMA_1" TO HrRep_EXECUTE_ROLE;
GRANT REPO.READ, REPO.EDIT_IMPORTED_OBJECTS, REPO.ACTIVATE_IMPORTED_OBJECTS, REPO.MAINTAIN_IMPORTED_OBJECTS ON "public.PKG_HrRep" TO HrRep_EXECUTE_ROLE;
GRANT HrRep_EXECUTE_ROLE TO HrRep_DEV_ROLE;
Which privileges is required to install data Provisioning Agent and Adapters?
Registering and configuring Data Provisioning Agent and Data Provisioning Adapter require specific privileges. Depending of your organisation, a dedicated agent user can be created for those particular tasks. Keep in mind, the SDI project administrator user should have all abilities to perform those requirements.
To register a DP Agent
System privilege: AGENT ADMIN
ex : GRANT AGENT ADMIN TO SDI_ADM_AGENT_ROLE;
To register an adapter
System privilege: ADAPTER ADMIN
ex : GRANT ADAPTER ADMIN TO SDI_ADM_AGENT_ROLE;
To configure DP Agent to use HTTP
Role: sap.hana.im.dp.proxy: :AgentMessaging
ex : call GRANT_ACTIVATED_ROLE( 'sap.hana.im.dp.proxy::AgentMessaging' , ' SDI_ADM_AGENT_ROLE' );
ex : call "_SYS_REPO"."GRANT_APPLICATION_PRIVILEGE"( '"sap.hana.im.dp.admin::Administrator"' , 'SDI_ADM_AGENT_ROLE') ;
To import a delivery unit with Application Lifecycle Management
Role: sap.hana.xs.lm.roles: :Administrator
ex : call GRANT_ACTIVATED_ROLE( 'sap.hana.xs.lm.roles::Administrator' , 'SDI_ADM_AGENT_ROLE');
To import a delivery unit via SAP HANA studio
Role: sap.hana.xs.lm.roles: :Transport
ex : call GRANT_ACTIVATED_ROLE( 'sap.hana.xs.lm.roles::Transport' , 'SDI_ADM_AGENT_ROLE');
Assign Role to dedicated User
ex : Your SDI Role: SDI_ADM_AGENT_ROLE
GRANT SDI_ADM_AGENT_ROLE TO SDI_ADMIN_AGENT_USER;
Granting all sap hana DP Agent and Adapters privileges in one go.
Here is a set of SQL syntaxes which will create a specific DP agent and Adapter Role with all the relevant privileges. A user with those privileges will be able to install and configure the DP Agent and the adapters.
CREATE ROLE SDI_DPAGENT_AND_ADAPTER_ROLE;
GRANT ADAPTER ADMIN TO SDI_DPAGENT_AND_ADAPTER_ROLE;
GRANT AGENT ADMIN TO SDI_DPAGENT_AND_ADAPTER_ROLE;
GRANT CREATE REMOTE SOURCE TO SDI_DPAGENT_AND_ADAPTER_ROLE;
call "_SYS_REPO"."GRANT_APPLICATION_PRIVILEGE"('"sap.hana.im.dp.proxy::AgentMessaging"' , 'SDI_DPAGENT_AND_ADAPTER_ROLE');
call "_SYS_REPO"."GRANT_APPLICATION_PRIVILEGE"('"sap.hana.im.dp.admin::Administrator"' , 'SDI_DPAGENT_AND_ADAPTER_ROLE');
call GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.roles::Administrator' , 'SDI_DPAGENT_AND_ADAPTER_ROLE');
call GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.roles::Transport' ,'SDI_DPAGENT_AND_ADAPTER_ROLE');
Which SDI privileges to give for monitoring requirements?
Monitoring is part of the SDI administrator ability requirements. Here is a set of SQL syntaxes which will assign monitoring privileges to a Role. The role can be given to relevant users. Sap hana cockpit can be used in order to perform data provisioning monitoring.
Which SDI privileges to give for remote source and remote subscription tasks?
Here is a set of SQL syntaxes which will assign relevant privileges to a role. Those privileges give the abilities to manage remote source and remote subscription. The role can be given to the SDI Administrator user or to any relevant SDI users.
CREATE ROLE SDI_REMOTE_SOURCE_AND_SUBSCRIPTION_ROLE;
-- The following CREATE REMOTE SOURCE role includes :
-- 1. CREATE VIRTUAL TABLE,
-- 2. DROP, CREATE REMOTE SUBSCRIPTIONS,
-- 3. PROCESS REMOTE SUBSCRIPTION EXCEPTION privileges
GRANT CREATE REMOTE SOURCE TO SDI_REMOTE_SOURCE_AND_SUBSCRIPTION_ROLE; :
GRANT DATA ADMIN TO SDI_REMOTE_SOURCE_AND_SUBSCRIPTION_ROLE;
CALL "_SYS_REPO".GRANT_APPLICATION_PRIVILEGE(' "sap.hana.im.dp.monitor::AlterRemoteSource"', 'SDI_REMOTE_SOURCE_AND_SUBSCRIPTION_ROLE');
CALL "_SYS_REPO".GRANT_APPLICATION_PRIVILEGE(' "sap.hana.im.dp.monitor::AlterRemoteSubscription"', 'SDI_REMOTE_SOURCE_AND_SUBSCRIPTION_ROLE');
CALL "_SYS_REPO".GRANT_APPLICATION_PRIVILEGE(' "sap.hana.im.dp.monitor::DropRemoteSubscription"', 'SDI_REMOTE_SOURCE_AND_SUBSCRIPTION_ROLE');
CALL "_SYS_REPO".GRANT_APPLICATION_PRIVILEGE(' "sap.hana.im.dp.monitor::ProcessRemoteException"', 'SDI_REMOTE_SOURCE_AND_SUBSCRIPTION_ROLE');
Which SDI privileges to give for replication and flowgraph tasks?
The following set of SQL syntaxes gives the privileges to manage replication and flowgraph tasks. The role which groups all those privileges can be assigned to the SDI Administrator user and alternatively to a SDI users.
CREATE ROLE SDI_REPLICATION_AND_FLOWGRAPH_ROLE;
CALL "_SYS_REPO".GRANT_ACTIVATED_ROLE('sap.hana.xs.ide.roles::EditorDeveloper', 'SDI_REPLICATION_AND_FLOWGRAPH_ROLE');
CALL "_SYS_REPO".GRANT_ACTIVATED_ROLE('sap.hana.xs.ide.roles::CatalogDeveloper', 'SDI_REPLICATION_AND_FLOWGRAPH_ROLE');
CALL "_SYS_REPO".GRANT_ACTIVATED_ROLE('sap.hana.xs.ide.roles::Developer', 'SDI_REPLICATION_AND_FLOWGRAPH_ROLE');
CALL "_SYS_REPO".GRANT_ACTIVATED_ROLE('sap.hana.xs.ide.roles::SecurityAdmin', 'SDI_REPLICATION_AND_FLOWGRAPH_ROLE');
CALL "_SYS_REPO".GRANT_ACTIVATED_ROLE('sap.hana.xs.ide.roles::TraceViewer', 'SDI_REPLICATION_AND_FLOWGRAPH_ROLE');
CALL "_SYS_REPO".GRANT_ACTIVATED_ROLE('sap.hana.xs.debugger::Debugger', 'SDI_REPLICATION_AND_FLOWGRAPH_ROLE');
GRANT EXECUTE ON "_SYS_REPO"."TEXT_ACCESSOR" TO SDI_REPLICATION_AND_FLOWGRAPH_ROLE;
GRANT EXECUTE ON "_SYS_REPO"."MULTI_TEXT_ACCESSOR" TO SDI_REPLICATION_AND_FLOWGRAPH_ROLE;
Which privileges do I need to manage sap hana application Lifecycle Management?
All about Lifecycle Management Application
In most cases sap hana application Lifecycle Management application will be required when working on SID Projects. An expert or an administrator user will be able to use and manage the tool with the following privileges.
Which privileges do I need to use sap hana application Lifecycle Management?
Life cycle management tool can be used by other users than the SDI administrator. With the following privilege set, developers will be able to transport content from one environment to another using the Lifecycle Management tool.
Which privileges to give to the SDI administrator user?
This SDI administrator user is the most important user for the SDI project. His role is to be self sufficient for any SDI requirements and to release the main sap hana system administrator from having to deal with the SDI environment.
His role is to prepare the SDI environment. He will create all the SDI infrastructure needed for the SDI development. He will assign all required privileges to each SDI project user.
All privileges and roles described on this page will have to be given to the SDI Administrator User.
CREATE ROLE SDI_ADMINISTRATION_ROLE;
GRANT SDI_DPAGENT_AND_ADAPTER_ROLE TO SDI_ADMINISTRATION_ROLE;
GRANT SDI_MONITORING_ROLE TO SDI_ADMINISTRATION_ROLE;
GRANT SDI_REMOTE_SOURCE_AND_SUBSCRIPTION_ROLE TO SDI_ADMINISTRATION_ROLE;
GRANT SDI_REPLICATION_AND_FLOWGRAPH_ROLE TO SDI_ADMINISTRATION_ROLE;
GRANT SDI_LIFECYCLE_ROLE TO SDI_ADMINISTRATION_ROLE;
GRANT SDI_ADMIN_LIFECYCLE_ROLE TO SDI_ADMINISTRATION_ROLE;