SAP BPC has been used for various planning application purposes- Demand Planning, Supply Planning, Sales Planning, Marketing Planning along with various financial planning processes like Cash  Flow Planning, Budgeting etc.

We all know that BPC Model/Application/Cube supports Account Modelling. When a default report is created, you get all the possible intersections of master data available for planning. More often than not, most of these combinations are undesirable for planning and there are always ways identified to display the right plannable combinations. I am going to talk about a quick approach to achieve the same via an hypothetical scenario.

Company XYZ Ltd deploys SAP APO for Demand Planning and to release the demand to supply department. However, they cannot have their on field sales representatives access the SAP APO system to enter forecast. Most of them do not have SAP GUI installed and the only way they can access and input plan numbers is via Excel files. This is where BPC comes into picture.

SAP APO sends over the following records to enable Sales representatives to enter the forecast:

Product Customer Region Calendar Month Historical Sales Regional Manager Forecast Sales Rep Forecast Average Selling Price
P1 C1 R1 201309 100EA 120EA 1.2USD
P1 C2 R1 201309 110KG 85KG 20SGD
P2 C3 R2 201309 1000LTR 1200LTR 5INR

This data is converted to account model after being extracted from APO in staging DSO/Cube and will appear as follows:

Product(ZPRDCT) Customer(ZCUST) Region(ZREGION) Month(ZMONTH) Keyfigure(ZBPC_KF) ZUNIT ZSIGN_DATA
P1 C1 R1 2013.09 HISTORICAL_SALES EA 100
P1 C1 R1 2013.09 REG_MANAGER_FORECAST EA 120
P1 C1 R1 2013.09 SALES_REP_FORECAST EA 0
P1 C1 R1 2013.09 AVERAGE_SELLING_PRICE USD 1.2
P1 C2 R1 2013.09 HISTORICAL_SALES KG 110
P1 C2 R1 2013.09 REG_MANAGER_FORECAST KG 85
P1 C2 R1 2013.09 SALES_REP_FORECAST KG 0
P1 C2 R1 2013.09 AVERAGE_SELLING_PRICE SGD 20
P2 C3 R2 2013.09 HISTORICAL_SALES LTR 1000
P2 C3 R2 2013.09 REG_MANAGER_FORECAST LTR 1200
P2 C3 R2 2013.09 SALES_REP_FORECAST LTR 0
P2 C3 R2 2013.09 AVERAGE_SELLING_PRICE INR 5

We will assume that we have a simple Planning model which will comprise of the following dimensions:

Model Dimensions
DATASRC
PRODUCT
CUSTOMER
REGION
KEYFIGURE
TIME
UNIT

The mapping will be 1:1. The transformation file will be as follows:

DATASRC=*STR(APO_UPLOAD)

PRODUCT=ZPRDCT

CUSTOMER=ZCUST

REGION=ZREGION

KEYFIGURE=ZBPC_KF

TIME=ZMONTH

UNIT=ZUNIT

SIGNEDDATA=ZSIGN_DATA

When you validate and process the transformation file, the number of records being processed will be shown as 12 which is expected. It is time to have a DM package to be created to load data from DSO into the model. Once DM Package is executed successfully, the log will show the submit count as 12.

Now when you create an input schedule, you will get all sorts of combinations based on the members of the dimension:

Blog.jpg

This is quite an undesirable output as the planning combinations are already coming over from the planning systems and you do not need to have any additional planning combinations. You therefore could use following under “Options” to take care of those:

Blog_sheet.jpg

Also notice that the records against SALES_REP_FORECAST show blank and not zero. This means that BPC does not load zero records by default. As a result, on selecting the above option- you will have the following output:

Blog1.jpg

The output is undesirable in either of the case. It really bothered me and I started looking into various options and settings and got no clue until I found the following setting in SPRO in the underlying BW system and click on “Execute” icon:

SPRO.jpg

Enter the name of the environment in the next screen:

ENV.jpg

Click on the “Create” icon in the next screen as shown:

Param.jpg

In the next screen enter the parameter as follows:

/wp-content/uploads/2014/03/zero_409288.jpg

Click “OK” and save it. Relogin into EPM addin and refresh the data. The output would now show as follows:

/wp-content/uploads/2014/03/results_409289.jpg

Keep in mind that- these settings are at the Environment level and will be applicable to all the models in that environment. You can reset this setting by changing the value to “0” from “1”.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Vadim Kalinin

    Hi Gajendra,

    It’s a well known option to allow writing zeros, but in general BPC assumes that zero and empty records are equal. By enabling this setting you will loose the ability to optimize cube with zero elimination. And this option is useful in many scenarios (clearing incorrect calculations, clearing some members before deletion etc…)

    B.R. Vadim

    (0) 

Leave a Reply