Hit the ground running - a quick start guide for IBP time series data integration

[Alecsandra]

SAP Integrated Business Planning is usually integrated with various systems in order to support data exchange, for example SAP APO, SAP ERP or non-SAP systems.

With this entry, I hope to facilitate the onboarding process of the new consultants in the area of IBP by explaining the principles of data integration.

There are several ways to send data to IBP: CPI-DS (Cloud Platform Integration for Data Services), open API (available since IBP 6.1) or web UI Data Integration app. The last one, even if it is the easiest to use, it is not automated and not suitable for large uploads.

I will keep the focus on ETL- like integration using CPI-DS tool. In the first part, I will discuss master data load while in the second one I will cover the transactional data.

Data load into IBP core tables goes through the staging tables, the so-called "landing zone". From here, after integrity checks, data will be either moved to the core tables or rejected.

 



 

Loading Master Data

 

In IBP we distinguish between several types of Master Data. Depending on the type, there are some dependencies when loading data:

• Simple


• Compound: loading data in compound tables requires first existing records in the simple tables


• Reference: they are referencing to already existing master data, not in scope of data load


• Virtual: their content is generated based on a JOIN condition between existing master data tables, not in scope of data load


• External: used for near real-time integration, not in scope of CPI-DS

 

At CPI-DS datastore level - metadata import, we can recognize the staging tables by the prefix STAG (e.g: SAPSOPG.SOPMD_STAG_PRODUCT). Only these tables can be used as a target.

 

In a CPI-DS dataflow, it is mandatory to map the master data attributes that are marked with Key and/or Required at master data definition level.



 

Loading Key Figures

 

Key Figures are objects that hold transactional data and are determined by a particular combination of attributes and time period values. They can be loaded into IBP, calculated on the fly or hold values computed by operators (like Unconstrained Heuristics output key figures).

Only stored Key Figures (or both Stored and Calculated) can be used as a target for data load. The staging table for key figures is found under Timeseries Folder and there is one for each version available in the planning area (e.g: SAPSOPG.SOPDD_STAGING_KFTAB_SM1BASESAP1)

 

Loading values into a key figure has to be done at the granularity of the base planning level. The root attributes of the base planning level act as a compound primary key for the key figure. This means that every single combination must be unique.

However, since IBP 6.1 data load is possible also on an aggregated level but only from a time dimension perspective.

Let's consider the example below.

Actuals Qty is a stored key figure in the base planning level PERPRODCUST. This planning level is defined by PRDID - CUSTID attributes and Monthly as the lowest time dimension where the Key Figure is stored. It is mandatory to provide valid mapping for these 3 dimensions within the target query.

For this key figure data, the load is supported on an aggregate time level like Quarter or Year (prerequisite is to define the time profile after guideline provided in 6.1 configuration guide).



 

Global Variables

There are several global variables needed by IBP application to process data once it has been loaded into the staging tables, While some of them are mandatory (like the ones listed below), some are optional and/or can be user-defined.

• $G_PLAN_AREA - mandatory only for transactional data load as master data can be cross planning areas


• $G_TIME_PROFILE


• $G_BATCH_COMMAND - in IBP we have the following data load options: INSERT_UPDATE, DELETE and REPLACE. Purge is also supported by IBP but is usually executed from web UI, giving that it is deleting all transactional data before a specific date.


• $G_LOAD_DATE

The full list and more details can be found on SAP IBP help page under the Integration section Please refer to Data Integration scenarios and SAP Cloud Platform Integration Guide. 



Other learning resources on SCN:

CPI-DS section under IBP FAQ: SAP Integrated Business Planning FAQs

Getting started on CPI-DS which summarizes existing official documentation and it is constantly updated with latest release features: Getting Started on HCI

Regards

Alecsandra

 

 

[SatishWaghmare]

Many Thanks Alecsandra for sharing this valuable information. Looking forward to your future posts in HCI area. Thanks again.

- Satish Waghmare

[Alecsandra]

Hi Satish,

Is there any specific topic you would like to see in more details?

Regards

Alecsandra

[Former Member]

Alecsandra, Simple and easy to understand this blog. Thanks!

Is there a possibility to make the scheduled task or HCI Process to wait from execution until it finds it source file in the Shared Drive? I was trying to call functions like file_exists / wait_for_file from a script in the HCI process but no luck, running into syntax errors.

