Querying IBP data from Excel via OData API Webservices
Daniel Lellouche
Principal Consultant and Architect at Camelot Consulting - Senior Manager
My recent post about connecting IBP and Excel by means of API raise about 40 comments, and 5500 of you read the pre-read article. Thank so much. So, this article is the complete content. Enjoy!!
Background
Accessing IBP data is nothing complicated when simply using the IBP Excel Addin, indeed! When you do so, the data flow is controlled by SAP-IBP custom development inherited from EPM, so-called IBP Excel Addin. From the selection of key figures and attributes you set in the IBP Planning view, data are fetched from the HANA IBP database and placed in Excel in a custom and SAP format.
On the other hand, you may also know Excel is not only a simple spreadsheet, with worksheets and formulas. With this "magic" business tool, you can also perform data mining, reporting, and analysis, and even switch to Power Pivot if you wish to address big volumes of data. ??
Objectives
Now let's imagine you wish to query data coming from IBP the same way you do from other data sources like Salesforce, Access, Power, Azure, etc.., with the intention to join these data together in order to perform advanced data mining activities like; drill down, drill up, browse through, checks, top 10, sort, grouping, etc... Typically we run data mining to check master data and transactional data healthiness. Establish KPIs collection. Validate planning options through data crunching etc…
Some may say if you create key figures and formulas in IBP and… And you must revert to IT anytime you have a little need or change. Not exactly the spirit we are after with data mining. We need easy access to data.
On the other hand, the SAP-IBP standard option by means of IBP Excel Addin is not an option anymore! If you try to extract data with this add-in, you are wasting your time and surely heading toward frustrations. The IBP Excel Addin is only made to use IBP data in a planning framework.
Way forward
Hum Daniel, what is then the way forward to do so?
Frankly, it took me a couple of hours to discover the fruitful below option with IBP itself!! Yes! Sometimes we get good satisfaction from SAP Products!
The base idea I describe below will help you to set up and activate the API Webservices of IBP, as a source of data for key figure extraction, directly usable in Excel or any other tool that accepts Odata format.
From a performance perspective, this is not any different compared to the connectivity between SAC and IBP. In other words, it should not be hitching.
Step by Step
Here is the section you asked me to write ??.
Step 1- Configuring a Communication User
Running API Webservice is not free access to IBP data, obviously not. So, in the setting, you first need to create a “Communication User”. There are a few options however the basic setting is already good for testing. Later you can define certificates and advanced settings. Here below I created DLEL_API users and got proposed a password by clicking the below button.
Step 2- Configuring a Communication system in IBP.
A bit like we need to create logical systems in ABAP with transaction SM59 when it comes to connectivity, here in IBP you need to create a so-called “Communication system”. Again, the standard setup works fine straight forward. I did not enter more than what you see below although you can define advanced options in this communication system. If so, you need help from your IT support and security administration likely.
When creating the communication system, you need to attach the communication user created in Step 1.
Later when calling the API from Excel you will have to use this user to connect to IBP.
Step 3- Configuring a communication arrangement.
In this step, you instruct IBP on what kind of communication (API methods) you intend to use. SAP proposes several scenarios, so-called communication scenarios. You can only use one of the predefined scenarios within your communication arrangement. In other words, if you wish to activate several different scenarios to use the available API Webservices from IBP, you will have to create one communication arrangement per scenario you wish to use. See below 2 communication arrangements, based on scenario SAP_COM_0143 that allows you to extract key figure data, and even upload in IBP!
领英推荐
And specifically for scenario SAP_COM_0143 which is the one to use when extracting key figure data.
Pay attention to the highlighted URL at the bottom. These are to be used later. The technical configuration is now finished.
Step 4- Allowing the Planning area and MDT to be accessed by APIs.
From a configuration perspective, you now need to instruct IBP which planning area and which MDT prefix can be accessed by means of IBP APIs. The technical setup does not open the door to all of your data. So, to authorize the access, you need to maintain in the GLOBAL Configuration app, 2 parameters, as shown below, in yellow the planning area and in red the MDT prefix (DLE for me).
Step 5- Optional. Getting full technical information about standard API, and testing.
By using the following link, you will find a great source of information from SAP about the available API and web services for IBP and much more.
On this platform, you can even test some of the APIs, not all as the proposed testing environment is a SAP-owned one, not yours
Step 6- Optional. Test your own connectivity.
With the Postman platform https://www.postman.com/ you can start experimenting with your own API connection with your IBP instance. This tool is simple, I recommend it. See below an example of Postman utilization. Pay attention to using the GET method to extract data. Should you try to save data in IBP you would use POST or PUT.
Specifically in the GET, see at the end of the sequence /DLE?$select=PRDID,DEMAND,PERIODID1. Running through the documentation of the API in SAP above platform you will learn how to control what you want to extract. Here from the planning area DLE, I wish to extract the Product, Location, Year, and only DEMAND key figure, without any filtering.
Alternatively, you can directly place the URL mentioned above in Chrome, which will connect your IBP for sure, however, the resulting format is a little techy ??
Step 7- Finally use this in Excel.
Now all is working, you can open Excel, go in the Data ribbon and call for an Odata extraction.
And magic, once you signed in, 5’ later comes the desired result. See the initial picture of this article
Enjoy IBP!
If you like this article, “Like” it, “Repost” it, “Comment” it. We grow by exchanging opinions!