Data Validations Business Logic

Data Validations are developed to enhance data quality in financial reporting. Data Validations are flexible and can be extended according to individual customer needs.

Data Validations are executed as part of different workflow steps (system activities), and their results are saved in the database and displayed on different predesigned web reports in models. The results of data validations are displayed on different detail levels, starting from a summary displayed on a scope level and going into the most detailed degree.

Implementation

Data validations are integrated into the database as calculated measures. For every distinct data validation, an element exists within the measure dimension of the corresponding cube. Within this element, specific attribute values contain the calculation guidelines, which are subsequently used by rules to compute the respective data validation. Data validation is considered successful or failure based on its outcome and the predefined threshold.

You can configure Target restrictions and calculation dependencies for individual validations. Additionally, you can set the severity attribute, determining whether failing the check should result in an error or a warning.

For instance, if you set a threshold of 0 and designate the severity as "Error," the associated data validation will be categorized as an Error when its result surpasses 0; otherwise, it will be classified as successful.

In total, there are three rule templates used for data validation evaluation:

  1. Data validation calculation

  2. Data validation errors and

  3. Data validation warnings

Each rule template generates one rule instance for a single data validation.

In total, there are three rule instances for each data validation:

  1. Calculate the result of the data validation based on the associated calculation logic.

  2. Set 1 if the Data Validation Severity = Error and data validation result > Data Validation Threshold; otherwise, set 0.

  3. Set 1 if the Data Validation Severity = Warning and data validation result > Data Validation Threshold; otherwise, set 0.

Here is the Balance Sheet_measure dimension as an example:

The parent element, Data Validations, and its descendants define calculation logic for the data validations, which are valid for Balance Sheet and Balance Sheet (Segment) cubes.

In the Calculation column, we see the calculation instructions. In the TargetRestriction column , the target cells are defined. In the Data Validation Threshold column , there is a threshold that decides upon the success or failure of the data validation. Lastly, the Data Validation Severity column shows the type of each data validation (Error or Warning).

The above details are currently defined via a sample data file but can also be modified or created directly on the dimension.

Further elements, such as Data Validation Errors and Data Validation Warnings and descendants, are used to count errors and warnings for each validation.

Currently, data validations are implemented for Balance Sheet, Balance Sheet (Segment), Scope of Consolidation, Profit and Loss, and Profit and Loss (Segment) cubes.

Data Validations in Financial Consolidation Model

The Data Validation functionality of the Financial Consolidation model allows the setup and running of various automatic checks on data, which help ensure sufficient data validity for the financial consolidation by checking data in the database for consistency. These checks can be set up and integrated into a Workflow Task via the Workflow functionality of the Financial Consolidation model1.

Data Validations in the Financial Consolidation Model are set up according to different Workflow activities:

  1. Workflow activity Configuration Validation has ID STATE_42 and is executable for Scope of Consolidation cube with Scope of Consolidation_measure dimension, Balance Sheet and Balance Sheet (Segment) with Balance Sheet_measure dimension, Profit and Loss and Profit and Loss (Segment) cubes with Profit and Loss_measure dimension.
    The result of the checks is shown on the Configuration report.
    Currently, 18 configuration data validations are implemented, but this figure can vary from customer to customer.

  2. Workflow activity Data Validation has ID STATE_52 and is executable for Balance Sheet and Balance Sheet (Segment) with Balance Sheet_measure dimension, Profit and Loss, and Profit and Loss (Segment) cubes with Profit and Loss_measure dimension.
    The result of the checks is shown on the Data Collection report.

  3. Workflow activity Separate Financial Statement Validation has ID STATE_45 and is executable for Balance Sheet and Balance Sheet (Segment) cubes with Balance Sheet_measure dimension.
    The result of the checks is shown on the Consolidation Manager report.

For every failed check (error or warning), a link to additional information (via a pop-up window) is provided (as a blue arrow). For more details, see the documentation on each of the above reports.

For Data Validation setup within Workflow, see the related article.

Naming convention

Data Validations are named after their respective workflow activity steps and are grouped by workflow step and cube.

