In some planning scenarios, planning on "non-posted" entities/characteristics is required. This requirement can be further complicated if there is a need to allow only certain combinations of entities (in the SAP context, the term "characteristic combinations" is often used for this). SAP Analytics Cloud (SAC) offers various options for implementing this requirement.
As a rule, historical actual data is used for planning scenarios, which not only serve as guidelines/proposed values for future planning, but also specify the breakdown or the permissible combination of entities (e.g. different customers for different sales organizations). In some planning scenarios, however, actual data is not available, but the planning screens should still only allow certain combinations of entities to be entered.
With hierarchies, dimension attributes ("user-defined properties") and validation rules, SAP Analytics Cloud already offers "out-of-the-box" functionalities to cover this requirement, which will be highlighted in this article. In addition, a simple in-house development with the help of "Data Actions" is explained, which brings additional advantages to the standard SAP functionalities.
Requirements definition
As already described in the instructions, the requirement is to only allow certain combinations of entities for planning. Invalid entity combinations must not be planned and, in the best case, should not be displayed at all. No historical data is available for the planning scenario.
The combination of 3 fictitious entities will serve as an example in the following:
- Sales Organization (SALES_ORG)
- Customer group (CUSTOMER_GROUP)
- Customer (CUSTOMER)
Only the following combinations should be allowed for these 3 entities:
| Sales organization | Customer group | Customer |
|---|---|---|
| 1000 | A | 1001 |
| 1000 | A | 1002 |
| 1000 | B | 1003 |
| 2000 | A | 2001 |
| 2000 | C | 2002 |
| 2000 | C | 2003 |
Implementation SAC
Initial scenario
To implement the requirement, the first step is to define a SAC planning model as follows:
This is an account-based (account dimension) planning model. However, a key figure-based planning model would be just as conceivable for the procedure described in this article.
The dimensions (generic type) used for the combinations have the following characteristics:
| Dimension | Member ID |
|---|---|
| Sales organization | 1000 |
| 2000 | |
| Customer group | A |
| B | |
| C | |
| Customer | 1001 |
| 1002 | |
| 1003 | |
| 2001 | |
| 2002 | |
| 2003 |
The account dimension only has one example that is to be planned:
Based on the model defined above, a story is defined with a planning table and the 3 dimensions sales organization, customer group and customer in the drilldown. All 3 dimensions are given the setting "Unposted data":
As expected, the SAC forms a cross product over all combinations of the 3 dimensions. This does not meet the requirement that only certain combinations should be available for planning:
Problem solution
One way to implement the requirement would be to use hierarchies. However, this would only allow correct combinations for stories that use this hierarchy. A story with a flat representation could still allow invalid combinations.
Validation rules based on "user-defined properties"
In addition to hierarchies, SAC offers another option for modeling relationships between entities (dimensions) in the form of "user-defined properties". "User-defined properties" have the advantage that they can not only be used as filters in stories, for example, but can also form the basis for defining validation rules. Validation rules prevent the storage of invalid combinations at model level.
Two "User-defined properties" ("Sales organization" and "Customer group") are therefore defined in the Customer dimension (CUSTOMER) (point 1 in the screenshot) and the relationships are entered accordingly in the master data table (point 2 in the screenshot).
For the sake of completeness, it should be noted at this point that the drilldown could now also be designed using the defined master data in such a way that only the "Customer" dimension could be included in the drilldown and the "Sales organization" and "Customer group" properties could only be displayed as additional information. This would at least allow the desired combinatorics to be displayed:
The input data would only be physically written on the "Customer" dimension. With the help of validation rules and/or self-defined "Data Action", the two properties "Sales Organization" and "Customer Group" could be derived and also persisted. However, as the dimension properties do not correspond to "fully-fledged" dimensions and this results in various restrictions (e.g. no ad-hoc filtering, no display of texts for the IDs, no sorting option), this solution approach is not considered further here. The solution approach described below uses all 3 dimensions for the drilldown, as already described in the initial scenario.
Based on the master data defined above, the validation rules are created in the first step. These must first be activated in the model properties:
After activation, the validation rules can be found here:
The validation rules can now be defined using the "Create with existing attributes" option:
The mapping should look like this at the end:
Welcome feature: The validation rules are automatically updated when the master data ("user-defined properties") on which they are based is adjusted 👍
If the story defined above is called up again, the invalid combinations are now blocked for input (only the fields marked in blue are ready for input):
Optimization of the planning screen with the help of a "planning grid"
Now the breakdown still needs to be "cleaned up" to remove the invalid combinations.
A hierarchical representation could also be used for this purpose. However, you would then have to maintain the relationships in two places, as "User-defined properties" of the Customer dimension and again as a hierarchy.
Another option is to set up a "planning grid" based on the stored attributes using a self-defined "data action", so that planning can be carried out later in the planning screen using booked "dummy" data.
To do this, a dummy account is first created in the Account dimension:
The following "Data Action" of type "Script" is then implemented:
MEMBERSET [d/Measures] = "AMOUNT_LC"
MEMBERSET [d/Account] = "Dummy"
DATA([d/SALES_ORG] = [d/CUSTOMER].[p/ATTR_SALES_ORG], [d/CUSTOMER_GROUP] = [d/CUSTOMER].[p/ATTR_CUSTOMER_GROUP]) = 1
As described above, this "Data Action" creates the corresponding rows in the facts table with the "dummy" value 1 (any other value can also be used) based on the stored entity combinations of the Customer dimension.
The "dummy" characteristic is now added for the account dimension in the story table and can also be hidden immediately, as it is only intended to span the "planning grid".
The last step is to deactivate the "Unbooked data" property for the 3 dimensions.
Now the table in the story only shows the permitted combinations as required:
The fact that planning is now based on booked "dummy" values also has other advantages, e.g. for filtering, but this aspect will not be discussed further in this blog.
Conclusion
As the implementation example above shows, SAC offers various options for planning the requirement for "unbooked" entity combinations. In particular, the function to define "user-defined attributes" on a dimension proves to be very helpful in this context. In the example, validation rules were used not only at model level to ensure that only the combinations available in the attributes could be planned. The same attribute information could also be used to span a "planning grid" with the help of a self-defined data action, so that the input screen only provides the valid entity combinations for display.
Author: Michael Wilk
Your contact person
Thomas Riedel
Managing Consultant
SAP Information Management
Thomas.Riedel@isr.de
+49 (0) 151 422 05 422
