Top Bottom Analysis using R Visualizations (part 1)

As a user of SAP Analytics Cloud you sometimes want specific visualizations tailored to your business needs. Have you tried the R Visualization functionality? It allows you to define your own custom charts with an R script. And if you don’t have an R server, don’t worry, SAP provides an R server runtime environment with pre-installed packages for data manipulation and data plotting.

This blog series shows how to build Top Bottom Analysis using the R server managed by SAP. Part 1 introduces our topic with multiple Top Five bar charts.

To start, we create a BI Model by following the path below from the main menu:
Create Model > Import a file > Select Source file > Import > Create Model

We name our model: MDL_US_EDU.

In case you want to run the example, the dataset is made available here.

Then, we insert an R Visualization by following the path below:
Create Story > Add a canvas page > Insert tab + > R  Visualization > Builder

We click on “Add Input Data” and select the dimension: State.

From the Builder we click on “Add a script”.

An R data frame object is automatically created using the information that was given in the Input Data panel. It is named after our BI Model. The structure of the data frame is shown in “Environment” one of the four panels of the R script editor.

We can now write our R script in the editor panel.

We load the R packages that we need for data manipulation and data plotting.

library(dplyr)
library(tidyr)
library(highcharter)

The R environment from SAP includes several graphing libraries: ggplot2, highcharter, plotly, lattice. In our case, we loaded the package: highcharter.

We compute the top 5 states by education.

top_limit = 5
df <- MDL_US_EDU %>%
   gather(Education, Rate, -State) %>%
   group_by(Education) %>%
   mutate(Rank = row_number(-Rate)) %>%
   ungroup %>%
   filter(Rank <= top_limit) %>% 
   mutate(Rate = Rate * 100) %>%
   select(-Rank) %>%
   arrange(desc(Education), desc(Rate)) %>%               
   mutate(Education = factor(Education, unique(Education)))

We prepare the list of top states by education level, and the chart title.

grouping <- df %>%
  group_by(name = Education) %>%
  do(categories = .$State) %>%
  list_parse()

title_caption = paste("Literacy - The top", top_limit, "states by education level")

Finally, we plot the data using the theme: chalk.

hchart(df, "bar", hcaes(x = State, y = Rate), name = "Rate") %>% 
  hc_title(text = title_caption , 
           margin = 20, align = "center") %>% 
  hc_plotOptions(series = list(dataLabels = list(enabled = TRUE, color = "white", style = list(fontSize = "18px"), format = "{y}%"))) %>%
  hc_xAxis(categories = grouping, title=list(text = NULL), tickLength = 0, 
           lineWidth = 3, lineColor = "black",
           labels = list(
		            style = list(fontSize = "20px"),
		            groupedOptions = list(list(style = list(fontSize = "20px"))) )) %>%
  hc_tooltip(enabled = FALSE) %>% 
  hc_add_theme(hc_theme_chalk())

To obtain this chart in SAP Analytics Cloud, you must assemble the different code snippets given above into a single R script and hit the Execute or Apply button.

Continue to part 2