The name starts with DV (Data Validation) followed by the workflow activity step, e.g., 42. The last digit groups single data validations according to the cube they are valid for, e.g., data validation groups DV422xx and DV424xx validate data configuration. However, the first group, DV422xx, is valid for the Balance Sheet cube, and DV424xx is valid for the Balance Sheet (Segment) cube.

The naming condition requires an _E at the end of the data validation (group) ID for the error data validation and _W at the end of the warning data validation, e.g., DV42201_E stands for failed error data validation, and DV42201_W for failed warning data validation.

Users have the flexibility to employ their preferred naming conventions, with the option of using the "_E" or "_W" suffix to differentiate between errors and warnings. Note that if you choose to use your own naming conventions, further configuration adjustments are required (e.g., on reports).

Data Validation Calculation Example

Let’s assume that data validation DV52101 from Balance Sheet Cube has a result amounting to 100€.

The threshold for this data validation is defined to be "0.1" and the data validation severity is an Error.

This means that the associated element DV52101_E under Data Validation Errors will be set to 1, which means that the data validation has failed.

Data Validation Configuration

Data Validations often use specific accounts or other KPIs. During model configuration, you must adjust those data validations with their own setup. Other Data Validations do not depend on system configuration and are valid for every customization. You can find all the existing data validations in Configurable and not configurable Data Validations.

Here is an example of modifying the data validation DV52101 from the Balance Sheet Cube:

Copy 
['Balance Sheet_measure^'∶^' DV52101^' ,^' Partner Entity^'∶^' ~^' ,^' BS Account^'∶^' ~^' ,^' Transaction Type^'∶^' T000^' ]
=B: ['Partner Entity':'All Partner Entities','BS Account':'100000','Balance Sheet_measure':'Value']
- ['Partner Entity':'All Partner Entities','BS Account':'200000','Balance Sheet_measure':'Value']

This Data Validation calculates the difference between Total Assets and Total Equity and Liabilities figures (due to some limitations, hard-coded account numbers are used). Account numbers ('100000' Total Assets and '200000' Total Equity and Liabilities) need to be replaced by customer-specific ones to adjust the data validation to the customer's environment.

To do this, modify the associated entries, on Balance Sheet_measure Sheet in the Excel sample data workbook, upload the changes to the modeler, and update the rule template on Balance Sheet and Balance Sheet (Segment) cubes.

  1. Update the Excel sample data workbook. Adjust the formula in the Calculation column.

  2. Upload the changes to the modeler.

  3. Click on update rule templates on Balance Sheet and Balance Sheet (Segment) cubes.

Data Validation implementation works similarly to KPI calculation logic.

For more details on KPI calculation and rule syntax, see Adding Business Logic to Cubes article.

User Guide

As already described, Data Validations are powerful tools.

The results of data validations are displayed on Configuration, Consolidation Manager, and the Data Collection reports.

Looking at the Consolidation Manager overview, we see the following table:

The table shows data validation results per Scope and Cube. If no data validation has failed, the user sees an empty table. In case there are warnings, as shown in the column Separate Financial Statement Validation (Balance Sheet), the grey circle with an exclamation point and the blue arrow next to it are shown.

In case of error, as visible in the column Separate Financial Statement Validation (Balance Sheet (Segment)) for Subgroup European Union, the red triangle with the blue arrow next to it is shown (e.g., 6 errors, 499 warnings).

Please note that error data validations are considered more severe and displayed in the All Scopes column. Moving the mouse over the arrow, you will see information about the number of errors and warnings in a tooltip.

You can also open the more detailed Data Validation Results Report by clicking the related blue arrow.

This report provides more detailed information on the failed data validations. It shows the list of failures followed by the list of warnings on a single data validation level, including the data validation description and associated TargetRestriction.

Clicking the blue arrow in the right column opens the Data Validation Details Report.

This report gives the most detailed information on failed data validation. In the first sentence, there is a summary of the concerned slice, followed by the list of failures. This information can be used to adjust the reporting figures and rerun the consolidation process.

Known issues

The current version of Financial Consolidation displays certain "false positive" data validation failures. These failures occur because the data validation checks for opening balances at the start of the first reporting year, comparing these figures to non-existent closing balance data from the preceding year. These particular validation issues are categorized as warnings and can be safely disregarded.

Please be aware that this is a known issue and will be addressed in a future update.

 

Updated July 8, 2024