SAP Analytics Cloud Planning - Validation Rules

[gajendra_moond]

Introduction

SAP Analytics Cloud for Planning (SAC-P) provides a feature called "Validation Rules" to ensure that valid combinations can be defined beforehand to avoid any improper data entry. This blog evaluates this aspect in detail.

 

Landscape

SAC Version: 2021.1.1
EPM library version: 1.00.202101.01.1605530525

 

Requirement

How to ensure that SAC-P allows for planning against valid combinations only.

 

Solution Details

Model Structure

Let us have a model with the following dimensions:

• Version


• Time


• Account


• Cost Center


• Profit Center

Here is the basic layout of the dimensions

Account

Profit Center

Cost Center

 

Here is a sample story with setting "Unbooked data" on (Unassigned members excluded).

As it can be seen that the story has generated output based on Cartesian product while the intent is to present only valid combinations that should be permitted for data input.

 

Validation Rule Configuration

Before the validation rules can be established, the following setting must be enabled under model preferences:

 

This is a model level setting and hence once the rules are established, data input is completely governed wherever this is true.

Once the setting is enabled, additional tab shows up as highlighted:

Under the Rules, tab new rules can now be configured. There are two ways of creating rules:

• Create with members


• Create with existing attributes (equivalent for linking property of one dimension with member of another dimension)

 

Please note that Version and Date(Time) dimensions are not available for defining rules.

Create with members

Here all the dimensions, except, Version and Date can be called and unique combinations can be defined. Let us consider the following scenarios:

• Allow Revenue Input against member No Cost Center only for all profit centers


• Allow COS input against all Cost Centers and Profit Centers except the default and unassigned members

Tip: Unassigned ('#') is not permitted for selection. Create a dummy member if data need to be posted on unassigned.

Add the following validation rule with two steps as shown below:

All of a few dimensions can be selected to define the rules.

 

When data is entered in the said story, it will update accordingly as per the rules defined as shown below:

 

Create with existing attributes

This validation rule is identical to linking the property of a dimension with the member of another dimension. However, the approach is more central and at model level i.e. once the rule is defined, the other combinations can not be planned at all unless rules are deactivated or adjusted. However, if you are looking for a workaround the above limitation, here is one of the ways to handle it.

Tip: Account dimension cannot be used with this approach. For that, create rules using "Create with members" option

Let us say we define Profit Center as an attribute of the Cost Center. Do note that, multiple members can be assigned as attribute as comma separated values.

When a validation rule is created using "Create with existing attributes" option, it begins with selecting a Reference Dimension (Cost Center in this case) and corresponding one or more matched dimensions (Profit Center in this  case).

Now that the rule is in place, and data input is performed in the story, the data input and update will happen only for the valid combinations. In the story mentioned above, the data should get updated only for the following combinations

• CC1-PC1


• CC1-PC2


• CC2-PC2

When data is entered at top node for the Cost of Sales, note that the disaggregation happened only on the valid combinations. The invalid combinations are not even permitted for data input.

 

Some key observations:

• Version and Time dimensions are not available when configuring validation rules.


• Maximum number of valid combinations is 1 Million


• Do not repeatedly use more than 1 dimension in different validation rules. This will pose limitation in terms of creating complex rules. However, a careful dimension design might help circumvent this limitation.


• The unassigned member is not permitted for selection when creating rules using "Create with members" option.


• No cyclic reference cross validation rules are allowed


• Since it is a model level setting, the data cannot be published via data actions for invalid combinations.


• If the data is present against invalid combinations, system only allows to delete it.


• The validation rules are not applicable when data is loaded/published via file or any other external data source and data can be loaded against combinations not true to validation rules. However, the loaded data will show a warning icon.

• In the above case, if the data is entered at the node level, the invalid member is completely ignored for disaggregation and value is recalculated accordingly.

[Mark_P_Burke]

Thank you for this summary.  We are looking into setting up best practice validation rules in SAP S/4HANA planning scenarios in SAP Analytics Cloud for use with pre-delivered business content, and your blog will be helpful.

[ThomasK]

Today I discovered this and could not believe my eyes ...

I´d say I´ve a normal finance planning solution with more than 10 different sub-plans. In almost all of them a validation rule for profit center makes sense. Now it seems I need to duplicate the profit center dimension to avoid this restriction, which is out of touch with reality. 

Can anybody explain this restriction from a business and/or technical viewpoint?

[danielsanders]

Hi, nice overview of the validation rules. I would lik to be able to use valicdation rules like Characteristic relationships in BPC Embedded ( BW IP ). So nu just to prevent users from booking invalida combinations, but also to limit the number of combinations generated when using Unbooked members. Because, why should the system display invalid combinations only to disable input for users.

I couldn't find a lot of activity on validation rules in the roadmap, is this functionality that I can expect in the future? Or is this it for the validation rules, in which case I don't intent to use it very often.

[danielsanders]

Hi Thomas,

I don't fully understand the requirement to add a validation rule for each planning. If you have a "Sub planning" dimension that is linked to the Profitcenter dimension through an attribute. That would require 1 validation rule.

So do you have multiple (more than 10) version dimensions?

 

Kr,

Daniel

[ThomasK]

Hi,

the limitation is documented here:

Creating Validation Rules by Combining Dimension Members (sap.com)

• 
  

  You can use the same dimension in up to 10 different rules.

  
  

In a financial planning application you have maybe 6-12 subplans. In all of them I´m using Profit Center and validation rules. Now I had to duplicate this public dimension for reason of this random restriction.

KR

Thomas

[gajendra_moond]

Thank you for your comment. We have options for "Booked" and "Unbooked" data. If you just want to have only plannable combinations to show up, then pre-load the combinations in the model and have your stories set to "Booked" data. This is not exactly what you are asking but suggesting a work around.

Another approach where you can refer this blog to mimic the Characteristic Relationships functionality - Characteristic Relationships.

At this point, since SAC is being updated every quarter, I am hopeful of some changes coming for validation rules now that new model has been released with the latest update supporting multiple key-figures. There are a lot of areas in SAC where improvements are desired and validation rules is one of them. I hope someone from SAP can provide an assurance here.

[raj_vangala]

Hi Gajendra,

 

A great blog, thanks for sharing.

Any progress/idea on just generate only valid combinations with or without booked option.

Your alternate solution on prepopulating the valid combinations is a good option but out-of-the box solution really useful.

Raj

[Anna_D]

Hi Gajendra,

Thanks for sharing the post. Very Interesting.

I have noticed that validation rules are checked only with manual input or while trying to publish data. But what about Data Actions results? Why Validation rules are not checked during Data Action calculations? Am I missing any settings?

I tried with a simple copy step - it is copying everything.

I tried with an Allocation step - it is allocating also on not allowed combinations. Even if when I copy only data on allowed combinations are published, my total allocated amount is not the one desired.

Thank you