Technical Articles
Derivation in SAP Analytics Cloud Planning – Advanced Scenario
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:
How would you apply this to data for a none fixed date range? E.g for a financial year that updates dynamically each year
Hi Edward,
you mean the fixed date 202101? The derivation rule in my example is time independent. I used this value only because SAC always requires a date dimension.
For time dependent derivation rules, you would maintain the valid combinations by period in the first model and in the advance formula you simply would omit the [d/Date] = "202101" filter. Then, the profit center in the profitability data will be derived by period.
Best regards
Hartmut
Hi Hartmut,
Thank you for the quick reply.
So in the validation model I would just add a date (Say "2022") for when the combo is valid when I add a new combination?
Best regards,
Ed
Exactly, you need to do that for each date where you have plan data in the other model
Also, how would I extend this to derive multiple dimensions instead of just profit centre?
If the source fields are the same, you simply could extend the first model with the additional target fields. In the data action, simply handle the other dimensions like the profit center.
Hi Hartmut,
So for the other dimensions would I add a new line per dissagregation like so:
DATA([d/t.S:SAP_ALL_PROFITCENTER] = #PROFCTRTOTAL) = RESULTLOOKUP()
DATA([d/t.S:SECOND_DIMENSION] = #SECONDDIMTOTAL) = RESULTLOOKUP()
DATA([d/t.S:THIRD_DIMENSION] = #THIRDDIMTOTAL) = RESULTLOOKUP()
then aggregating over the dimensions
DATA([d/MEASURE] = #DRIVERTOTAL, [d/t.S:SAP_ALL_PROFITCENTER] = "#", [d/t.S:SECOND_DIMENSION] = #, [d/t.S:THIRD_DIMENSION] = #) = RESULTLOOKUP([d/MEASURE] = #DRIVER)
Then dissagregating again
DATA() = RESULTLOOKUP([d/t.S:SAP_ALL_PROFITCENTER] = #PROFCTRTOTAL) *(Driver/drivertotal)
DATA() = RESULTLOOKUP([d/t.S:SECOND_DIMENSION] = #SECONDDIMTOTAL) *(Driver/drivertotal)
DATA() = RESULTLOOKUP([d/t.S:THIRD_DIMENSION] = #THIRDDIMTOTAL) *(Driver/drivertotal)
Or would it be done a different way? Sorry for all the questions. I am new to this and even going through all available courses and the learning hub there is not much information on advanced formulas and how to use them.
Kind regards,
Ed
Hi Ed,
not sure if that works, you might try out. I would have done the first part like
// Aggregate over profit center and second dimensionDATA([d/t.S:SAP_ALL_PROFITCENTER] = #PROFCTRTOTAL, [d/t.S:SECOND_DIMENSION] = #SECONDDIMTOTAL ) = RESULTLOOKUP()
and the third like
DATA() = RESULTLOOKUP([d/t.S:SAP_ALL_PROFITCENTER] = #PROFCTRTOTAL, [d/t.S:SECOND_DIMENSION] = #SECONDDIMTOTAL )) *(Driver/drivertotal)
Best regards
Hartmut
Hi Hartmut,
Thank you for the help. Unfortunately I am still having issues.
I have the code all translated for my models, but when I try and validate it says that my variable members are not defined.
I tried using the VARIABLEMEMBER function to define them at the start and then it says they are not valid.
Is there any extra steps you need to do that are not shown here to define the variables?
Kind regards,
Ed
Hi Ed,
thanks for pointing to this. In the code snippet above I missed the variablemember definition. I have updated it now.
Best regards
Hartmut
Thanks Hartmut,
I worked out I was doing it incorrectly before this, but this clarifies that I now do it correctly.
Hi Hartmut,
Unfortunately this hasn't worked for me. I am sure I am doing something incorrectly but I'm not sure what it is.
When I enter the values for the plan and then run the data action, the values are still assigned the the unassigned dimensions that were meant to be derived. There are no values assigned to the dimensions that were meant to be derived either so it looks like it isn't doing anything with the data.
Here is my code:
I am deriving the "Company Code", "Plant", "GL account", and "Energy subcategory" from a combination of "Price ID" and "Cost Centre"
I also specify [d/ENERGY_SUBCATEGORY] = "Main" in the link as I am just doing it for those combinations for now just to test. I will make this a public dimension so I do not need to specify this in the future.
I really appreciate the help.
Kind regards,
Ed
Hi Ed,
I see two potential problems:
Here you should filter all dimensions that are not part of the linked model, i.e. that are neither source nor target dimensions, with #. The system needs to know where to put these data in the model.
Here you read the #DRIVER differently than you have written it. You read it e.g. with plant = # .
Hope this helps.
Best regards
Hartmut
Hi Ed,
there might be an additional problem in your tenant: Due to an SAC bug you should restrict the measures, e.g. via.
MEMBERSET [d/MEASURE] = ("AMOUNT", "QUANTITY")
It could be all measures, but it needs to be restricted to real measures. Otherwise the system uses also the virtual measures #DRIVER etc. in a wrong way.
The bug is fixed in version 2021.09
Best regards
Hartmut
Hi Hartmut,
Again, thank you for all your help. However, it is still not working for me. I have made the changes you suggested (as well as some others I noticed).
In the target model there are only either the combination dimensions, or the ones to be derived, no others. That is why I have:
Is it an issue to only have combination and derived and no others?
I also fixed the other issue you suggested, here is my complete code:
I have changed the "Energy Subcategory" to be a public dimension so I don't need to restrict it in the link and added the member set restrictions as suggested. I am really not sure what else to do?
The process I go through in the story is to enter my data for the plan and then run the data action. I have also tried having a new published version and running the action on that with no manual entry and still no luck.
I appreciate any help you can give.
Kind regards,
Ed
Hi Ed,
from my perspective the formula looks good now.
Reason now could be the data in the both models that are not posted as expected. For troubleshooting you could step by step replace the variablemembers in your formula with real members and view the intermediate results in a story.
Best regards
Hartmut
Hi Hartmut,
I have found the issue. This sap note: https://apps.support.sap.com/sap/support/knowledge/en/2821141
Details how delete wont work with members not in hierarchies (or as I have found, incomplete/blank hierarchies).
So I just had to find those hierarchies and either fill them in with <root> or delete them. Now it works perfectly.
Thank you for all your help and really quick responses 🙂
Kind regards,
Ed
Hi Ed,
you also can use
CONFIG.HIERARCHY.INCLUDE_MEMBERS_NOT_IN_HIERARCHY = [d/SAP_ALL_COMPANY_CODE], [d/SAP_FI_BPL_GLACCOUNT], ...
at the beginning of the formula to force it to work also with the members that are not in the hierarchy.
The interesting thing is that the advanced formula works always works with a hierarchy if the dimension has one. Even if you don't specifiy it or use it in the formula. If the dimension has several hierarchies, it uses the first hierarchy as default hierarchy.
Best regards
Hartmut
Hi Hartmut,
Everything is working great. I would just like to know how I would maintain this data when I want to remove a valid combination?
Kind regards,
Ed
Hi Ed,
you want to delete the data in case there is no corresponding entry in Energy_Valid_Combinations?
I think, this can be done by putting the DELETE() outside the IF.
Best regards
Hartmut
Hi Martmut,
No, I wanted to delete a valid combination (I am copying over last years combinations to this year for ease but some of them changed so I need to remove them).
I just added a new measure that is a deletion flag and users can enter 1 in there and run a data action and itll get rid of them. Not sure if it is the best way but it works.
Kind regards,
Ed
Hi Ed,
what about Deleting values in a table ?
Best regards
Hartmut
Hi Hartmut,
We are working on a similar scenario on my project. We are wanting to assign a GL account in our profitability model, to data without a GL account. We used the same idea as this blog, as to create another model to assign the 3 dimensions.
At a high level, largely the Region will not have a Cust Plan Grp (i.e. will = #).
But in some cases the client need to go to a split via GL account for a specific Cust Plan Grp.
So we needed a couple of IF statements. Not one, as in the blog.
After running the Data Action, and switching the GL Hierarchy view to “Flat Presentation” – I was able to see this erroneous record in the planning template (second screenshot), was displaying 2 records, one normal and one by #GLACCALL.
This #GLACCALL is the Variable Member in the Data Action (Fourth screenshot in my question) and if I run the Data Action again, I get a doubling issue.
Before:
After running data action
After running data action twice
So the way to get around this issue, we found is using a physical Driver “Measure” – not a Variable Member as in the blog.
Data Action
Instead of #DRIVER.
It looks like we cannot necessarily multiply 2 Variable Members together (one being of type dimension, one being of type measure). Is this correct?
Also in this blog: https://blogs.sap.com/2021/05/03/advanced-formulas-how-they-work/
It seems like #GLACCALL is a record that can present itself in the data, is this correct?
Thank you.
Raj
Hi Raj,
I wonder why you see #GLACCALL in the table. If #GLACCALL is defined as
VARIABLEMEMBER #GLACCALL OF [d/SAP_FI_BPL_GLACCOUNT]
you will not see it in the table. VARIABLEMEMBERS get lost when the data action is finished.
Best regards
Hartmut
Hi Hartmut,
That is what is the mystery for me.
I have not persisted this data, it is solely used as a variable member. We believe this to be a bug.
Where you cannot see the #GLACCALL variable member in a "hierarchy" or "leaf nodes" view - but you are able to view this variable member in the table/data when you select the "flat presentation" view.
This is the message we get when attempting to Publish the data, this implies that the data is being persisted:
Thank you
Raj