How to trigger planning sequence as a batch job in Analysis Office

Now with more and more customers to adopt SAP BPC Optimized for S/4 HANA for finance (“BPC”) as the planning platform, one of the critical requirement is to trigger planning sequence as backend job from Analysis Office(“AO”), especially when the planning sequence takes long time to complete.

The attempt of this blog is to provide one solution for this requirement.

Before we dive deep into this topic, assumed we have one planning sequence (PS_1) which contains the real business logic we want to run in backend job, and it needs a parameter fiscal period from AO.

If we insert this planning sequence to AO workbook, and execute it from there directly, it will run in foreground mode, and Excel will be freezing before it is done. We must look for other solution for this requirement.

Step 1: Execute planning sequence in process chain

There are several ways to run planning sequence as backend job. You can manage it through ABAP code with function module – RSPLSSE_PLSEQ_EXECUTE, or just make it easier to use BW process chain. Here we’ll discuss the later one since there is another benefit with this option – to manage parallel running for the planning sequence.

Refer to SAP help document about this topic.

We know PS_1 needs parameter “fiscal period”, and it should be specified by end user in AO, so just let the field “Variable Variants” blank. If parallel process is needed, section 2 is for that purpose.

Step 2: Define planning function type to trigger process chain

Create a custom planning function type to trigger process chain (Z_TRIGGER_PC) in RSPC.

To make sure the planning function will be executed even there is no data in the region specified by the filter, “process Empty records” must be selected.

The other problem should be addressed in this function is to retrieve the parameter value from AO, and pass it to PS_1 (which is included in process chain).

In this program, we use a custom table to save the parameter value firstly, and then retrieve it with customer exit variable. The customer exit variable will be used in PS_1.

Step 3: Trigger process chain from AO

Process chain can’t be executed from AO directly, so we need to leverage another planning function as a trigger. At same time, we also need to pass parameter fiscal period from AO to backend.

We create another real-time InfoCube RCUBE02 which only include 0FISCPER and one key figure, and on top of it we create another Aggregation level AL01.

On Aggregation level AL01, we’ll create below two items:

1. One query Q_01 with input parameter for fiscal period, which will generate input in AO prompt dialog.
2. One planning function PF_TRIGGER with type Z_TRIGGER_PC (refer to below for more information about this custom type).

Insert query Q_01 and planning function PF_TRIGGER into one workbook, and now in the workbook prompt dialog, we can input/select fiscal period.

The only left problem is to pass the query variable value to planning function PF_TRIGGER.

There are several options for this problem:

1. Read query variable value through VBA code, and then assign it to planning function variable.
2. To retrieve query variable value in Excel cell with formula, and then specify that cell as source for planning function variable.

Once this problem is resolved, when user selects one period and execute planning function PF_TRIGGER from AO, the parameter will be passed to backend, and custom code in PF_TRIGGER will firstly write variable value to a z-table, and then trigger process chain. Once process chain is executed, the custom exit variable used in planning sequence will get value from z-table, and then it will be used in PS_1 (the real business logic).