SAP Analytics Cloud - Planning using Validation Models

[mo_ezzat]

Use Case:

Let us assume a use case in which we have a planning model with 4 dimensions, aside from date and version:

• Plant
• Cost Center
• Material
• GL Account

We need to prevent the planning users from booking expenses of:

• Certain "Materials" on certain "Plants", a "Material" can be booked to more than one "Plant".
• "GL Accounts" on certain "Cost Centers", an account can be booked to more than one "Cost Center".

In both requirements, none of the dimensions could be used as properties due to to the fact that their cardinality are N:N.

Unfortunately, there is no way to directly upload valid combinations to validation rules, they are either manually created, or supplied via properties.

Workaround:

The workaround is to simply prevent the users from picking invalid combinations from the start, this can be achieved by a shadow model (validation model) + Input Controls.

Allowed Combinations:

Plant	Material
P001	M100
P001	M200
P001	M300
P002	M200
P002	M300
P002	M400
P003	M200

Cost Center	GL Account
CC100	5110
CC100	5120
CC200	5110
CC300	5110
CC400	5110
CC400	5120
CC400	5130

Setup:

As per the requirement, we have 4 dimensions in scope, for ease of maintenance, it is optimal to have all the validations stored in one model, also all of the dimensions in scope to be public dimensions, as this will also lessen the hassle of maintaining authorizations if "Data Access Control" is in scope.

I would also keep it as simple as possible by choosing the data granularity as "Year", and setting the range to 1 year only.

Loading will have to be done twice, once for the first table while mapping "Cost Center" and "GL Account" to "#", and a calculated key figure [MAPPED] = 1.

The job should be configured as "Clean and replace subset of data", choose the 2 unassigned dimensions so that we ensure that all the rows for "Plant" and "Material" are flushed, as they will always be associated with "#" "GL Account" and "Cost Center".

Second loading file that has the other remaining relations between "GL Account" and "Cost Center", "Plant" and "Material" will be unassigned.

This is how the data looks like combined together in the model.

Consumption

This is the most interesting section of the blog post, it is mainly about using the "Cascading Effect" feature, what we need to have is input controls based on the dimensions in scope, that interact differently with each other, namely, we need:

• "Plant" to filter "Material" without impacting "GL Account" and "Cost Center".
• "Cost Center" to filter "GL Account" without impacting "Plant" and "Material".

By placing the input controls all together in one page, we get the following preview, notice that the filter list will be empty for all, as all of them are initially preselected to "#", so they are contradicting each other, as both conditions are contradicting, "#" "Plant"/"Material" is contradicting with "#" "GL Account"/"Cost Center" as we do not have a single line in the model with all dimensions as "#", even if we did, it would still be wrong.

Handling this would be by selecting the input control and changing the "Interactions apply to:" options, before changing them, this is how the input controls would behave:

Let us have one example on "Plant", then I will show the final output after adjusting all of them, select "Plant" input control, navigate to the designer panel, deselect "Automatically Connect Newly Created Input Controls", click "Select Input Controls", select the "Material" Input control, this means that "Plant" is the driving dimension for the "Material" selection.

For "Material", we select nothing, otherwise it is a cyclic reference that will lock the selection options.

For "Cost Center", we choose the "GL_Account" input control only, and finally for "GL Account" we select nothing. Below is the end result:

As shown, the end user would only be allowed to choose between the valid combinations we defined, the main idea would be either that planning directly on the table is disabled, and done via a popup that includes the input controls in scope, or directly through the input controls after selecting a specific line from the planning table, changing the values then clicking a button that will run a data action which will delete the old line, then create a new line with the entered values on the selected combinations performed on the input controls, in that case "Add Member" needs to be invisible during Runtime (Styling panel option).

If the relation between input controls gets complicated, the "Linked Analysis Diagram view" would help in understanding the impact of the widgets on each others. Access it by selecting one of the input controls, 3 dots, Linked Analysis, Diagram View

Only the widget in scope will be displayed, to display all, click on the filter icon and choose the relevant page

Here is how it looks like

It is very important to note that "Linked Analysis" should not be changed in this scenario, in case it is, "Cascading Effect" would only work if at least the input control has a link to at least one widget, in one of my complicated builds, I had to create hidden dummy tables to make the "Cascading Effect" work.

We can also hide the options menu for the input controls to make them appear as simple dropdown lists, this is easily achieved by disabling "Visible in the Runtime" toggle from the styling panel.

Finally, since the "Validation" model is a planning model, a table with the existing combinations can be presented in an admin story, from which admins can quickly change valid combinations, until they are ready to be imported from the source system.

The example below shows how to add "Material" "M100" to "Plant" "P003".

If you would like to mimic the same instant effect, the dummy table idea should be used as the input controls along with the table share the same source, hence if something gets selected from the input control, it will automatically be applied as a filter to the table, which will hinder the task we aim to achieve, which means we need to remove the table from the "Linked Analysis" options for all the input controls, and keep "Cascading Effect" working, this is achieved by the following "Linked Analysis" Diagram.

Recommendations:

• Everything comes at a cost, please keep in mind that workarounds and straying away from standard system behavior might overcomplicate technical builds, leading to a higher cost of maintenance for the solution, our recommendation would always be to stick to the standard features provided for the tool.
• If "Data Access Control" is activated for one of the validation dimensions, access to "#" member of that dimension would need to be granted for all users who will access the application, otherwise the validation model itself will not show values for all non related dimensions, in this example if access to "#" "Plant" is not given to all users, none of them will be able to pick any "Cost Center" or "GL Account" as all their values are excluded because of the relation to "#" "Plant".
• When in doubt, consult the experts! Ask your Account Executive/Customer Success Partner/Technical Quality Manager about the Premium Hub - CoE Analytics team, we have a variety of enablement, and support services that can help you stabilize, expand, and get the most out of SAP Analytics Cloud.