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