Analyze SAP Netweaver Gateway Data in R via JDBC

Access SAP Netweaver Gateway data with pure R script and standard SQL on any machine where R and Java can be installed. You can use the CData JDBC Driver for SAP Netweaver Gateway and the RJDBC package to work with remote SAP Netweaver Gateway data in R. By using the CData Driver, you are leveraging a driver written for industry-proven standards to access your data in the popular, open-source R language. This article shows how to use the driver to execute SQL queries to SAP Netweaver Gateway and visualize SAP Netweaver Gateway data by calling standard R functions.

Install R

You can match the driver's performance gains from multi-threading and managed code by running the multithreaded Microsoft R Open or by running open R linked with the BLAS/LAPACK libraries. This article uses Microsoft R Open 3.2.3, which is preconfigured to install packages from the Jan. 1, 2016 snapshot of the CRAN repository. This snapshot ensures reproducibility.

Load the RJDBC Package

To use the driver, download the RJDBC package. After installing the RJDBC package, the following line loads the package:

library(RJDBC)

Connect to SAP Netweaver Gateway as a JDBC Data Source

You will need the following information to connect to SAP Netweaver Gateway as a JDBC data source:

• Driver Class: Set this to cdata.jdbc.sapgateway.SAPGatewayDriver
• Classpath: Set this to the location of the driver JAR. By default this is the lib subfolder of the installation folder.

The DBI functions, such as dbConnect and dbSendQuery, provide a unified interface for writing data access code in R. Use the following line to initialize a DBI driver that can make JDBC requests to the CData JDBC Driver for SAP Netweaver Gateway:

driver <- JDBC(driverClass = "cdata.jdbc.sapgateway.SAPGatewayDriver", classPath = "MyInstallationDir\lib\cdata.jdbc.sapgateway.jar", identifier.quote = "'") 

You can now use DBI functions to connect to SAP Netweaver Gateway and execute SQL queries. Initialize the JDBC connection with the dbConnect function.

SAP Gateway allows both basic and OAuth 2.0 authentication. You can use basic authentication to connect to your own account, or you can use OAuth to enable other users to retrieve data from your service with their accounts. In addition to authenticating, set the following connection properties to access SAP Gateway tables.

• Url: Set this to the URL of your environment, or to the full URL of the service. For example, the full URL might appear as: https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/. In this example, the environment URL would just be: https://sapes5.sapdevcenter.com.
• Namespace: Set the appropriate Service Namespace. In the example above, IWBEP is the namespace. It is optional if the full URL to the service is specified.
• Service: Set this to the service you want to retrieve data from. In the example above, the service is GWSAMPLE_BASIC. It is not required if the full URL is specified.

Authenticate via Basic Authentication

In basic authentication, you use your login credentials to connect. Set the following properties:

• User: This is the username you use to log in to SAP Gateway.
• Password: This is the password you use to log in to SAP Gateway.

Authenticate via OAuth Authentication

You can connect to SAP Gateway using the embedded OAuth connectivity (without setting any additional authentication connection properties). When you connect, the OAuth endpoint opens in your browser. Log in and grant permissions to complete the OAuth process. See the OAuth section in the online Help documentation for more information on other OAuth authentication flows.

Built-in Connection String Designer

For assistance in constructing the JDBC URL, use the connection string designer built into the SAP Netweaver Gateway JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

java -jar cdata.jdbc.sapgateway.jar

Fill in the connection properties and copy the connection string to the clipboard.

👁 Using the built-in connection string designer to generate a JDBC URL (Salesforce is shown.)

Below is a sample dbConnect call, including a typical JDBC connection string:

conn <- dbConnect(driver,"jdbc:sapgateway:User=user;Password=password;URL=https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/;InitiateOAuth=GETANDREFRESH;")

Schema Discovery

The driver models SAP Netweaver Gateway APIs as relational tables, views, and stored procedures. Use the following line to retrieve the list of tables:

dbListTables(conn)

Execute SQL Queries

You can use the dbGetQuery function to execute any SQL query supported by the SAP Netweaver Gateway API:

salesorderlineitems <- dbGetQuery(conn,"SELECT ProductID, Quantity FROM SalesOrderLineItems WHERE Quantity < 15")

You can view the results in a data viewer window with the following command:

View(salesorderlineitems)

Plot SAP Netweaver Gateway Data

You can now analyze SAP Netweaver Gateway data with any of the data visualization packages available in the CRAN repository. You can create simple bar plots with the built-in bar plot function:

par(las=2,ps=10,mar=c(5,15,4,2))
barplot(salesorderlineitems$Quantity, main="SAP Netweaver Gateway SalesOrderLineItems", names.arg = salesorderlineitems$ProductID, horiz=TRUE)

👁 A basic bar plot. (Salesforce is shown.)