Integrating SAP IBP with Data Visualization Tools using OData Services

[piyush_parekh]

Data visualization helps in representing large amount of data and garner critical insights like trend, pattern and outliers instinctively. In this blog post, we will learn how to integrate SAP IBP with some of the data visualization tools.

SAP IBP control tower module helps to visualize planning data without any need of integration. OData services are useful in cases client uses an analytical tool like SAP Analytics Cloud for which data needs to be retrieved from various systems. SAP introduced OData services for key figure data extraction in v1802. Now, you can have an overview of SAP IBP data along with the data from other systems, displayed on the same dashboard!

There are two parts of integration.

Part 1: Setting up OData services in SAP IBP

Part 2: Extracting data from SAP IBP

I used RStudio for part 2. Planners are free to use any tools for data extraction, transformation and visualization.

Let’s discuss each integration step in detail.

Part 1:  Setting up OData Services in SAP IBP

Pre-requisite: Catalogs associated with communication management functionalities are assigned to the user.

Step 1: Maintain Communication Users

Create a communication user by maintaining username of your choice, description and password. Username and password will be used in inbound communication for authentication to fetch data from SAP IBP. You can either setup a password of your choice or use a system generated password.



Step 2:  Create Communication System

Navigate to communication system fiori app. Create a communication system by entering following details:

System ID: <text>

System Name: <text>

Host Name: myXXXXXX-api.scmibp.ondemand.com (Enter 6 digits of your SAP IBP system URL in place of XXXXXX)



Under “User for Inbound Communication” section, add newly created communication user in step 1 and select Authentication Method as User ID and Password. Save the profile.



Step 3: Create Communication Arrangement

Click on New to create a new communication arrangement. Enter arrangement name and select Scenario ID as SAP_COM_0143. Further, in Common Data section, select the communication system created in step 2. As soon as you select communication system, Inbound Communication fields will get auto-populated. Save the communication arrangement.



That’s it!! You’ve successfully established OData connection. In part 2, we’ll test our connection and extract sample data from “ACTUALSQTY” key figure at “Brand” and year level.

 

Part 2: Extracting data from SAP IBP

Here, I have illustrated a basic R code to fetch data from SAP IBP. Step 2 explains how required elements containing key figure data is extracted from response file and transformed to a data frame.

Pre-requisite: Install R, RStudio and basic knowledge of working with R packages (httr, jsonlite, ggplot2)

Step 1: Fetch data from SAP IBP

Install and call required R packages.

#1. Calling required packages

library(httr)

library(jsonlite)

library(ggplot2)

Following parameters are mandatory to provide for fetching key figure data from SAP IBP.

1. Business User ID


2. Planning area in which target key figure belongs


3. UOMTOID in which data to be extracted


4. CURRTOID in which data to be extracted


5. Target Key Figure ID


6. At what level data to be extracted including period

Once you have above details, enter this information against respective fields in R code.

#2. Building URL

baseurl <- "https://myXXXXXX-api.scmibp.ondemand.com/sap/opu/odata/IBP/EXTRACT_SRV/extract_kf"

BUSINESS_USER <- "<business user ID>" 

PLAREA <- "<your planning area>"

UOMTOID <- "PC"

CURRTOID <- "USD"

KF_IN <- "ACTUALSQTY"

ATTR_IN <- "BRAND"

PERIOD <- "PERIODID1"

Let’s merge all filter parameters in a single URL.

#3. Single URL

call1 <- paste(baseurl,

               "?$filter=BUSINESS_USER%20eq%20%27",BUSINESS_USER,

               "%27%20and%20PLAREA%20eq%20%27",PLAREA,

              "%27%20and%20CONVERSION_ATTRIBUTES%20eq%20%27%5B%20%7B%22ATTRIBUTE%22%3A%22UOMTOID%22%2C%22VALUE%22%3A%22",UOMTOID,

              "%22%7D%2C%20%7B%22ATTRIBUTE%22%3A%22CURRTOID%22%2C%22VALUE%22%3A%22",CURRTOID,

              "%22%7D%5D%27%20and%20KF_IN%20eq%20%27",KF_IN,

              "%27%20and%20ATTR_IN%20eq%20%27",ATTR_IN,

              "%2C",PERIOD,

              "%27&saml2=disabled",sep = "")

Assign values to variables username and password for authentication. These are credentials of communication user created in step 1 of part 1.

#4. Calling API

username <- "<your communication user username>"

password <- "<your communication user password>" 

get_data <- GET(call1, authenticate(username,password, type = "basic"))

Execute the code. It might take some time to get the response depending on data volume.

Step 2: Extracting relevant details from API response

Response file from SAP IBP may contain additional information. Refer this link to see how a detailed response looks. Next set of code extracts necessary key figure data from response you get from SAP IBP.

#5. Extracting key figure data series 

get_data_parse <- content(get_data, as = "parsed")

finaldata <- get_data_parse[["d"]][["results"]][[1]][["SERIE"]]

To analyze or visualize this data in RStudio, create a data frame.

#6. Creating data frame 

df <- fromJSON(finaldata)

Run print(df) and you will see key figure data in a table format.

Step 3: Data transformation and visualization

This is a sample transformation code. Modify this code as per your requirement. We will change column header names and convert Actuals Qty data frame to continuous variable.

#7. Transforming data to required format

colnames(df) <- c("brand","year","time1","actualsqtydf")

series <- df$actualsqtydf

actualsqty <- series$VALUE

keeps <- c("brand","year")

df2<- df[keeps]

df2$actualsqty <- actualsqty

df2$actualsqty <- as.numeric(as.character(df2$actualsqty))

We are ready to plot our first chart. I have used ggplot2 package and plotted a stacked column chart.

#8. Sample data visualization

ggplot(df2, aes(year,actualsqty,fill=brand))+ geom_col()

This is how the plot looks like:



Integration with Tableau

A Web Data Connector (WDC) is used to establish a direct connectivity between Tableau and SAP IBP. To build WDC from scratch, a fair knowledge of HTML and Javascript is necessary. HTML controls UI part of the connector while Javascript is used for defining extraction logic and transforming data in a format that can be consumed by Tableau.



Limitations of IBP OData services:

1. You should not use OData services for mass data extraction. It is not a replacement of CPI/SDI.


2. There could be a limitation on number of API calls users can make in a given timeframe and number of records that can be extracted in an instance.


3. As of now, you can only export data using OData services. Write OData services are planned to be released in 1908.

I hope this article helps you understand end-to-end process of visualizing planning data outside SAP IBP. Share your thoughts and experiences on tools your client uses for data visualization.