Derivation in SAP Analytics Cloud Planning – Advan...

hartmut_koerner · ‎2021 Mar 23

Summary

The derivation of additional dimensions is a common requirement in planning applications. For example, you want to derive the company code from a plant, the profit center from a cost center, the functional area from the G/L account and so on.

The built-in “Validation rules” feature in SAP Analytics Cloud can be used to implement derivations, but has some limitations: The validation rules are only applied during manual data entry and not after running data actions. There is no easy way to import the rules from an external system, e.g. from SAP S/4HANA.

Therefore, I have looked at advanced formulas for implementing derivation rules. In another blog post, Derivation in SAP Analytics Cloud Planning – Reading Attributes, I have described how this can be achieved by reading a dimension attribute. In this blog post, I now focus on more advanced cases where you have more than one source field and you want to import the derivation rules from an SAP S/4HANA system. The example I use here is the derivation of the profit center from the combination of plant / product.

Basic solution idea

The basic idea is to load and keep the valid member combinations (here: plant / product / profit center) in a separate model. The advanced formula that applies the derivation rule runs on the planning model itself (here: profitability planning) and accesses the other model via the LINK function. The derivation can be seen as a special case of a top-down distribution where there is only one receiver. Thus, in this example, I disaggregate plan data at plant / product level down to profit center level. The advanced formula is generic and can also be used for top-down distribution. The question now might be why I use an advanced formula and not the SAP Analytics Cloud allocation to manage this task. The reason for this is that with SAP Analytics Cloud allocations, the sender (here: the combination plant / product / profit center = unassigned) remains with the value of 0 and is not deleted. This disrupts us in the derivation case.

Solution in detail

Step 1: Create a separate model for the valid combinations

The model simply contains the three dimensions plant, product, and profit center. In the account dimension, I create a single measure that I call “Driver”.

Step 2: Load combinations from SAP S/4HANA

In the data management area of the newly created model, I create a new import using the SAP S/4HANA connection. I choose the OData service “Remote API for product master” and, within this service, the entity A_ProductPlant. The three columns for plant, material (=product), and profit center are mapped to the respective dimensions of the model. The trick is now to create a calculated column with a fixed value “1” and map it to the driver measure:

In addition, I set a fixed value for the time dimension (here: 202101) and load it directly into the Actuals version:

Step 3: Create advanced formula for derivation

On the profitabiltiy model I now define an advanced formula that derives the profit center for the plant / product combinations. The profitability model has a Measure dimension with measures like Amount and Quantity, plant, product, profit center and other dimension like company code, customer etc.

MEMBERSET [d/MEASURE] = ("AMOUNT", "QUANTITY")

VARIABLEMEMBER #PROFCTRALL OF [d/t.S:SAP_ALL_PROFITCENTER]

VARIABLEMEMBER #DRIVER OF [d/MEASURE]



// // Aggregate over profit Center

