SDI-Based Blending Between SAP HANA Live Data Models and Import Data Models

You can enable blending between a primary SAP HANA live data model, and import data models.

The following setup and configuration steps must be performed by the administrator for your SAP HANA system, and an administrator for SAP Analytics Cloud. These steps are typically not performed by one person alone; therefore, it is recommended that you make a plan with your SAP HANA and SAP Analytics Cloud system administrators before proceeding with the steps below.

Ensure your SAP HANA system is on the Correct Version

You are on SAP HANA 2.0 on-premise SPS02 Patch 5 or higher, with EPMMDS version 1.00.201815.00.1534765831 (Wave 201815) or higher. See SAP Note 2456225 and SAP Note 2444261 for additional setup information.
Your SAP HANA system must have a full use license. Data blending for SAP Analytics Cloud is not permitted for SAP HANA systems using a runtime (REAB) license. SAP HANA systems using a Runtime Edition (REAB) license will be supported in the near future.

Install the SAP Data Provisioning Agent

SAP Analytics Cloud leverages the use of SAP HANA Smart Data Integration (SDI) to enable data blending. You must have the SAP Data Provisioning Agent installed on a Windows or Linux host from which it can establish HTTPS connections to SAP Analytics Cloud and your SAP HANA system.

The version of the Data Provisioning Agent required for your SAP HANA system can be determined by checking the Product Availability Matrix.

For information on installing the agent, see Installing the SAP Data Provisioning Agent. The following steps must be performed during the agent installation:

Data Provisioning Agent Planning and Preparation.
1. Supported Platforms and System Requirements
2. Software Download
Install the Data Provisioning Agent.
1. Install from the Command Line

Afterward, proceed with creating a table in your SAP HANA system.

Create a table in your SAP HANA system

Using SAP HANA Studio, create the REMOTE_OBJECTS table on your on-premise SAP HANA system. This table is used by SAP Analytics Cloud to temporarily store data results required for a blend via the agent connection. You must execute the following SQL and stored procedure statements within the SQL Console of SAP HANA Studio.

Create a user for administering this table. This user will also be used in SAP Analytics Cloud for creating the connection.
```
CREATE USER SDI_ADMIN PASSWORD <admin_password> NO FORCE_FIRST_PASSWORD_CHANGE;
ALTER USER SDI_ADMIN DISABLE PASSWORD LIFETIME
```
The remaining steps for setting up your on-premise system must be done with the SDI_ADMIN user.

Create the REMOTE_OBJECTS table.

CREATE COLUMN TABLE "SDI_ADMIN"."EHS_REMOTE_OBJECTS" (ID VARCHAR(100), TYPE VARCHAR(50), INSERT_TIME TIMESTAMP, SEQ INTEGER, EXPIRE_TIME TIMESTAMP, OBJECT VARBINARY(5000), PRIMARY KEY (ID, TYPE, INSERT_TIME, SEQ));

Set permissions for the REMOTE_OBJECTS table, which allow for storage and access to the temporary cache data during data blending scenarios.

Caution

If you re-import the INA Delivery Unit (DU), these permissions will be removed from the INA_USER role, and they will need to be re-added.

GRANT EXECUTE, INSERT ON SCHEMA "SDI_ADMIN" TO "sap.bc.ina.service.v2.userRole::INA_USER";
GRANT SELECT ON "SYS"."PROCEDURES" TO "sap.bc.ina.service.v2.userRole::INA_USER";

CREATE PROCEDURE "SDI_ADMIN"."DATA_READ_##EHS_REMOTE_OBJECTS" (IN remoteId VARCHAR(100), IN objectType VARCHAR(50), OUT outputTable TABLE(OBJECT VARBINARY(5000)))
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
READS SQL DATA AS
BEGIN
outputTable = SELECT OBJECT FROM "SDI_ADMIN"."EHS_REMOTE_OBJECTS"
JOIN (SELECT MAX(INSERT_TIME) AS MAX_INSERT_TIME FROM "SDI_ADMIN"."EHS_REMOTE_OBJECTS" WHERE ID=:remoteId AND TYPE=:objectType LIMIT 1)
ON INSERT_TIME = MAX_INSERT_TIME WHERE ID=:remoteId AND TYPE=:objectType ORDER BY TYPE, SEQ ASC;
END;

