Technology Blog Posts by SAP
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Creating an Import Data Connection to an On-Premise MS SQL Server

Sakshi_Kathuria
Associate
Associate
4 weeks ago
561

In this blog we’ll discuss complete steps that allows you to import data from an on-premise SQL database to SAP Analytics Cloud .

Here, Our Data source is SQL Database from which the SAP Analytics Cloud is connected using the SAP Analytics Cloud Agent and SAP Cloud Connector.

It is recommended that the Cloud Connector, SAP Analytics Cloud agent are installed together on a dedicated server, and not a personal computer. This helps to ensure that multiple users can use an import data connection without experiencing slowness or downtime.

Below are the steps for Import Data connection for On-premise SQL Database –

1. Check the connectivity between SAC agent server and SQL DB on the required port.

telnet <MS SQL SERVER> 1433

Sakshi_Kathuria_0-1762844012864.png

2. Installing and Configuring SAP Cloud Connector and JVM on a linux server.

  • To install a cloud connector refer the link:

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/ae39ab60b1154c179e2ba...

  • Make sure a latest JVM version is installed on the Cloud Connector server.

Sakshi_Kathuria_1-1762844113464.png

  • To open the SCC, enter: https://<hostname>:8443 in a browser, where the <hostname> is the hostname of the machine on which the connector is installed, and the port number is the one configured during installation. The default port number is 8443
  • Click on “Add subaccount” and enter the details of the SAC subaccount , which you can find on SAP Analytics Cloud. To find the Subaccount details of SAP Analytics Cloud :

You must use a System Owner account to perform the following steps. If you don't know who the system owner is, log on to SAP Analytics Cloud and from the side navigation, choose Security > Users. 

 In the SAP Business Technology Platform (BTP) Account section, enter account ID. In our case SAP Analytics Cloud tenant was hosted on a non-SAP data center, hence our account ID is an email address.

 When the account ID is accepted, account information will be displayed.

Sakshi_Kathuria_2-1762844113468.png

Sakshi_Kathuria_3-1762844113472.png

Once all the details are entered, your subaccount addition in your Cloud Connector will look like below:

Sakshi_Kathuria_4-1762844113475.png

  • Configure System mapping.

Sakshi_Kathuria_5-1762844113476.png

Sakshi_Kathuria_6-1762844113477.png

Add the internal host name of the Tomcat server where the SAP Analytics Cloud agent is running.

The default Tomcat port is 8080 and the default HTTPS port is 8443.

Sakshi_Kathuria_7-1762844113480.png

Enter the virtual host and port so that the internal host name and port are not exposed. 

Sakshi_Kathuria_8-1762844113483.png

Sakshi_Kathuria_9-1762844113484.png

Sakshi_Kathuria_10-1762844113485.png

Sakshi_Kathuria_11-1762844113486.png

Sakshi_Kathuria_12-1762844113489.png

Agent is reachable.

Sakshi_Kathuria_13-1762844113492.png

Add resource.

Sakshi_Kathuria_14-1762844113494.png

3. Installing and configuring SAP Analytics Cloud Agent on Linux Server

You can verify the installation by opening the following URL in your browser: http://<Host>:<Port>/C4A_AGENT/deploymentInfo

Sakshi_Kathuria_15-1762844334164.png

The version of the SAP Analytics Cloud agent installed is displayed.

  • Also verify if JRE, Tomcat are installed on the Agent server.

Sakshi_Kathuria_16-1762844334165.png

Sakshi_Kathuria_17-1762844334170.png

  • Log on to SAP Analytics Cloud with an administrator account.
  • From the side navigation, choose System  Administration -->  Data Source Configuration.
  • In the On-Premise Data Sources section, Add a new location.

You can connect to multiple locations that have on-premises data sources. Each location that you add should already have a Cloud Connector installed and configured on it.

You can also leave the Location ID blank to create a Default Location. This Default location will correspond to your subaccount on the SAP Cloud Connector with a blank Location ID.

Sakshi_Kathuria_20-1762844334177.png

  • Make sure Enable Agent is set to ON and enter the virtual hostname and Port that you created during Cloud Connector Configuration along with the Agent username password specified in tomcat-users.xml file.

Sakshi_Kathuria_21-1762844334182.png

Sakshi_Kathuria_22-1762844334184.png

Sakshi_Kathuria_23-1762844467694.png

4. Make sure JDBC driver is installed.

  • Install JDBC drivers if not already based on the JVM version.

https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?vie...

The jar files can be in any location on your server as long as the properties file includes the correct file locations.

We kept it in SAC agent installed directory and gave permission 777 and ownership as sidadm:sapsys.

  • Create a properties file that specifies the paths to JDBC drivers you want to use and place it in the same file system where the SAP Analytics Cloud agent is installed. The properties file can have any name, such as DriverConfig.properties.

Sakshi_Kathuria_24-1762844467699.png

  1. You must restart the SAP Analytics Cloud agent, using the Java option -DSAP_CLOUD_AGENT_PROPERTIES_PATH since the agent is installed on Linux.

Navigate to tomcat/bin.

Run the shutdown.sh script.

Sakshi_Kathuria_25-1762844467701.png

Open the catalina.sh file in the tomcat/bin directory and find the line where Java options are set. Modify this line so that the -DSAP_CLOUD_AGENT_PROPERTIES_PATH option is included and points to your .properties file.

 

Sakshi_Kathuria_26-1762844467703.png

Run the startup.sh script.

Sakshi_Kathuria_27-1762844467706.png

5. Creating the Import Data Connection in SAC.

Sakshi_Kathuria_28-1762844467708.png

Sakshi_Kathuria_29-1762844467710.png

Sakshi_Kathuria_30-1762844467714.png

Sakshi_Kathuria_31-1762844467718.png

1 Comment