Skip to Content

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)

A1.png

The following screen will appear.

A2.png

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

A3.png

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

A4.png

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.

A5.png

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.

A6.png

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

A7.png

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.

A8.png

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:

A9.png

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.

A10.png

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.

A11.png

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:

A13.png

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.

http://hanahangout.com/wp-content/uploads/2012/03/a1i14.jpg

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.

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

  1. Alexander Gossmann

    Hi Krishna,

    thanks a lot, did you find a way to consume HANA Views throgh sqlFetch?  I tried _SYS_BIC.package/VIEW but get always table not found on channel message.

    Best regards

    Alex

    (0) 
  2. Tina Wu

    Hi Krishna,

    Thanks, but there’s a problem when I execute your command as follows .


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


    But I have not found a Driver called “HDBODBC32”, my screenshot like this.

    Capture.PNG

    So where is wrong in my process?

    Or if I need to download a HANA Driver?

    Best regard,

    Tina

    (0) 
    1. Krishna Tangudu

      Hi Tina,

      Do you have HANA client installed in your PC?

      you will find HDBODBC if you have installed 64 bit client? The screenshot i showed you is showing 32 bit driver.

      Regards,

      Krishna Tangudu

      (0) 
      1. Tina Wu

        Hi Krishna,

        I have already resolved the problem by your reply. Now I can successfully connect to HANA Studio using R.

        Thanks very much!

        Best regards,

        Tina

        (0) 
      2. Sandip Sahoo

        Hello Krishna,

        I was trying to connect R with HCP (Hana Cloud Platform). I have hana cloud connector installed in my machine. But I don’t see any JDBC/ODBC driver (HDBODBC) in my machine .And also please tell me at what point of time this driver HDBODBC will be available in my machine. Do I need to create the tunnel after that  then only HDBODBC will be available? Please guide me.

        Regards

        -Sandip

        (0) 
    1. Sandip Sahoo

      Hello Imane,

      I was trying to connect R with HCP (Hana Cloud Platform). I have hana cloud connector installed in my machine. But I don’t see any JDBC/ODBC driver (HDBODBC) in my machine .And also please tell me at what point of time this driver HDBODBC will be available in my machine. Do I need to create the tunnel after that  then only HDBODBC will be available? Please guide me.

      Regards

      -Sandip

      (0) 
  3. Jorge Lizama

    Hi, this is just an FYI to connect to views, you can connect to views using the schema path (_SYS_BIC), below a couple of lines that you can use once you have already established the connection (odbcConnect), also recommended is to use the sqlQuery instead of sqlFetch

    1. library(“RODBC”
    2. ch<-odbcConnect(“DNSCONNECTIONNAME”,uid=“USER”,pwd=“PASSWORD”
    3. sql<-‘SELECT * FROM “_SYS_BIC”.”PACKAGENAME/VIEWNAME”‘
    4. res<-sqlQuery(ch,sql)
    (0) 

Leave a Reply