Blag's bag of rants: A Shiny example

miércoles, 13 de febrero de 2013

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"...

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") ) ))

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) }) })

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.