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.