BPC Planning for S/4HANA – FOX formulas – Part 1
You would like to use the BW formula planning functions for the BPC Embedded Model for Consolidations or for Planning and learn to write FOX (formula extension) code.
I started writing a blog on coding FOX formulas and happened to be working on a BPC Optimized for S/4HANA system. My intention was just to write a single blog but found it developing into a few parts due to the length and the fact that I wanted to build the system from scratch, including the entire data model.
So this is PART 1 of a 4-part blog with the following structure:
PART 1 – Setting up the data model, creating an Analysis for Office workbook, capturing and saving some seeding data to the data model. (No fox code yet)
PART 2 – Write a “Hello World” equivalent fox formula and execute it from a button placed in the Analysis for Office workbook.
PART 3 – Tools for debugging functions, variables, constants, loops, If-statements, messages.
PART 4 – Capturing data directly into the cube without using Analysis for Office, “DO” loops, “TMVL()” expression – time value offset calculations, “ATRV()” expression – using attribute values of a dimension member, a complete look at what it takes to calculate production quantities from sales demand and working across 2 cubes, periodic escalations of prices and quantities, overhead costs and cost of sales price calculations, sales price calculation based on a markup percentage, sales income calculation and cost of sales calculations.
*** NOTE ***: Also refer to Gerd Schoeffl’s blog posts related to BPC for further great content on coding and designs for BPC solutions.
The system I’m using is BPC Optimized for S/4HANA but you can use any version of BPC that can create an embedded model.
The powerful benefits of using S/4HANA for planning are NOT demonstrated in this blog as we’re focusing on FOX coding. We will not cover the S4 master data and transaction data integration or the S4 delivered planning content. However, if you look at my blog on setting up RTC you will get a good idea of how you can use S4 master data and S4 transaction data from the foundation views of S4.
What may be of interest to you from a BPC-S4 perspective is to visualise how embedded BW and embedded BPC is accessed and used within an S4 system. You might be wondering how different it is to developing BPC on a typical stand-alone instance of BW and this is evident from this blog. The short answer is that you can develop BPC as normal within S4. You can still access RSA1, create your own real-time planning cubes, manage the environments from the BPC Web Admin client and create Analysis for Office user interfaces. In a sense nothing changes other than logging onto the S4 system, seeing the accounting menu and executing BW transaction codes such as RSA1. However, it would be a total understatement to say “nothing changes” because when you choose to take advantage of the S4 integration, you have at your fingertips all the S4 master- and transaction data exposed to BW in virtual views without the need for extractors as well as all the pre-delivered BPC planning content.
So in this first part of coding FOX formula planning functions we are going to setup the planning model by doing the following:
- Create all the InfoObjects from scratch, two with an attribute
- Create two real-time planning cubes for revenue planning and cost planning
- Capture the master data
- Create a composite provider to join the two cubes
- Create an aggregation level
- Create a planning query as datasource for Analysis for Office
- Create an Analysis for Office workbook for capturing data and viewing our coding results
- Capture and save some initial seeding data so we can start our coding
I love FOX coding. It’s easy to use, it’s a powerful language, it can run directly in HANA using the HANA Planning Engine so the functions execute extremely quickly, it doesn’t require a developers key, it can be used on consolidation as well as planning models and most functional consultants and any technical BPC consultant who doesn’t know the language can be resourced onto a project and within a couple of weeks, under the lead of a senior consultant, will be productive on your project.
I have delivered very complex calculation models only using fox code. I have coded production planning, recursive functions, nominal and compound interest calculations for portions of a financial period, weighted average cost of capital, complex treasury calculations with forward cover and tax rate changes, shared services, disaggregations, cost allocations, minority interests, capital calcs etc. all in fox. To put it another way, the rare occasions that we have decided to move fox code to ABAP or another language has never been based on the complexity of the business rules but rather due to other considerations.
As a general rule you can use BPC planning functions to deliver almost all of your solutions logic but you will need ABAP for areas such as coding BW variables or start and end routines of data flows.
The aim of this blog is to give you an introduction to BPC Planning Functions and in particular, fox code. We will do the following:
- Write a formula function which requires fox coding. We will use all the basic features of the language for loops, variables, calculations, internal-tables etc.
- Link the planning functions to the user front end input template as a button they can press to execute the fox code.
Assume: When I refer to “BW Planning functions”, “BPC Planning Functions” or simply “Planning functions” it also applies to consolidation models and it is the functions available in BW under transaction code RSPLAN.
The FOX help reference guide
At the heart of FOX coding is the coding editor and it’s the one place in SAP where, ever since I started coding FOX around 2000/1 in SEM-BPS, the in-app online help documentation always works and with each new version of BPC I always open a blank coding screen, find the INFO button and call up the familiar reference material just like I’ve done below with the “all new” embedded BW on S/4HANA:
- DATA DECLARATIONS
- CONDITIONAL STATEMENTS
- LOOP CONTRUCTS
- ….and so on.
Everything you need to know about FOX coding is here in these help pages but it’s not the starting point. To get here we need to create a data model and aggregation level and then we can create the formula function.
You can also find this help in the SAP Help documentation here.
Overview of system components
We’ll setup a simple data model from scratch which has the following design:
- At the base of the design we’ll create some very simple characteristics and key figures, capture some master data and add an attribute to some characteristics so we can demonstrate how fox accesses attribute values in the code
- We will create two planning cubes, one for revenue planning and one for cost planning
- We will combine the two planning cubes into a composite multi-provider
- In order to create a planning function you must build it on an aggregation level which is a subset of the characteristics and key figures of the underlying InfoProvider. (In our case we have selected all InfoObjects for the aggregation level)
- The next level of construction is to prepare a query based on the aggregation level to serve as a data source for the Analysis for Office Workbook so we can capture and save input data as well as view output results
- Also built on the aggregation level will be our planning functions and filters used to compile the planning sequence(s) which is linked to the button(s) in our Analysis for Office workbook so the user can execute them
Setup the data model
|Using Eclipse with the BW Modeling perspective I recommend that you create a separate InfoArea to contain the build objects – but this is not mandatory. Next right-click the InfoArea and select NEW, INFO-OBJECT.|
|Create CHARACTERISTICS for COST CENTER, GL ACCOUNT, ITEM, PROFIT CENTER and VERSION with length 10, master data and texts.|
Use the technical names shown in the screen-shot.
Now create KEY FIGURES for AMOUNT, NUMBER and QUANTITY using 0CURRENCY and 0UNIT as the supporting characteristics for the unit-based key figures. These 3 key figures will demonstrate how currency, units and simple data types are handled in FOX code.
|Creating ZNUM gives us a general purpose number key figure which does not require a currency/unit selection.|
Now create a characteristic named ZTYPE which has NO master data or texts and is an ATTRIBUTE ONLY characteristic.
We will use this as an attribute on ZITEM and ZGLACC to classify the line items and demonstrate how FOX accesses and uses them in coding.
|Add ZTYPE to ZGLACC and ZITEM as an attribute.|
Logon to BW to create the planning cubes in RSA1.
For those of us who have worked on stand-alone instances of BW for a long time and now switching to S/4HANA this is going to take some getting used to…executing BW transaction codes directly from the ERP system where you can see the accounting SAP menu.
You can of course work on any version of BW i.e. BW on AnyDB, BW on HANA, BPC 11 etc. The only code element you may have a problem with in very old versions is the use of internal tables which is a fairly new addition to FOX coding and if your version of BW does not support composite providers then use a multi-provider instead.
|Create two InfoCubes in transaction code RSA1, MODELING. The first will be called ZREV01 for REVENUE PLANNING and the second ZCST01 for COST PLANNING. Make sure you select the check-box for REAL-TIME. Without this selection you will not be able to save data back to the cube.|
|Add all the characteristics we created to the ZREV01 cube EXCEPT for the Cost Center. Also add the standard time dimension characteristics which we did not need to create.|
|Add all the characteristics we created to the ZCST01 cube EXCEPT for the Profit Center. Also add the standard time dimension characteristics which we did not need to create.|
Now go back to Eclipse to create the Composite provider which I named ZPLAN01.
You should see your two InfoCubes in the InfoArea when you refresh the project browser.
Add your two InfoCubes as a UNION to the COMPOSITE PROVIDER and drag and drop all the characteristics and key figures to the target.
You’ll notice that all InfoObjects are shared between the two source cubes except the COST CENTER and PROFIT CENTER as we made these unique to each cube. So you will see single mapping lines from the source cubes to these two target objects.
|The final step in the data model construction is to create an aggregation level.|
Create an aggregation level based on the InfoProvider ZPLAN01 (the composite provider) with the technical name ZPAL01 and on the OUTPUT tab add all the InfoObjects from the composite provider.
You’ll notice that the system automatically adds a characteristic 0INFOPROV to the aggregation level so that we can distinguish which cube the data is being read- and written back to for shared characteristics.
|So…by the end of the data model setup you should see the InfoArea content as follows: Characteristics and Key Figures, the two real-time InfoCubes, the Composite Provider and an aggregation level.|
Add master data
|Manually capture some basic master data for each of the characteristics created|
|Once again in transaction RSA1, INFOPROVIDER, right-click, context menu item MAINTAIN MASTER DATA.|
For each characteristic select NEW for TEXTS and capture the ID and DESCRIPTION.
When adding attributes for ITEM and GL ACCOUNT select TIME INDEPENDENT master data.
|GL Account attributes|
Create a BPC Planning Model
Logon to the BPC Web Admin Client and create a new ENVIRONMENT for EMBEDDED models and then create an EMBEDDED PLANNING MODEL based on the COMPOSITE provider.
Setup the user interface & capture some transaction data
The resulting Analysis for Office (AO) excel workbook for capturing data will look as follows:
The datasource is a query set to open in INPUT-READY mode with the Columns set to:
- Key figures – ZNUM, ZQTY and ZAMT. All set to INPUT-READY.
- Periods – fixed values 1 to 12
The rows are set to:
- Profit Center
- Cost Center
- GL Account
- Unit of Measure
The header fixed values are set to:
- Fiscal year variant
- Fiscal year
Here are a few pointers to get the workbook INPUT-READY if it’s a bit new to you:
|The datasource for the input template in Analysis Office is a query built on the aggregation level.|
|Flag the query to open in INPUT-READY mode under the PLANNING tab so you don’t have to manually switch it when you add it to the workbook.|
|Add the 3 Key Figures to the columns and make them INPUT READY by selecting each key figure in turn and on the PLANNING tab changing the INPUT-READY field to INPUT-READY.|
ALL CHARACTERISTICS must be added to either the columns, rows or header (free) sections of the query. Omitting a single characteristic will prevent it from opening in INPUT-READY mode as the query must be at the lowest level of granularity in order to know where to save data.
The characteristics in the HEADER area under FREE characteristics must have a FIXED FILTER set for the same reason. These will not be visible in the worksheet and the user will not be able to capture them so they must have fixed values so that when we save the record it has values to save against for those characteristics.
After adding the query to the workbook display the design panel and on the COMPONENTS tab at the bottom of the screen select the PLANNING tab in the middle and link the query to the BPC Planning Model.
On the same tab selecting the datasource’s CROSSTAB will allow you to select the number of new blank lines to capture. The default is 5 and hence the 5 new rows displayed for capture in the worksheet.
Selecting the datasource [DS_1] also allows you on this tab to set the datasource to INPUT READY on opening.
Capture and save data
So now for the moment of truth…can you capture and save data? Any errors in the setup and this will not work.
Capture a value of 100, 80 and 150 in the Quantity column for period 1 for the 3 finished products PRD01, PRD02 and PRD03. Save.
If the SAVE is successful the workbook will display all the updated sub-totals, the SAVE button will return to an inactive state and if you select the MESSAGES button you can view the last system return message which reads “DATA WAS SAVED”. Also notice that the captured lines are open for editing and 5 new rows have been added to the bottom of the screen for new characteristic combinations to be captured.
Well written with great screen shots, making it seem that all of us can write Fox Formula's.
Helpful blog, thank you!
The input ready query, mentioned in the blog has fiscper3(posting period) in the columns of the bw query against the key figures such as zamt, zqty etc and in the filter 0fiscper3 is restricted from 1 - 12. Since 0fiscper3 is in columns and it has multiple values from 1- 12(restricted at filter level), it doesnt become input enabled. I tried putting it in the rows and then it got input enabled and the access type for all the characteristics i have taken is "Master Data". Can you Please let me know if you have created the input ready query in a different way, thank you.
Let me understand the issue: if you put 0fiscper3 in the rows it works but if you move it to the columns it does not and all characteristics are marked “master data”? Is this the issue?
The basic principle that needs to be followed is that all characteristics must be at the lowest level of granularity for input. If even 1 is at a node level input is not possible to enter data.
In addition if all characteristics are at the lowest level and they are flagged to use info-provider data the input will be blank for a new cube with no data...so right to flag characteristics to use master data.
Then next check is that query is set to receive new rows. The default is 5 new rows for a new query but check this is on.
Thank you for the response!
I have maintained the granular/detailed level for each characteristic of the input query and am able to input the values.
Since the query shown below from the this blog has the fiscper3 placed in the columns, so i was wondering if you designed it differently because placing fiscper3 (while has multiple values) in the columns doesnt qualify for input ready.
Yes 0fiscper3 will qualify for input ready even if it is in the columns as it is restricted in the "filter" tab of the query as multiple fixed values. From here it would then not matter if it was in rows or columns because it is hard-coded 1-12 and in most cases we would put this time dimension across the columns.
I hope this answers your question.
Thank you for the clarification !
Image of input ready query in the blog displays posting period:
Image of bw query and template below. Doesnt seggregate on periods 1 - 12 as seen below.
BW Query created:
In the FOX editor, there is a button called "Data Types", If we click on the data types button, it will show the list of data types could be used for declaring internal tables or variables in the FOX coding.
Could you please let us know how these data types are maintained here.