DATA([d/t.S:SAP_ALL_PROFITCENTER] = #PROFCTRALL) = RESULTLOOKUP()



// Copy driver from other model

DATA([d/MEASURE] = #DRIVER, [d/Date] = "202101", [d/t.S:SAP_ALL_CUSTOMER] = "#", [d/SAP_ALL_COMPANY_CODE] = "#", [d/SAP_FI_BPL_GLACCOUNT] = "#",

	 [d/t.S:SAP_ALL_FUNCTIONALAREA] = "#", [d/SAP_FI_BPL_FIXVARIABLE] = "#", [d/SAP_FI_BPL_QUANTITY_UNIT] = "#") =

LINK([KOE__PLANTPRODUCT], [d/Version] = "public.Actual", [d/Date] = "202101", [d/MEASURE] = "DRIVER")

// Aggregate driver over profit center

DATA([d/MEASURE] = #DRIVER, [d/t.S:SAP_ALL_PROFITCENTER] = #PROFCTRALL) = RESULTLOOKUP([d/MEASURE] = #DRIVER)



// Make sure that data are not deleted in case no derivation is maintained

IF RESULTLOOKUP([d/MEASURE] = #DRIVER, [d/Date] = "202101", [d/t.S:SAP_ALL_CUSTOMER] = "#", [d/SAP_ALL_COMPANY_CODE] = "#", [d/SAP_FI_BPL_GLACCOUNT] = "#",

				[d/t.S:SAP_ALL_FUNCTIONALAREA] = "#", [d/SAP_FI_BPL_FIXVARIABLE] = "#", [d/SAP_FI_BPL_QUANTITY_UNIT] = "#", [d/t.S:SAP_ALL_PROFITCENTER] = #PROFCTRALL) != 0 THEN

	// Delete old data for all combination plant/product where the condition above is fulfilled

	DELETE()

	// Disaggregate to Profit Center

	DATA() = RESULTLOOKUP([d/t.S:SAP_ALL_PROFITCENTER] = #PROFCTRALL) *

	RESULTLOOKUP([d/MEASURE] = #DRIVER, [d/Date] = "202101", [d/t.S:SAP_ALL_CUSTOMER] = "#", [d/SAP_ALL_COMPANY_CODE] = "#", [d/SAP_FI_BPL_GLACCOUNT] = "#",

				 [d/t.S:SAP_ALL_FUNCTIONALAREA] = "#", [d/SAP_FI_BPL_FIXVARIABLE] = "#", [d/SAP_FI_BPL_QUANTITY_UNIT] = "#") /

	RESULTLOOKUP([d/MEASURE] = #DRIVER, [d/Date] = "202101", [d/t.S:SAP_ALL_CUSTOMER] = "#", [d/SAP_ALL_COMPANY_CODE] = "#", [d/SAP_FI_BPL_GLACCOUNT] = "#",

				 [d/t.S:SAP_ALL_FUNCTIONALAREA] = "#", [d/SAP_FI_BPL_FIXVARIABLE] = "#", [d/SAP_FI_BPL_QUANTITY_UNIT] = "#", [d/t.S:SAP_ALL_PROFITCENTER] = #PROFCTRALL)

ENDIF

This formula implements a top-down distribution, and as discussed above, the derivation can be seen as a special case of top-down distribution with only one receiver.

With the MEMBERSET definition at the beginning we restrict the derivation to the relevant measures.

First, the formula aggregates our data over the target dimension profit center to #AMOUNTTOTAL. This is necessary because you cannot be sure that the data was initially posted with profit center = Unassigned. They might be assigned to any profit center.

As a next step, it copies over the data from the other model that contains the valid combinations. All other dimensions of the profitability planning model need to be set explicitly to # (Unassigned). If you forgot this, the data would be duplicated to all dimension values. And it calculates the total driver value #DRIVERTOTAL per combination plant / product.

In the third section the formula performs the disaggregation, in short by calculating AMOUNT = #AMOUNTOTAL * #DRIVER / #DRIVERTOTAL. This formula is evaluated for each profit center and for each combination of the other dimensions in the profitability model. Therefore, it is crucial to access #AMOUNTOTAL and #DRIVERTOTAL with profit center = #.

Step 4: Apply advanced formula

Starting point is the profitability plan where the profit center still is Unassigned:

After applying the advanced formula, I get the following result, and when you compare with the combinations in step 2, you find that the correct profit center was derived.

Now I manually add another entry for product FERT 233 so that you can better understand how this is related to top-down distribution:

If I now run the same advanced formula one gets the following result for product FERT 233. All values are split according to the ratio 1:2 to the two profit centers:

Conclusion

With a separate model for storing member combinations and a small advanced formula, it is possible to implement derivation rules where the target dimensions depend on more than one source dimension. The example mentioned above describes a pattern that can be applied in many other cases. For example, this pattern can be used to extend sales and profitability planning within the “Integrated Financial Planning for SAP S/4HANA” content package with an account determination rule when importing external sales quantity data. An account determination is nothing else than a derivation rule for the G/L account from dimension combinations like customer / product.

For additional information please look into the following places:

Integrated Financial Planning for SAP S/4HANA with SAP Analytics Cloud

Visit your community topic page to learn more about SAP Analytics Cloud

Find all Q&A about SAP Analytics Cloud and feel free to ask your own question