Derivation in SAP Analytics Cloud Planning – Reading Attributes

[hartmut_koerner]

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.

Therefore, I have looked at advanced formulas for implementing derivation rules. In simple cases as described above, this can be achieved by reading a dimension attribute. For advanced cases where the target field depends on more than one source field, see my other blog post Derivation in SAP Analytics Cloud Planning – Advanced Scenario .

Example

I will use the derivation of the profit center from the cost center to demonstrate the idea. The cost center model may contain both the Cost Center and the Profit Center dimension. The cost center planner will start planning expenses. A data action then performs allocations between the cost centers. In the end, for reporting purposes, the profit center shall be derived from the cost center by reading the respective attribute and filling the profit center dimension in the fact data of the model.

Solution in detail

Step 1: Check cost center master data

In the public dimension SAP_ALL_COSTCENTER, we see that the profit center is filled in an attribute column.

Step 2: Create advanced formula

VARIABLEMEMBER #MEASURE1 OF [d/MEASURE]

VARIABLEMEMBER #MEASURE2 OF [d/MEASURE]



// Aggregate over profit center

DATA([d/MEASURE] = #MEASURE1, [d/t.S:SAP_ALL_PROFITCENTER] = "#") = RESULTLOOKUP([d/MEASURE] = "AMOUNT")

// Derive profit center from cost center attribute   

DATA([d/MEASURE] = #MEASURE2, [d/t.S:SAP_ALL_PROFITCENTER] = [d/t.S:SAP_ALL_COSTCENTER].[p/Profit_Center]) =

RESULTLOOKUP([d/MEASURE] = #MEASURE1, [d/t.S:SAP_ALL_PROFITCENTER] = "#")

// Check if derivation was executed successfully (attribute really is a profit center)

IF RESULTLOOKUP([d/MEASURE] = #MEASURE2, [d/t.S:SAP_ALL_PROFITCENTER] = [d/t.S:SAP_ALL_COSTCENTER].[p/Profit_Center]) =

	RESULTLOOKUP([d/MEASURE] = #MEASURE1, [d/t.S:SAP_ALL_PROFITCENTER] = "#") THEN

	DELETE([d/MEASURE] = "AMOUNT")

	DATA([d/MEASURE] = "AMOUNT") = RESULTLOOKUP([d/MEASURE] = #MEASURE2)

ENDIF

 

The central part here is the assignment [d/t.S:SAP_ALL_PROFITCENTER] = [d/t.S:SAP_ALL_COSTCENTER].[p/Profit_Center], However, this is not sufficient as it is important to cover all edge cases as well:

• We cannot assume that the profit center is initially posted to # (Unassigned). It is possible that another profit center is already filled, or there could be several records with different profit centers for one cost center. We should make as few assumptions as possible about the existing data.


• The derivation can fail because the profit center attribute contains a value that does not exist in the profit center dimension.

Therefore, the algorithm is designed as follows:

First, it aggregates our data over the target dimension profit center to #MEASURE1. This is to cover the first of the above situations.

In the next step, the derivation is done, stored in #MEASURE2 and not yet posted to the AMOUNT measure. This applies to the second situation mentioned above.

In the third step, the comparison checks whether the derivation has worked. If the profit center attribute does not contain an existing profit center, the data is not be copied to #MEASURE2. If the comparison is true, the old AMOUNT data for the cost center is deleted and replaced by the derived data.

Step 3: Apply advanced formula

The starting point is the cost center plan to which the profit center is not yet assigned (Unassigned):

After applying the advanced formula, I get the following result. When you compare with the cost center master data in step 1, you see that the correct profit center has been derived:

Now I will do a stress test of the advanced formula mentioned above to demonstrate that it also covers the edge cases. The starting point is now as follows:

For cost center Manufacturing 1, data was posted with two profit centers, and for cost center Purch & Store 1, I have maintained an invalid profit center in the attribute.

After applying the advanced formula, the result is now as follows:

Purch & Store 1 remains unchanged, the invalid derivation rule is not applied. The two rows of Manufacturing 1 are aggregated and posted to the correct profit center.

Conclusion

A simple, waterproof advanced formula can do the derivation from an attribute that also covers the edge cases. The above example describes a pattern for the derivation that can be applied in many other areas.

In the “Integrated Financial Planning for SAP S/4HANA” content package this technique is widely used, and you can find various coding examples here. Even a slightly more sophisticated scenario where the functional area is derived from the G/L account and cost center with a kind of priority rule.

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