Skip to Content
Author's profile photo Alvaro Tejada Galindo

A Shiny example – SAP HANA, R and Shiny

As you may already know…I love R…a fancy, open source statistics programming language. So today, I decided to learn something new using R.

There aren’t much Web Servers for R, but there’s one that I really like called Rook, that I covered on my blog RSAP, Rook and ERP.

Today, I tried a new one that’s is making a lot of noise in the R community, called Shiny. I gotta say…I felt instantly in love with it…

So you may ask…so what’s so cool about Shiny? Well…besides the fact that it allows you to create web applications using R…it’s completely dynamic…meaning that once you change a parameter, the graphic and not the whole web page is reloaded automatically…awesome, huh?

So…when I wrote about Ruby and SAP in my blog Ruby joins the SAP HANA party, I tried to emulate an SE16 to browse tables from the SFLIGHT package…this time…I will do the same but with a little twist…the application will allow you to choose a table, but also to choose how many records you want to display…

So, what we need? Simply…if you haven’t already…install this two packages…”shiny” and “RODBC”…

Shiny_SAPHANA_00.png

After this, we need to create a folder called Shiny and after this create a new one called SAP_HANA_R (This is just to be organized).

We now need to create two files, called ui.R and server.R

ui.R

library(“shiny”)

library(“RODBC”)

ch<-odbcConnect(“HANA_TK”,uid=”SYSTEM”,pwd=”manager”)

odbcQuery(ch,”SELECT table_name from SYS.CS_TABLES_ where schema_name = ‘SFLIGHT'”)

tables<-sqlGetResults(ch)

odbcClose(ch)

shinyUI(pageWithSidebar(

  headerPanel(“SAP HANA and R using Shiny”),

  sidebarPanel(

    selectInput(“Table”, “Choose a table:”,

                choices = tables$TABLE_NAME),

    numericInput(“Records”, “Number of Records to view:”, 10)

  ),

  mainPanel(

    tableOutput(“view”)

  )

))

server.R

library(“shiny”)

library(“RODBC”)

shinyServer(function(input, output) {

  output$view <- reactiveTable(function() {

    ch<-odbcConnect(“HANA_TK”,uid=”SYSTEM”,pwd=”manager”)

    schema_table<-paste(“SFLIGHT.”,input$Table,sep=””)

    query<-paste(“SELECT TOP”,input$Records,”* FROM”,schema_table)

    odbcQuery(ch,query)

    result<-sqlGetResults(ch)

    odbcClose(ch)

    head(result, n = input$Records)

  })

})

When we have finished with the two files…we can create a new one just to call our application.

Shiny_HANA.R

library(shiny)

setwd(“C:/Blag/R_Scripts”)

runApp(“Shiny/SAP_HANA_R”)

Keep in mind that the setwd(“C:/Blag/R_Scripts”) is my main R Script folder, as setwd stands for “Set Working Directory”…

When we run Shiny_HANA.R, the browser will popup showing the parameters and the table by default.

Shiny_SAPHANA_01.png

As you can see, we can choose a new table to display.

Shiny_SAPHANA_02.png

We can also choose how many lines or records we want to display…

Shiny_SAPHANA_03.png

Shiny_SAPHANA_04.pngI hope you like it… 🙂

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Andy Silvey
      Andy Silvey

      Hi Blag,

       

      another of your excellent blogs.

       

      Question, how are Hana and R doing, I remember their first kiss, but how is their relationship progressing, obviously they're still together, but do you know if they have any plans for the future, wedding bells for example 🙂

       

      We need to be kept informed on the relationship too 🙂

       

      All the best,

       

      Andy.

      Author's profile photo Alvaro Tejada Galindo
      Alvaro Tejada Galindo
      Blog Post Author

      Andy:

       

      Glad you liked my blog As for SAP HANA and R...well...I haven't talked with "R in SAP HANA" team in a long time...so I'm not really sure is there any news or not...will try to contact them and hopefully I will post a new blog with the news

       

      Greetings,

       

      Blag.

      Author's profile photo Kumar Mayuresh
      Kumar Mayuresh

      Hi  Alvaro

       

      Another great input from you.. its quite tough to follow your steps but yes I always try doing that.

       

      Seems after HANA and R first kiss ... this is next step in their relationship...considering the date 14th feb .

       

      Keep writing and make us all learn with time ...

       

      Regards

      Kumar.

      Author's profile photo Alvaro Tejada Galindo
      Alvaro Tejada Galindo
      Blog Post Author

      Kumar:

       

      Thanks I'm always trying to do something new...and that's one of the trickiest parts of my job...each morning I woke up thinking..."What can I blog about today?!"

       

      I know...SAP HANA and R should get finally married or something...as I told Andy...will try to contact the "R in SAP HANA" team...I really don't have a clue if there is something else new...but I will inform of any input that I get from them

       

      Greetings,

       

      Blag.

      Author's profile photo Former Member
      Former Member

      Hi,

       

      have you tried to access any of the advanced features of HANA such as analytical views, SQLScript procedures in R? What is the most advantages access technology ODBC, MDX, AXML?

       

      Albrecht

      Author's profile photo Alvaro Tejada Galindo
      Alvaro Tejada Galindo
      Blog Post Author

      Albrecht:

       

      Yes I have And actually Lars Breddemann explained it in a very easy way in one of his comments http://scn.sap.com/community/hana-in-memory/blog/2012/01/29/r-meets-hana#comment-334850

       

      For SQLScripts procedures in R...I not sure call them from R, but you can read this http://scn.sap.com/community/developer-center/hana/blog/2012/05/21/when-sap-hana-met-r--first-kiss you do your coding on the SAP HANA Studio using R as the script language...so in the end you can mix Stored Procedures and R code...

       

      For R, right now you have two options...ODBC or an R Server accessed by SAP HANA Studio using RServe...

       

      Greetings,

       

      Blag.

      Author's profile photo Former Member
      Former Member

      Alvaro,

       

      thanks for pointing me in the right direction.

       

      Albrecht

      Author's profile photo Former Member
      Former Member

      Really nice framework for R.  Thanks Alvaro - you keep coming up with these gems.

       

      Cheers,

      Piers Harding.

      Author's profile photo Alvaro Tejada Galindo
      Alvaro Tejada Galindo
      Blog Post Author

      Thanks Piers I'm always trying to learn new things...and share them with the community of course

       

      Greetings,

       

      Blag.

      Author's profile photo Former Member
      Former Member

      Hello Blag,

       

      Another wonderful usecase

       

      Could you please point me to a link for establishing ODBC connection with HANA?

       

      I was able to establish jdbc connection and hence could built some simple UI to access HANA.... but in this case I am facing some issues.

       

      Thanks! - AO.

      Author's profile photo Former Member
      Former Member

      Hello Blag,

       

      Never mind my previous question.... I got the issue resolved.

       

      Just in case someone else faced similar issue, here is the problem statement and answer,

       

      Problem - Unanble to extablish ODBC connectivity to SAP HANA via R

       

      Why? - hdbodbc32 driver necessary to establish connection was not availabe in the ODBC Driver Adminstrator.

       

      Solution - Download and execute hdbsetup.exe. Driver will be then available.

       

       

      Thanks! - AO.