Standard BPC allowed for linking the property of a dimension to another dimension member to ensure valid combinations are presented for planning purposes. This blog presents one of the ways to handle the same in SAP Analytics Cloud for Planning (SAC-P).
SAC Version: 2021.1.1
EPM library version: 1.00.202101.01.1605530525
Let us say there are two dimensions – Profit Center and Cost Center. Cost Center also has property Profit Center. The SAC story should display the combinations showing only Profit Centers that are associated to Cost Centers.
Here is the basic layout of the two dimensions
Here is a sample story with setting “Unbooked data” on.
As it can be seen that the story has generated output based on Cartesian product while the intent is to present only two combinations based on linking the property Profit Center of the Cost Center dimension with the ID of Profit Center dimension.
Now the linking will be performed via an script based data action. The script looks as follows:
MEMBERSET [d/Cost_Center_DNU] = (BASEMEMBER([d/Cost_Center_DNU].[h/CC_HIER], "ALL_CC")) MEMBERSET [d/Profit_Center_DNU] = (BASEMEMBER([d/Profit_Center_DNU].[h/PC_HIER], "ALL_PC")) MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/YQM], "2021")) DATA([d/Profit_Center_DNU] = [d/Cost_Center_DNU].[p/PC]) = 1*0
Do note that DATA() expression is evaluated as “1*0” instead of a constant value “0”. The reason being that system will not accept constant “0” and will ignore writing data back.
Now the data action is executed and zeroes will be populated against the linked combinations.
Now the Profit Center and Cost Center can be set to “Booked” data only and the output will be limited to linked combinations only.
There could be other ways of handling the same scenario. However, this approach helps limit the records resulting into a better performance. This could be something that can be executed at the beginning of the planning cycle.