Unleashing the Power of Validation Rules in SAP Analytics Cloud

Data integrity is a major concern for data quality initiatives. In SAP Analytics Cloud (SAC), it can be achieved with validation rules. In this blog post, we will explore the fundamentals of validation rules, their significance, and effective strategies for leveraging them in SAC.

In the realm of data analytics and strategic decision-making, SAP Analytics Cloud (SAC) stands out as a powerful cloud-based solution. One vital tool for maintaining data integrity in SAC are validation rules. They allow us to define valid combinations of dimension members, ensuring accurate data entry and analysis.

The Role of Validation Rules

Validation rules in SAC serve various purposes, including:

  • preventing input of invalid combinations,
  • deriving one characteristic from the attribute of another, or
  • highlighting existing invalid combinations.

 

During the data publishing process in SAC, these rules identify and discard any invalid combinations. Invalid cells will be marked with a warning icon in all versions. However, it is worth noting that a ‘Planning Professional’ license is required to maintain these rules.

Two Approaches to Maintaining Validation Rules

There are two major approaches when maintaining validation rules in SAC:

Figure 1 two approaches for validation rules
Figure 1: Two approaches for validation rules

Creating with members

This method involves directly selecting allowed values from master data value lists. While accurate, it can be time-consuming when dealing with various combinations. This method focuses on the actual dimension members and is suitable when you want to control the fundamental values themselves.

Creating with existing attributes

This method uses one dimension with custom values or a list of values as an attribute. This dimension is then used as a reference for maintaining the validation rules, for example, matching the ‘Plant’ with ‘Company Code’. (Please note: With the latest SAC updates, the attribute length restriction in SAP Analytics Cloud (SAC) has undergone a significant transformation. Previously limited to a length of 128 for all properties of type ’Text‘, SAC’s recent upgrade to version 2023.13 or later now allows for a maximum length of 5,000.) However, using validation rules in SAC does come with certain limitations:

  • Cross-validation rules are not allowed. If we have a company code as an attribute of the product and a company code as an attribute of the plant, we cannot add both to the validation rules.
  • Only three rules per model are allowed.
  • When creating a rule, you can only use up to three dimensions. In other words, a single rule can take a maximum of three different factorsinto account. However, it is also crucial to plan out your dimensions carefully. Strategic dimension design can prove particularly useful when dealing with complex rules. If a rule involves more than one dimension, it can become more complicated to manage.
  • Only customer properties are visible for the validation rules.
  • The maximum number of valid combinations is one million.
  • The version and date dimensions cannot be used for validation rules. Data for these dimensions can be restricted by data locking.
  • All attribute values should be available as dimension members in the matched dimension, or you may receive an error or warning message.

 

Interestingly, when there are multiple attribute values used for a validation rule, the members are displayed using a comma (,) as the separator, with no extra space between the separator and the member, like this: member,member.

You can create invalid combinations using the ‘Add Member’ function, but the value will not be saved due to the validation rule.

Technical Considerations

Certain technical restrictions should be kept in mind. Planning cannot be restricted to a dummy value with a validation rule.

  • If one member of a dimension is valid, unassigned members are also considered valid (managing this scenario may require using filters or data locking).
  • Conversely, if all assigned members of a dimension are invalid, unassigned members are also deemed invalid.

 

By following these guidelines, you can harness the power of validation rules in SAP Analytics Cloud to ensure data quality, leading to more accurate and reliable analyses.

Practical Case: Streamlining Input Forms and Creating Valid Combinations in a Business Scenario

In this practical case, we successfully focused on streamlining the data entry process by creating an input form ensuring that only valid combinations can be entered. Our use case involved a model that tracked the quantity of materials required for each product, with each product being manufactured at specific plants. By thoroughly understanding the model structure and leveraging data actions and validation rules, we effectively simplified the data entry process and achieved accurate results.

Model structure

During our analysis, we examined the model structure, which included key dimensions such as version, date, company code, plant, product, and material. Notably, the plant dimension was associated with the company code attribute, while the product dimension presented a list of selectable plants. Our focus in the input form was specifically on analyzing data related to plant, product, and material, while ensuring that the company code was derived from the selected plant properties.

Figure 2 Key dimensions for the model structure
Figure 2: Key dimensions for the model structure
Figure 3 Public dimension ‘Product’ with attributes
Figure 3: Public dimension ‘Product’ with attributes

Creating available combinations

In our implementation, we utilized data actions to create available combinations by copying valid product-material combinations as a first step from a separate model, serving as a master data storage. We created an additional model because we use SAC version 2023.13 which does not allow to load more than 128 symbols for attributes, and data action cannot consume separate value form list of values in attributes.

Figure 4 Creation of all product-material combinations
Figure 4: Creation of all product-material combinations

Data action creates only possible combinations of product material for all plants. Unassigned value for ‘Plant’ is available, and to manage those cases, the data action performs a deletion of the unassigned plant value.

Figure 5 Validation rule for product and plant
Figure 5: Validation rule for product and plant

Upon data publishing, we cross-checked the combinations created in step 1 with validation rules for product-plant combinations. Any invalid combinations were promptly flagged and not saved, thereby maintaining data integrity.

Figure 6 Warning for an invalid combination
Figure 6: Warning for an invalid combination

Additionally, the company code value was automatically populated based on the associated plant’s company code attribute, as specified by the second validation rule.

Streamlining Data Entry to Maintain Data Integrity with Validation Rules

By successfully implementing these steps, we created an input form that streamlined the data entry process and allowed only valid combinations. This approach not only improved data integrity but also enhanced the user experience, enabling more accurate and efficient tracking of material quantities for each product. With a well-designed input form and the power of validation rules, your business units can confidently make informed decisions based on reliable and precise data.

To effectively use validation rules in SAC, consider the following best practices:

1. Understand your data model

Gain a comprehensive understanding of the dimensions, properties, and relationships within your data model. This knowledge will guide you in defining meaningful validation rules.

2. Define logical combinations

Analyze your data requirements and define valid member combinations based on logical rules. Consider business rules, constraints, and dependencies to determine which combinations should be allowed.

3. Test and iterate

Test your validation rules with sample data. Iteratively refine the rules until they accurately reflect your intended data integrity requirements.

4. Communicate rules to users

Clearly communicate the validation rules to users who will be entering data. This ensures they are aware of the restrictions, and it helps prevent errors during the data entry process.

5. Regularly review and update rules

As your business requirements evolve or new data patterns emerge, periodically review, and update your validation rules to maintain data integrity over time.

Through the strategic use of validation rules in SAC, you can boost the accuracy and trustworthiness of your data analysis. This lays a solid foundation for making well-informed strategic decisions. These rules not only bolster data integrity but also enhance the efficiency of planning applications, stories, and data actions. The result? Improved business outcomes driven by improved data quality.

Latest Blog Posts