Skip to Content
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:

18 Comments
You must be Logged on to comment or reply to a post.
    • 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

        • 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

          • 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

             

          • 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

             

             

             

          • 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:

            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

          • 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