Validating Planning Data Input

Objectives

After completing this lesson, you will be able to:

  • Validate planning data input

Data Validation Overview

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.

Note
If the property is used as a column in the data table, the Add Member and unbooked data options are disabled.

Configure Data Validation

Enable Validation Rule in the Model

Validation Rule enabled in the Model

How to Use a Validation Rule

To use a validation rule:

  1. Activate the feature in the model.
  2. Map members from one dimension to the property values in a reference dimension.
  3. 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.
  4. Test the validation rule in a story.

Validation Rule with Existing Attributes

Dimension combination rule screen.

Validation Rule with Members

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.

Notes:

Customers with the Planning Professional license are granted 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.

Create Data Validation Rules

Task 1: Configure the Model to Enable Data Validation and View Model Members

Business Scenario

As you input data as part of the income statement forecast, you need a way to prevent data entry for invalid combinations and to derive data based on valid combinations.

You decide to create data validation rules to control data input and to perform derivation but first you have to enable data validation in the model.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Enable data validation in the model
  • View the dimension members

Task 2: Create an Attribute Combination Validation Rule

Business Scenario

You decide to create an attribute combination validation rule to specify 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.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Access the validation rules workspace
  • Create validation rule based on existing attributes
  • Configure the validation rule

Task 3: Create a Member Combination Validation Rule

Business Scenario

You decide to create a member validation rule that allows postings only for specific cost center and account combinations.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Create validation rule based on members
  • Configure the permitted member combinations

Perform Data Validation in Stories

Story Settings

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

  1. Enable the Color fill for editable cells format.
  2. Enable the Reason for unplannable data message for invalid combinations.

Excluding Unassigned Members in the Input Form

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

In the example below, you can see when validation rules are enabled and a property assignment is changed, existing postings will display a message in the story as explained. In this scenario, a data action can be used to:

  1. Copy the values to the valid combination.
  2. Delete the invalid combination.

Bottom up Scenario

Bottom up concept:

  1. 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.

Bottom up Scenario

Top Down Scenario

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.

Top Down Scenario
Note
When new members are added in the story, you can select property values for them, and the system will then update the validation rules in the background.

Result in a Story for an Invalid Combination

In a layout with multiple dimensions in the rows, validation rules don't control the combinations of unbooked members. Therefore, when a value is entered for an invalid combination, a message will appear.

"You cannot enter data in this cell due to a validation rule defined for the underlying data model. To enable data entry, please contact your planning modeler to adjust the validation rules for this model."Result in a Story for an Invalid Combination

If the planners disaggregate the values in such a story, booked data has a higher priority than unbooked data:

  • If the planners disaggregate a value to both valid and invalid booked data, the value will only be disaggregated to the valid booked cells.
  • If the planners disaggregate a value to both valid and invalid unbooked data, the value will only be disaggregated to the valid unbooked cells.
  • What happens if the planners disaggregate a value to invalid booked data and valid unbooked data? The value will be disaggregated to the booked invalid cells and an error message will inform you that the disaggregation failed.
Note
  • Data actions don't check for validation rules, however while publishing the data, validation rules are checked for data correctness, and invalid records won't be saved.
  • Data imports don't check for validation rules.
  • Validation rules are updated when the member properties are updated. Invalid / posted data will display a warning: Data Changes not Allowed. Due to validation rules, any data changes in this cell except deleting data will be reverted when you publish data entries.

Manage Attribute Changes

A Change to a Value Causes the Data to Need Realignment

When master data attributes are changed, there's a warning that users will see in the dimension. In the story, if a value is changed for the invalid combination, the message is: Data Changes not Allowed. Due to validation rules, any data changes in this cell except deleting data will be reverted when you publish data entries.

Warning: Dimension combination in rule C is updated due to master data change in dimension Cost_Center of model validation.

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.

Realign the Data

In order to realign the data:

  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.

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

Test Data Validation Rules

Task 1: Test Validation Rules in a Story

Business Scenario

Now that you have configured data validation rules, you need to test them in stories.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Create a new story with a canvas page
  • Configure the data table to display a color for editable cells
  • Show Reason for unplannable data
  • Test the validation rule

Task 2: Use Derivation

Business Scenario

Your model has validation enabled for attribute combinations. When you enter data for a high-level member, the data will be disaggregated only to valid combinations.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Create new story page
  • Configure the data table
  • Verify that only valid combinations are disaggregated to

Task 3: Use Member Combinations

Business Scenario

Your model has validation enabled for member combinations for specific cost centers and accounts.

What skills will you develop in this practice exercise?

In this practice exercise, you will:

  • Create a new story page using duplication
  • Use a validation rule to allow input only on valid combinations

Log in to track your progress & complete quizzes