Skip to Content
Technical Articles
Author's profile photo Hartmut Koerner

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:

Assigned Tags

      22 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Edward Hardwick
      Edward Hardwick

      How would you apply this to data for a none fixed date range? E.g for a financial year that updates dynamically each year

      Author's profile photo Hartmut Koerner
      Hartmut Koerner
      Blog Post Author

      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

      Author's profile photo Edward Hardwick
      Edward Hardwick

      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

      Author's profile photo Hartmut Koerner
      Hartmut Koerner
      Blog Post Author

      Exactly, you need to do that for each date where you have plan data in the other model

      Author's profile photo Edward Hardwick
      Edward Hardwick

      Also, how would I extend this to derive multiple dimensions instead of just profit centre?

      Author's profile photo Hartmut Koerner
      Hartmut Koerner
      Blog Post Author

      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.

      Author's profile photo Edward Hardwick
      Edward Hardwick

      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

       

      Author's profile photo Hartmut Koerner
      Hartmut Koerner
      Blog Post Author

      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

      Author's profile photo Edward Hardwick
      Edward Hardwick

      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

      Author's profile photo Hartmut Koerner
      Hartmut Koerner
      Blog Post Author

      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

      Author's profile photo Edward Hardwick
      Edward Hardwick

      Thanks Hartmut,

       

      I worked out I was doing it incorrectly before this, but this clarifies that I now do it correctly.

      Author's profile photo Edward Hardwick
      Edward Hardwick

      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:

      VARIABLEMEMBER #COMPANYCDTOTAL OF [d/SAP_ALL_COMPANY_CODE] 
      VARIABLEMEMBER #PLANTTOTAL OF [d/t.S:SAP_ALL_PLANT] 
      VARIABLEMEMBER #GLTOTAL OF [d/SAP_FI_BPL_GLACCOUNT] 
      VARIABLEMEMBER #SUBCATTOAL OF [d/ENERGY_SUBCATEGORY]  
      VARIABLEMEMBER #DRIVER OF [d/Measure] 
      VARIABLEMEMBER #DRIVERTOTAL OF [d/Measure]
      
      // Aggregate over profit Center
      DATA([d/SAP_ALL_COMPANY_CODE] = #COMPANYCDTOTAL, [d/t.S:SAP_ALL_PLANT] = #PLANTTOTAL, [d/SAP_FI_BPL_GLACCOUNT] = #GLTOTAL, [d/ENERGY_SUBCATEGORY] = #SUBCATTOAL) = RESULTLOOKUP()
      // Copy driver from other model
      DATA([d/Measure] = #DRIVER, [d/Date] = "202104")
      = LINK([Energy_Valid_Combinations], [d/Version] = "public.Actual", [d/Date] = "202104", [d/Account] = "DRIVER", [d/ENERGY_SUBCATEGORY] = "Main") 
      // Aggregate driver over profit center
      DATA([d/Measure] = #DRIVERTOTAL, [d/SAP_FI_BPL_GLACCOUNT] = "#", [d/t.S:SAP_ALL_PLANT] = "#", [d/SAP_ALL_COMPANY_CODE] = "#", [d/ENERGY_SUBCATEGORY] = "#") = RESULTLOOKUP([d/Measure] = #DRIVER)
      // Make sure that data are not deleted in case no derivation is maintained
      IF RESULTLOOKUP([d/Measure] = #DRIVERTOTAL, [d/Date] = "202104", [d/SAP_ALL_COMPANY_CODE] = "#", [d/SAP_FI_BPL_GLACCOUNT] = "#", [d/t.S:SAP_ALL_PLANT] = "#", [d/ENERGY_SUBCATEGORY] = "#") != 0 THEN
      	// Delete old data for all combination plant/product where the condition above is fulfilled
      	DELETE()
      	// Disaggregate to Profit Center
      	DATA() = RESULTLOOKUP([d/SAP_ALL_COMPANY_CODE] = #COMPANYCDTOTAL, [d/t.S:SAP_ALL_PLANT] = #PLANTTOTAL, [d/SAP_FI_BPL_GLACCOUNT] = #GLTOTAL, [d/ENERGY_SUBCATEGORY] = #SUBCATTOAL) *
      	RESULTLOOKUP([d/Measure] = #DRIVER, [d/Date] = "202104", [d/SAP_ALL_COMPANY_CODE] = "#", [d/SAP_FI_BPL_GLACCOUNT] = "#", [d/t.S:SAP_ALL_PLANT] = "#", [d/ENERGY_SUBCATEGORY] = "#") /
      	RESULTLOOKUP([d/Measure] = #DRIVERTOTAL, [d/Date] = "202104", [d/SAP_ALL_COMPANY_CODE] = "#", [d/SAP_FI_BPL_GLACCOUNT] = "#", [d/t.S:SAP_ALL_PLANT] = "#", [d/ENERGY_SUBCATEGORY] = "#")
      ENDIF

       

      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

       

      Author's profile photo Hartmut Koerner
      Hartmut Koerner
      Blog Post Author

      Hi Ed,

      I see two potential problems:

      // Copy driver from other model
      DATA([d/Measure] = #DRIVER, [d/Date] = "202104")

      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.

      RESULTLOOKUP([d/Measure] = #DRIVER, [d/Date] = "202104", [d/SAP_ALL_COMPANY_CODE] = "#", [d/SAP_FI_BPL_GLACCOUNT] = "#", [d/t.S:SAP_ALL_PLANT] = "#", [d/ENERGY_SUBCATEGORY] = "#") /
      	

      Here you read the #DRIVER differently than you have written it. You read it e.g. with plant = # .

      Hope this helps.

      Best regards
      Hartmut

       

       

       

      Author's profile photo Hartmut Koerner
      Hartmut Koerner
      Blog Post Author

      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

      Author's profile photo Edward Hardwick
      Edward Hardwick

      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:

      DATA([d/Measure] = #DRIVER, [d/Date] = "202104")

      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:

      MEMBERSET[d/Measure] = ("PRICE","USAGE")
      VARIABLEMEMBER #COMPANYCDTOTAL OF [d/SAP_ALL_COMPANY_CODE] 
      VARIABLEMEMBER #PLANTTOTAL OF [d/t.S:SAP_ALL_PLANT] 
      VARIABLEMEMBER #GLTOTAL OF [d/SAP_FI_BPL_GLACCOUNT] 
      VARIABLEMEMBER #SUBCATTOAL OF [d/Energy_Subcategory]  
      VARIABLEMEMBER #DRIVER OF [d/Measure] 
      VARIABLEMEMBER #DRIVERTOTAL OF [d/Measure]
      
      // Aggregate over profit Center
      DATA([d/SAP_ALL_COMPANY_CODE] = #COMPANYCDTOTAL, [d/t.S:SAP_ALL_PLANT] = #PLANTTOTAL, [d/SAP_FI_BPL_GLACCOUNT] = #GLTOTAL, [d/Energy_Subcategory]  = #SUBCATTOAL) = RESULTLOOKUP()
      // Copy driver from other model
      DATA([d/Measure] = #DRIVER, [d/Date] = "202104")
      = LINK([Energy_Valid_Combinations], [d/Version] = "public.Actual", [d/Date] = "202104", [d/Account] = "DRIVER") 
      // Aggregate driver over profit center
      DATA([d/Measure] = #DRIVERTOTAL, [d/SAP_FI_BPL_GLACCOUNT] = #GLTOTAL, [d/t.S:SAP_ALL_PLANT] = #PLANTTOTAL, [d/SAP_ALL_COMPANY_CODE] = #COMPANYCDTOTAL, [d/Energy_Subcategory]  = #SUBCATTOAL) = RESULTLOOKUP([d/Measure] = #DRIVER)
      // Make sure that data are not deleted in case no derivation is maintained
      IF RESULTLOOKUP([d/Measure] = #DRIVERTOTAL, [d/Date] = "202104", [d/SAP_ALL_COMPANY_CODE] = #COMPANYCDTOTAL, [d/SAP_FI_BPL_GLACCOUNT] = #GLTOTAL, [d/t.S:SAP_ALL_PLANT] = #PLANTTOTAL, [d/Energy_Subcategory]  = #SUBCATTOAL) != 0 THEN
      	// Delete old data for all combination plant/product where the condition above is fulfilled
      	DELETE()
      	// Disaggregate to Profit Center
      	DATA() = RESULTLOOKUP([d/SAP_ALL_COMPANY_CODE] = #COMPANYCDTOTAL, [d/t.S:SAP_ALL_PLANT] = #PLANTTOTAL, [d/SAP_FI_BPL_GLACCOUNT] = #GLTOTAL, [d/Energy_Subcategory]  = #SUBCATTOAL) *
      	RESULTLOOKUP([d/Measure] = #DRIVER, [d/Date] = "202104") /
      	RESULTLOOKUP([d/Measure] = #DRIVERTOTAL, [d/Date] = "202104", [d/SAP_ALL_COMPANY_CODE] = #COMPANYCDTOTAL, [d/SAP_FI_BPL_GLACCOUNT] = #GLTOTAL, [d/t.S:SAP_ALL_PLANT] = #PLANTTOTAL, [d/Energy_Subcategory] = #SUBCATTOAL)
      ENDIF

      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

      Author's profile photo Hartmut Koerner
      Hartmut Koerner
      Blog Post Author

      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

      Author's profile photo Edward Hardwick
      Edward Hardwick

      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

      Author's profile photo Hartmut Koerner
      Hartmut Koerner
      Blog Post Author

      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

      Author's profile photo Edward Hardwick
      Edward Hardwick

      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

      Author's profile photo Hartmut Koerner
      Hartmut Koerner
      Blog Post Author

      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

      Author's profile photo Edward Hardwick
      Edward Hardwick

      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

      Author's profile photo Hartmut Koerner
      Hartmut Koerner
      Blog Post Author

      Hi Ed,

      what about Deleting values in a table ?

      Best regards
      Hartmut