CREATE PROCEDURE "SDI_ADMIN"."EXPIRED_CHECK_##EHS_REMOTE_OBJECTS" (IN remoteId VARCHAR(100), IN viewUTCTime TIMESTAMP, OUT rowCount INTEGER)
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
READS SQL DATA AS
BEGIN
SELECT COUNT(*) INTO rowCount FROM "SDI_ADMIN"."EHS_REMOTE_OBJECTS"
JOIN (SELECT MAX(INSERT_TIME) AS MAX_INSERT_TIME FROM "SDI_ADMIN"."EHS_REMOTE_OBJECTS" WHERE ID=:remoteId AND TYPE='CUBE' LIMIT 1)
ON INSERT_TIME = INSERT_TIME
WHERE ID=:remoteId AND TYPE='CUBE' AND INSERT_TIME > :viewUTCTime AND EXPIRE_TIME > CURRENT_UTCTIMESTAMP LIMIT 1;
END;

CREATE PROCEDURE "SDI_ADMIN"."DELETE_ENTRIES_##EHS_REMOTE_OBJECTS" (IN remoteId VARCHAR(100), IN cleanTimeoutSeconds INTEGER)
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER AS
BEGIN
DELETE FROM "SDI_ADMIN"."EHS_REMOTE_OBJECTS" WHERE EXPIRE_TIME < ADD_SECONDS(CURRENT_UTCTIMESTAMP, -:cleanTimeoutSeconds) OR
(ID=:remoteId AND INSERT_TIME < (SELECT MAX(INSERT_TIME) FROM "SDI_ADMIN"."EHS_REMOTE_OBJECTS" WHERE ID=:remoteId LIMIT 1));
END;

Configure and register the SAP Data Provisioning Agent

Register the Data Provisioning Agent with SAP Analytics Cloud:
1. Log onto SAP Analytics Cloud and go to (Main Menu) System Administration Data Source Configuration.
2. Scroll down to the SAP HANA Smart Data Integration section, and select Register New Data Provisioning Agent.
3. In the dialog, enter a unique name for your new agent registration.
  Note
  The registration name cannot be changed later.
4. (Optional) Provide a description for your new agent registration.
5. Select Save before proceeding.
  Note
  You will need the information presented in the Data Provisioning Agent dialog for step 2. Either keep the dialog box open, or note the information provided and give it to the SAP HANA administrator who will perform step 2.
Create a connection between the agent and your on-premise SAP HANA system. Perform the following steps in the SAP Data Provisioning Agent.
Note
<DPAgent_root> is the directory where the DP agent was installed. By default, on Windows, this is C:\usr\sap\dataprovagent, and on Linux it is /usr/sap/dataprovagent.
1. Configure dpagentconfig.ini:
  
  In the directory where the agent was installed, open <DPAgent_root>/dpagentconfig.ini, and edit the following lines. Use the values provided by the Register New Data Provisioning Agent dialog from step 1.b. The required values are listed under Data Provisioning Agent Details.
```
agent.name=(<AgentName>)
hana.onCloud=true
hana.server=(<HANA_Server>)
hana.port=(<HANA_Port>)
hana.useSSL=true
```
  Note
  <HANA_Server> is the server IP address or hostname. <HANA_Port> is the HTTP port.
  
  If you need a proxy to access the internet due to a corporate firewall, you must also set the following lines:
```
cloud.useProxy=true
proxyHost=<proxy_hostname>
proxyPort=<proxy_port>
```
  Note
  The proxy must be able to handle HTTP long polling. Using a proxy may have a performance impact on blending.
2. Save the changes made to dpagentconfig.ini.
3. Open an Administrator prompt on the Windows or Linux host where the DP agent is installed, and run the following commands:
  - Set the environment variable.
    On Linux:
```
export DPA_INSTANCE=<DPAgent_root>
```
    On Windows:
```
set DPA_INSTANCE=<DPAgent_root>
```
  - Start the Data Provisioning Agent.
    On Linux:
