SAP HANA: My experiences on using SAP HANA with R

[Former Member]

Hello SCN,

Firstly, I thank Blag for wonderful blogs on HANA & R like HANA meets R and R meets HANA . *Which introduced me to this amazing language called “R”.

In this blog I will discuss about how ODBC helps HANA to connect with different tools like Crystal reports 2011, R etc.We will also discuss about creating a procedure in HANA and calling the same to create a table in SAP HANA database. We will use ‘R’ to read the data from HANA and to plot a graph on that table. Then we will understand different problems faced while trying to plot a graph on top of tables in SAP HANA database and what is the future road map of HANA & R.

1) ODBC and HANA:

Data Services supports several ODBC data sources natively, including:

• MySQL
• Neoview
• Netezza
• Teradata

Configuring HANA ODBC:

The following are the necessary credentials for configuring ODBC driver for HANA:

SERVER = <server_name>:3<xx>15
USER = <user_name>
Password = <password>

In my case it is:

SERVER=hanasvr-04:30015
USER=S0008208595
Password=********

Steps for Configuring Data sources for ODBC driver of HANA:

Go to Control Panel -> Data sources (ODBC)

The following screen will appear.

Now Press “Add” to add a new DSN based on ODBC Driver “HDBODBC32” which is a ODBC driver for HANA.

The following screen will appear where you will have to enter the DS name along with its description and Server name.

If you are still facing issues with “Server: Port” number, you can find the number in the properties tab of your system node in HANA
Studio as in the below screen.

With this we created our new data source for ODBC driver of HANA. We can test our connection here by pressing “Connect” in the above
screen. Which will navigate us to the below screen.

On pressing “OK”, we will get the message “Connect successful” as in the below screen.

Press “OK” to continue. With this we have successfully created a DSN for ODBC driver. We can now use this DSN to connect from R to SAP
HANA Database and read the tables.

We can also connect to Crystal reports 2011 with the help of this ODBC connection.

2)  Installing R and R STUDIO (GUI):

To use “R” (Similar to S) , we have to first install “R” language and then install the GUI (windows/Unix)
version.To install R, Use the link http://cran.r-project.org/  and for R STUDIO use the link http://rstudio.org/ .

Using RODBC package:

Now we have to install the Package “RODBC” for using ODBC Driver and connecting to SAP HANA Database. Download the package from RODBC and install it as shown below.

Now we are all ready to use our ODBC Driver and read the tables in SAP HANA Database from RSTUDIO and display
them in different plots or graphs.

Connection statement for SAP HANA Database:

Library ("RODBC")

ch<-odbcConnect("SVR3",uid="S0008208595",pwd="*******")

Here Ch is used for storing the necessary DSN name along with User id and Password to connect to SAP HANA
Database.

3) Talking with SAP HANA Database using R:

In this case I would like to create a procedure on SBOOK table in SFLIGHT, which shows the “Revenue per Agency”.  We will use this procedure to fill the table “FLIGHT” and connect to this table from R STUDIO and display the result in a plot.

Creating a procedure:

CODE:

CREATE PROCEDURE STOC

      (IN MANDT NVARCHAR

            (3),IN AGENCYNUM NVARCHAR

            (8),IN FORCURAM INTEGER,IN FORCURKEY NVARCHAR

            (5),IN NAME NVARCHAR

            (25),IN COUNTRY NVARCHAR

            (3),IN CURRENCY NVARCHAR

            (5)

      ) LANGUAGE SQLSCRIPT AS

BEGIN SELECT

       "SBOOK"."MANDT", "SBOOK"."AGENCYNUM", SUM

      ("SBOOK"."FORCURAM") as "FORCURAM", "FORCURKEY", "NAME", "COUNTRY", "CURRENCY"

FROM "SFLIGHT"."SBOOK", "SFLIGHT"."STRAVELAG"

WHERE "SBOOK"."AGENCYNUM" = "STRAVELAG"."AGENCYNUM"

AND "SBOOK"."MANDT" = "STRAVELAG"."MANDT"

GROUP BY "SBOOK"."MANDT","SBOOK"."AGENCYNUM","SBOOK"."FORCURKEY","STRAVELAG"."NAME","STRAVELAG"."COUNTRY","STRAVELAG"."CURRENCY"

into FLIGHT;

      END;

Create a table “FLIGHT” as shown below.

Now call the procedure to load “FLIGHT" table:

CODE:

CALLS0008208595.STOC ('300','000299', '123321','US', 'FLY','US','USD');

Now you can see the data in FLIGHT table.

Now connecting to SAP HANA Database from R STUDIO *

CODE:

library("plotrix")

library("RODBC")

ch<-odbcConnect("DS",uid="S0008208595",pwd="*******")

res<-sqlFetch(ch,"S0008208595.FLIGHT")

FORCURAM=res$FORCURAM

NAME=res$NAMe

barplot(res$FORCURAM,names.arg=res$COUNTRY,

main="FLIGHT REVENUE PER COUNTRY")

odbcClose(ch)

OUTPUT:

You can see in the above screen in console how it is getting executed.

In the next experiment I tried to the same on “BIG” data but it thrown me the following the error “finite 'xlim' ”. Means this bar plot doesn’t support BIG data plots.

My observations in this experiment:

1. I cannot use the views or procedures I have created using the wizard in HANA Modeler.
2. If data is more the graphical representation becomes clumsy.
3. I cannot use bar plot on BIG data as there is a possible limit as shown below when I tried to get the output on 900000 records.
4. I am able to communicate only with tables in SAP HANA Database
5. R language helps me to represent data mining techniques efficiently with the help of rich library of packages on different statistical formulas.

There are many tutorials available on R in the net for free as R is an open source. With SAP planning to tighten the integration between HANA and R, I hope this blog encourages you all to understand R and play with it on top of HANA.