Do we have a list of functions that can be called from the pre-loaded scripts?

Thanks,

Kiran

[former_member123657]

Great Information, Alecsandra!

[Former Member]

Thank you Alecsandra for the post. Specifically on loading data in higher time bucket is there any SAP note which talks in detail about this new feature or is it covered in the configuration guide?.

Thanks,

Srikant

[Irmi_Kuntze]

You will find in the new model config guide of IBP 6.1 global parameter INTEGRATION with KF_UPLOAD_EXT_AT_BASE_LEVEL_ONLY and KF_UPLOAD_INT_AT_BASE_LEVEL_ONLY. Read the documentation of that. Plus chapter 17.1 "Aggregation and Disaggregation of Data Across Different Time Levels" gives some further insights

[Alecsandra]

Hi Srikant,

Assuming that IBP modelling is already done as per configuration guide (http://help.sap.com/download/ibp/ibp61_model_conf.pdf see chapter 17.1), loading data from HCI on aggregate level is possible with the usage of $G_TIME_PROFILE_LEVEL global variable. This global variable is integer type and represents the TP level as displayed in the level column in web ui time profile config.

Regards

Alecsandra

[Alecsandra]

Hi Kiran,

After upgrade to 1.0.11 also the application help has been enhanced. You will find wait_for_file listed there.

Regards

Alecsandra

[Former Member]

Hi, Alecsandra

Thank you for good information.
Actually, we are using HCI to load the local data into a key figure of IBP now and researching how to configure HCI then I found this blog.

We need some information regarding loading CSV file into key figure.
If you can, could you share the information about below issue?

<our situation>
- goal is to load CSV file into key figure of IBP
- use template task "SOP_File_Task"
- use staging table for key figures which is found under Timeseries Folder like SAPSOPG.SOPDD_STAGING_KFTAB_*.

Now we are facing below error.

2016-12-21 08:51:04 (E)(48968:32844) VAL-30177 |SESSION Y_TSK_LPS_KF|DATAFLOW Y_TSK_LPS_KF_Y_DF_LPS_KF|STATEMENT <GUID::'-NtIqAGdgh6b7TFZoGYk0e' LOAD TABLE Y_DS_for_SAP_cloud_IBP.SAPSOPG100.SOPDD_STAGING_KFTAB_SAPIBP1SAPIBP1 INPUT(Target_Query)>
Column <ID> is a key column for target <SOPDD_STAGING_KFTAB_SAPIBP1SAPIBP1>, but does not match any column from the input
schema and the target's <use input key> option is not ON.

I guess the reason is because "ID" column in the staging table for key figure is a key.
But I couldn't understand why staging table has column "ID" as key by default and
wanna know how I deal with this error.

Best Regards,
Kosuke

[Alecsandra]

Hi Kosuke,

I guess you didn't map ID with gen_uuid() function. This is mandatory.

Best
Alecsandra

[Former Member]

Hi, Alecsandra

Thank you a lot!
I could resolve this issue.

Best Regards,
Tanaka

[former_member415274]

hi Alecsandra,
I'm having the following error :
|Session TASK
Syntax error at line <1>: <>: found <[end of text]> expecting <'(', &ERROR, __AL_LOOKUPEX_TRAN, __AL_SEARCH_REPLACE_TRAN,
__RFC_FUNCTION, __AL_SCRIPT_FUNCTION, __AL_STORED_PROCEDURE, __AL_EXTRACT_FROM_XML, __AL_EXTRACT_FROM_JSON, __AL_TRAN_FUNCTION,
+, AL_UNSPECIFIED_PARAM, CONVERT, a float, identifier, an integer, a null, a quoted identifier, ;, a string, a decimal,
VARCHAR, VARIABLE, +, NOT>.
1 error(s), 0 warning(s).
Check and fix the syntax and retry the operation.

SESSION TASK
Error parsing global variable values from the command line: <$G_LOAD_DATE= Sysdate();$G_PLAN_AREA= 'SAPMODEL1';$G_TIME_PROFILE=
-1;$G_BATCH_COMMAND= 'INSERT_UPDATE';$G_SCENARIO= ";>. Check the syntax and try again.

Could you help me please
Regards

[Alecsandra]

Hi Pablo,

I think the issue is caused by the default value you used for the $G_SCENARIO variable. Ensure they are straight quotes, to me they seem curly 🙂

Best

Alecsandra

[Former Member]

Great Article!  In fact I am in the middle of an Integration Proposal with IBP and a Non SAP ERP developed in .net . We will integrate data via SQL .  If you can give me feedback about this plan will be great!

 

thanks a lot

Pablo Vecchietti

 

[former_member267676]

Hi Alecsandra - This is excellent information!

We were trying to upload Key figure to IBP using HCI-DS and came across your blog. The upload is going through with csv file method but we are unable to proceed using HCI. The data is getting loaded to staging table but post processing returns 'zero records processed error'

I have raised a question in the below link, would appreciate if you could have a look and suggest any possible resolutions.

IBP - HCI DS - IBP key figure upload, post processing script error

[Alecsandra]

Hi Nair,

Errors during post-processing usually happen because of the data consistency (for example you are loading transactional data but the master data is missing for those combinations, your IBP granularity is monthly but you send the data in weekly values resulting into a key duplication etc). To analyze the error that appeared during post-processing you need to download the rejection report from IBP data integration fiori app.

 

best

Alecsandra

[Former Member]

Hi  Alecsandra,

Am new to this topic, was trying to integrate IBP and APO.

Need to send Sales Forecast from IBP to APO system using HCI-DS, was looking for step by step documents if any for the same.

Thanks,

Rizwan

 

[Alecsandra]

Hi Rizwan,

There is a template for the outbound integration from IBP to APO. Please check the online documentation for the IBP_KF_PlannedIndependentRequirements template.

https://help.sap.com/viewer/eab8fd1726934516a89eabced318b210/1708/en-US/e33eb855d3c66030e10000000a44...

Best,

Alecsandra

 

[Former Member]

Thanks for this link and for you help.

 

Thanks,

Rizwan

[former_member415274]

hi alecsandraghita ,

 

Why when i export data to csv file the values of some key figures doest match with the values in ibp excel add in  ?

 

Best Regards

[Former Member]

Hi Alecsandra,

We are sending  Confirm STR from ECC to IBP, (Transaction Data).

Below details from ECC table (EBAN) need to be posted to IBP KF table(SOPDD_STAGING_KFTAB_SCENARIOPLANAREA)

Material number,Location,Base Unit Of measure,Order Quantity,Requisition date,Supplying Plant.

Maintained all the necessary Global Variables as well.

This is the error-

List of attributes that exist in the row does not match the list of root level attributes for certain key figure(s) base level in batch &1 and file &2 caused by: Key figure attributes do not match root level.

Similarly for scenario Actual Sales(Transaction Data)- ECC TO IBP.

Conflict between the base levels for key figures in the row because the key figures presented in the row do not share the same base level in batch &1 and file &2 caused by: Attributes from different base levels

 

Request you to please suggest.

 

Thanks,

Rizwan

 

 

 

 

[Former Member]

 

Hi Alecsandra,

 

I have a requirement where in I should pass the unit sale price to IBP. In S4hana price is based on month(price condition-validity(this month to 6th month).But in IBP data should be divided into weekly records

So do we bring this single record to weekly records?

 

 

Thanks,

Pravi

[Alecsandra]

Yes, the values should match assuming the IBP selected attributes of the planning level in the planning view are the same with the extraction attributes + UoM / Currency consistency

[Alecsandra]

Hi Pravi

I assume the Price in IBP is defined on weekly granularity (week is the root of the planning level). Then you should be able to load the price using monthly buckets and mention that data is loaded on an aggregate level ($G_TIME_PROFILE_LEVEL). During post-processing, IBP will disaggregate the price from Monthly to Weekly using the disaggregation expression of the key figure  - given it is a price key figure, I assume the expression is Copy To.

Best

Alecsandra

 

[Alecsandra]

Hi Rizwan,

The first error indicates an issue with the target attributes mapping. The target attributes must match the planning level root attributes. Could you please check what are the root attributes of the planning level and ensure only these attributes are being mapped?

Regarding the second error, do you try to load multiple key figures with the same interface?

Best

Alecsandra

[former_member527376]

Hi Alecsandra,

 

The template for PIR in HCI-DS is for transferring data from IBP to SAP APO SNP.

I am using HCI-DS for data (Master/Transaction) exchange between IBP and HANA. In HCI-DS there is no standard template for transferring the Final Consensus Demand (IBP Planned supply) from IBP to HANA.

Can you please suggest a method in order to achieve the data transfer from IBP to HANA (IBP Supply to PIRs). Any related documentation will be extremely helpful.

 

Regrads,

A. Basu.

[Alecsandra]

Hi Basu,

 

You are right, as of now there is no out of the box interface. However, this can be achieved. You can check the standard S4 BAPI for PIR creation. If this satisfies your integration needs, then you can expose it as a web service and use it in the HCI-DS interface.

 

Best

Alecsandra

[former_member527376]

Hi Alecsandra,

 

Thanks for the response. Can you provide any documentation which will help us to achieve PIR creation in S4? We need the IBP planned consensus demand to be converted to PIR, whcih can serve an input for MRP live in S4...

 

Regards,

A.Basu.

[Former Member]

Hi Alecsandra,

Thanks for the details, was able to post message to IBP for below error, this happened as UOM filed was not needed/maintained in the KF for confirm str.

List of attributes that exist in the row does not match the list of root level attributes for certain key figure(s) base level in batch &1 and file &2 caused by: Key figure attributes do not match root level.

 

For second error- When deleted the KF and loaded the message again, this worked out, but no idea why even after maintain the Global Variable as Insert_update, even then we have this error, need to work on this interface, so that we dont face issues in the production.

Thanks,

Rizwan

[Alecsandra]

Hi Basu,

Please send me an email and I will share some screenshots with you.

Best

Alecsandra

[former_member527376]

Hi Alecsandra,

I have dropped a mail from my mail id.

Thanks!!!

Regards,

A. Basu

[Former Member]

Hi Alecsandra,

We need to integrate Data Virtualization(DV) which is an SQL language system, with HCI DS.

These are the option through which we can integrate DV with HCI -DS either ODBC or JDBC.

I was working on the same, am not able to find any option to create Data Stores for ODBC or JDBC.

Request you to please suggest.

Thanks,

Rizwan

 

 

[Former Member]

Hi Alecsandra,

After exploring came to know that HCI-Ds cannot talk to  DV system using ODBC or JDBC.

SAP to confirm, and only option left out is APIs (SOAP/REST).

Any documents on SOAP to IBP will be really helpful.

Thanks,

Rizwan

 

 

 

 

[suppadhyay]

Hi Alecsandra,

 

We are facing issues with discrepancy between attributes visible in core table and staging table in HCI. Is there a way to view staging table and core table and understand the reason of discrepancy using IBP Web UI or Excel UI and not in HCI?

 

Thanks,

Samiksha

[Former Member]

Hi,

 

On what cases we should use $G_BATCH_COMMAND=REPLACE?

 

tHANKS,

pRAVI

[Alecsandra]

Hi Pravi,

There are multiple use cases:

1. Can be used during implementation when you require to test and finetune the interface and would like to replace data loaded before with current data.


2. Might be required by business processes. For example, I had a customer which required to upload the current month data in replace mode (as there was no possibility to load the deltas only), and the history in insert_update mode.

Here is how REPLACE works:

https://help.sap.com/viewer/da797ae2bf6246d58abd417f24915d55/1802/en-US/ffe6a12683fa4eddbdba0208c2fe...

 

 

[Alecsandra]

Hi Samiksha,

Not sure that I understand the issue.

In the IBP core table you will only have the records that pass the consistency check in IBP.You can retrieve the data that gets rejected (not moved from IBP staging to IBP core table) from the IBP Data Integration fiori app - rejection file.

 

 

[former_member540046]

Hi Alecsandra,

This link is no longer valid, would you have the new one?

Full list and more details can be found it chapter 3 of Data Integration Guide:http://help.sap.com/download/ibp/ibp61_hci_integration.pdf

Regards,

Gilly

 

[Alecsandra]

Hi Gilly,

Sorry for the late reply, I updated it now. This is quite an old post, I was not expecting to be still actual.

Best

Alecsandra

[former_member540046]

Thanks Alecsandra! Much appreciate it!

[former_member592043]

Can you reply to this thread?

https://answers.sap.com/questions/643809/cannot-retrieve-children-nodes-browse-remote-error.html

[former_member592043]

Hi,

 

Can you explain the functionality of $G_TIME_PROFILE? Right now value has been set as -1.