```
<DPAgent_root>/bin/agentcli.sh --configAgent
```
    On Windows:
```
<DPAgent_root>/bin/agentcli.bat --configAgent
```
    You must start the agent, if it has not been started already: Select option 2. Start or Stop Agent, and then option 1. Start Agent.
    
    Select q. Quit to exit the script.
    
    This pattern of stopping and then starting the agent is required whenever you make a configuration change to the agent.
  - Set the credentials for the HANA XS user.
    On Linux:
```
<DPAgent_root>/bin/agentcli.sh --setSecureProperty
```
    On Windows:
```
<DPAgent_root>/bin/agentcli.bat --setSecureProperty
```
    Select 1. HANA XS Username.
    
    Enter the username presented in the Register New Data Provisioning Agent dialog from step 1.b.
    
    Select 2. HANA XS Password.
    
    Enter the password presented in the Register New Data Provisioning Agent dialog from step 1.b.
    
    Note
    
    If you have closed and reopened the Register New Data Provisioning Agent dialog from step 2.b, and the password field is empty, you can click the button next to the password box to generate a new password.
    
    When you click the Save button, the new password is saved to SAP Analytics Cloud and is ready to be used.
    
    Select q. Quit to exit the script.
  - Restart the SAP Data Provisioning Agent.
    On Linux:
```
<DPAgent_root>/bin/agentcli.sh --configAgent
```
    On Windows:
```
<DPAgent_root>/bin/agentcli.bat --configAgent
```
    Select option 2. Start or Stop Agent, and then option 2. Stop Agent to stop the agent.
    
    Select option 1. Start Agent to start the agent.
    
    Select option 1. Agent Status to check the connection status.
    
    If the connection succeeded, you should see Agent connected to HANA: Yes.
    
    Select q. Quit to exit the script.
    
    You can now close the agent registration dialog from step 1.
Connect SAP Analytics Cloud to your on-premise SAP HANA system.
1. Log on to SAP Analytics Cloud and go to (Main Menu) System Administration Data Source Configuration.
2. Under SAP HANA Smart Data Integration, select Add New Remote Source. The following dialog will appear:
3. In the dialog, provide a name for your SAP HANA system
4. Provide the fully qualified domain name of your SAP HANA system.
5. Provide the SQL port of your SAP HANA system.
6. Provide the schema and table name of the REMOTE_OBJECTS table you created.
7. If you want to enable SSL encryption between your remote source and the SAP Data Provisioning Agent, select the Use encryption and Validate certificate check boxes.
8. Provide the credentials for the SDI_ADMIN user that created the REMOTE_OBJECTS table.
9. (Optional) Select the Use remote subscription check box, to allow caching of blended query results in SAP Analytics Cloud. By enabling this option, you may potentially improve the load performance of blended visualizations.
  Note
  For security reasons, don't select this option if you want to prevent data from being copied and saved in SAP Analytics Cloud.
10. Select Save.
Associate an existing live data connection with the remote source.
Note
The live data connection you select must be the same on-premise SAP HANA system as the remote source.
1. Click Add Live Data Connection on the remote source created in step 3.
2. In the drop-down box, select the appropriate live data connection.
3. Click Save.

For information about installing the agent, see Installing the SAP Data Provisioning Agent.

Troubleshooting Data Provisioning Agent errors

If you encounter problems with the Data Provisioning Agent, it can be helpful to examine the logs located in the <DPAgent_root>/log directory.

Please ensure that your data provisioning agent is connected to HANA.
1. Run <DPAgent_root>/bin/agentcli.bat --configAgent.
2. Select option 1. Agent Status to check the connection status.
3. Make sure the output shows Agent connected to HANA: Yes.
4. If the output doesn't show that the agent is connected, it may show an error message. Resolve the error, and then select option 2. Start or Stop Agent, and then option 1. Start Agent to start the agent.
Failed to connect to the remote source. Please restart your data provisioning agent and try again.
1. Run <DPAgent_root>/bin/agentcli.bat --configAgent.
2. Select option 2. Start or Stop Agent, and then option 2. Stop Agent to stop the agent, and then option 1. Start Agent to start the agent.