Using Validation Rules in SAP Analytics Cloud: Part 1
Updated: Apr 7, 2021
Get started with setting up and using Validation Rules in SAP Analytics Cloud.
SAP Analytics Cloud (SAC) is a feature-rich solution. If you work in planning models, you will find Validation Rules extremely useful when defining specific ranges to restrict data to outlined intersections. At its core, this feature allows users to maintain dimensional relationships between two members for a given planning model. Simply put, it ensures that your data gets distributed to the right dimensions when you are in a planning scenario.
To help you better understand Validation Rules, we've curated a two-part blog series. In this first part of the series we will explain how to enable and set up a validation rule. In the second part, we will demonstrate how this rule is then applied and why the scenario below is not possible without setting up a validation rule. The following scenario will be used throughout the series:
You have employees, with different titles and want to utilize planning in SAC to add the end of quarter bonuses and maintain salaries changes over time. You recognize that there is a relationship between employees and their title, which can be summarized in a data entity diagram as:
Notice the relationship and cardinality between the two dimensions. Employees must have one and only one job (e.g., David Choi, employee ID: 1025 is an analyst). Jobs can have zero or multiple employees (e.g., David & Catherine are both analysts). Therefore, Employees and Jobs have a many-to-one relationship.
When you are updating salaries and adding bonuses, you are either trying to distribute the budget for a given role amongst employees under it or planning on employees and want their remuneration to aggregate up to their respective roles. This is where Validation Rules come into play.
Enabling and Setting Up Validation Rules
In order to explore and utilize this feature, it must first and foremost be enabled. Validation Rules are managed at the model level – which means you must turn the feature on/off for every model you wish to use. Once enabled, all users with edit rights for that particular model will be able to modify or create new validation rules.
To enable the feature within a model, open the preferences of the target planning model of choice, and turn the Validation Rules feature on.
Next, select OK and save your model to enable Validation Rules. Now that it's turned on, go back to your model, you will see a new tab called Rules appear. This is where you can view, modify, or add Validation Rules. Before doing anything else, we must first check on additional settings.
As mentioned earlier, this feature revolves around dimensions that have relationships. In SAC, these relationships are defined as custom (i.e., you've created them yourself) properties in a model. Following our employee // salary example, our model should look something like this:
Notice that the employee dimension has a custom property called Job.
[Note]: within SAC and in the general data world, property and attribute are used synonymously. Within SAC, although in the model, it is referred to as a custom property. When setting the rule, you will be asked which attribute you desire to map. This may seem confusing but in reality, the system is simply referring to the custom property.
Now that the Validation Rules feature is turned on, it’s time to create our validation rule and test it out. Navigate to the Rules tab and select Create New Rule. For this scenario, we will use the second option, Create with Existing Attributes (as the model already contains the custom properties needed).
For the Reference Dimension, we will select Employee (this is the dimension that contains the custom property that we are trying to match). In the Matched Dimension column, we’ll select Job. This is summarized below:
Finally, we must now name the rule. In this case, we will label it as Employment. Now that the rule is set up, it's ready to be tested!
In this first article, we’ve established a dimensional relationship that must be enforced in the model (between employees and jobs), turned on the Validation Rules feature within SAP Analytics Cloud, and created a validation rule in our model that enforces the data relationship.
In part two of this article, we will apply the rule to achieve the end goal of this scenario to use planning in SAC to add bonuses at the end of quarters and maintain salary changes over time.
To learn more about SAP Analytics Cloud and see all of its powerful capabilities in action, request a live demo from Analysis Prime today!
About author: Jean-Paul Morneau
Jean-Paul (JP for short) has been a SAP Analytics Cloud (SAC) Consultant at Analysis Prime for the past 1.5 years. His skills in SAC lie with the Business Intelligence features, model building, data manipulation / prep, and training of the tool. Outside of work, JP is an avid road cyclist when it isn’t raining in Vancouver, skiing Whistler backcountry in the winter or trying a new recipe he found on Reddit.