Following on from my last post Analytics with SAP and R using the RSAP RFC based connector, I wanted to expand on the simple example that was based on reading tables in SAP with something a little more meatier (apologies to vegetarians).
The basic premise of the RSAP connector is that the RFC that you want to use must already exist giving you the data that you require, or we must fall back on trusty old friends like RFC_READ_TABLE that allow us to have direct (inefficient) access to the underlying tables. However, even working with in this restriction, we can do a bit better than this, and the help comes in the form of other generic interface RFCs that are available for BI and reporting.
The function module RSDPL_CUBE_DATA_READ gives us access to the basic underlying data of a BI Cube. This opens up all sorts of possibilities, as we can use the BI engine to do the heavy lifting through ETL, and Cube formation. This will conveniently get the right kinds of data into an efficient state so that we can then execute targeted queries for advanced analytics with R. I think one of the most attractive properties of this solution is that it opens up a new world of data analysis possibilities by bringing to bear all the features that R has to offer, on the investment that businesses have already made when implementing the SAP BI solution - an opportunity to extend the ROI by extracting more value.
To encapsulate this, I have added a new function to RSAP - RSAPReadCube(). This is passed a connection object, and lists of characteristics and key figures that are desired in return. Additionally, an options list can be supplied for limiting the query results. The result set is a data.frame. Lets walk through the following example, which is based on the SAP Gateway test drive demo system, which provides the demo data:
0D_NW_T01 - Plan cube
0D_NW_C01 - Actual cube
Instructions on how to generate this demo content can be found here .
The code goes as follows:
# get the Net Value key figure from the plan and actual cubes, and plot a comparison time series.
# load the library, set working directory for location of connection properties file sap.yml, and establish
# RFC connection
library(RSAP)
setwd('/home/piers/git/public/RSAP')
conn <- RSAPConnect("sap.yml")
# retrieve the net value key figure, sales org by month from both the plan and actual cubes. Then
# close the connection
actual <- RSAPReadCube(conn, '0D_NW_C01',
chars=list('0CALMONTH', '0D_NW_SORG'),
kfigures=list('0D_NW_NETV'))
plan <- RSAPReadCube(conn, '0D_NW_T01',
chars=list('0CALMONTH', '0D_NW_SORG'),
kfigures=list('0D_NW_NETV'))
RSAPClose(conn)
# aggregate the net value by month, and build a time series object
a <- aggregate(actual$D_NW_NETV,
by=list(month=actual$CALMONTH), FUN="sum", na.rm=TRUE)
p <- aggregate(plan$D_NW_NETV,
by=list(month=plan$CALMONTH), sum, na.rm=TRUE)
plan_vs_actual <- ts(data.frame(plan=p$x, actual=a$x), start=c(2006,01), deltat=(1/12))
# plot the time series plan vs actual
ts.plot(plan_vs_actual, gpars=list(yaxt="n", lty=1:2, col=1:2, ann=FALSE))
lbls <- round(seq(0, max(plan_vs_actual[,'plan'])/1000000, length=10))
lblsat <- round(seq(0, max(plan_vs_actual[,'plan']), length=10))
axis(2, at = lblsat, labels = lbls, tick=TRUE)
title("Plan vs Actual \n Net value", xlab="Time (months)", ylab="EUR (mil)")
legend(2007, 30000000, c('Plan', 'Actual'), lty=1,cex=0.8, col=1:2, title="Plan vs Actual")Which gives us this result:
So - with a few simple lines of code, you have access to the whole power of the BI engine right there on your desktop.
