Using Data Validation Rules in Planning

Validation rules use case and features:

• Prevent data input for invalid combinations.
• Derive related members based on attribute values in the dimension.
• Warnings in the story help to guide planners.
• Use a combination of members: For example, only allow entry for cost center = Sales and account = advertising.
• Use dimension members referenced as attributes: For example, only allow entry for cost center = CC100_1000, Company 1000, and Profit Ctr PC100.

In planning models, you can define valid combinations of dimension members that control data entry and derivation.

Validation rules define allowed member combinations to prevent improper data entry. For the dimensions you define in a validation rule, only the member combinations that you specify as allowed combinations can pass validation.

Validation rules require dimensions with properties that refer to other dimensions in the model.

To use a validation rule:

1. Enable Validation Rules in the model settings.
2. In the Validation Rules workspace of the model, create a validation rule.
   • Create an attribute-based validation rule:
     • Select the reference dimension.
     • Map the reference dimension property to a matching dimension.
   • Create a member-based validation rule:
     • Select the dimensions to cross reference.
     • Map the allowed dimension members.
3. Activate the rule by turning on the switch under Activate Rule, and then saving the rule.
4. Test the validation rule in a story.

Without using a validation rule to combine two dimensions, you can ensure strict member combinations by maintaining members of one dimension as the other one’s attributes. With validation rules, you can match the existing attributes you maintained for that reference dimension to the matched dimension and create validation rules based on that.

You can create a validation rule by directly specifying the dimension members that allow data entry. Dimension combinations other than the ones you define in rules are invalid and won’t allow data entry or planning operations.

With dimension combination rules, you can match the existing attributes maintained for the reference dimension to the matched dimension and create dimension combination rules based on that.

To define multiple allowed members to match with the reference dimension attribute, enter the members and use a separator such as a comma (,) between the members.

Once a rule has been created, you can view them in the Validation Rules panel.

1. Select the validation rule in order to view it in the workspace.
2. Create a new validation rule or delete an existing rule.
3. Activate, deactivate, or edit a rule. Selecting edit will allow you to change the rule in workspace (it will no longer be greyed out).

To use validation rules, users must have the Planning Professional license and the Validation Rule permission.

• You can create three rules for one model.
• You can define up to three dimensions in one rule.
• You can use the same dimension in up to 10 different rules.
• You can't choose time or version dimensions in a dimension combination rule.
• You can't use system properties, only custom properties are supported.
• All values of the property are available as dimension members in the matched dimension. Otherwise, you'll receive an error or warning message asking you to check and maintain the dimension's master data.

There are two key settings in the story when using validation rules:

1. Enable Color Fill for Editable Cells in the Styling panel.
2. Enable the Reason for unplannable data message for invalid combinations.

It's important to exclude unassigned members in the data table so that when disaggregation occurs, only valid members are updated.

The bottom-up concept is where:

1. You enter data with only cost center in the layout.
2. Company code and profit center values are derived from the validation rules.

In the following example, you can see that when data is entered for Cost Center CC100_1000, then Company Code 1000 and Profit Center PC100 are derived based on the validation rule and posted to the model.

In the following example, you can see that the left side displays the input by cost center (bottom up) and the right side displays the input by company code (top down). When values are entered for the company code dimension, the values are distributed proportionately to posted members and evenly to unposted members.

Validation rules are updated when the member properties are updated. When master data attributes are changed, the following warning is displayed in the model: Dimension combination in rule [rule name] is updated due to master data change in dimension [dimension name] of model ][model name]..

In the story, if a value is changed for the invalid combination, a warning icon appears with the message: Data Changes not Allowed. Any data changes except for deleting data are not allowed due to the validation rules.

When using validation rules, any property value changes will be automatically updated and existing data with invalid combinations will have a warning in the stories.

To realign the existing transaction data, planning functions can be used to copy to the new valid combination and delete the invalid combination.

In the following example, you can see how the data can be realigned using two data actions.

1. Run a copy data action.
   • From company code US (1000) → CA (2000).
   • For cost center CC1000_1000.
2. Run a delete data action.
   • For company code US (1000).
   • For cost center CC1000_1000.