Views:

Overview

The Office of the Superintendent of Financial Institutions (OSFI) requires regular filings from Canadian financial institutions. Using North52 Business Rules Engine, organizations can automate data validation directly within Dynamics 365 or Dataverse - ensuring totals reconcile with balance sheet figures, loan classifications are accurate, and reporting thresholds are met before submission.

This article uses the OSFI A2 Non-Mortgage Loans return as an example. This quarterly filing, required for all Canadian deposit-taking institutions, captures non-mortgage loan data classified by institutional sector - including loans to financial institutions, governments, individuals, and businesses - along with expected credit loss allowances, credit score distributions, and top counterparty exposures.

We focus on a subset of rules to validate in this example, providing guidance on one possible way to validate the data using a Dataverse record as the source of the information. North52 rules can also be implemented to validate data from other file types like XML and JSON. Rule validation messages can be returned in a Model Driven Form (like in the example below), and API response or via a Power Pages website. Get in touch with our support team if have any questions: support@north52.com  

Rules for Validation

The validation rules for the return are defined in a spreasheet on the OSFI website: https://www.osfi-bsif.gc.ca/sites/default/files/documents/a2-validation-en.xlsx?v=1767888621423

A sample of 5 (out of 190+) rules is shown below:

Rule #Description
IR001

Subtotal of Financial Institutions FC Resident Loan Balances should add down

EqualWithinThreshold([8807],
     [8800] + [8801] + [8802] + [8803] + [8804] + [8805] + [8806],
     0, 10)

IR002

Subtotal of Financial Institutions TC Resident Loan Balances should add down

EqualWithinThreshold([8816],
     [8809] + [8810] + [8811] + [8812] + [8813] + [8814] + [8815],
     0, 10)

IR003

Subtotal of Financial Institutions FC  Non-Resident Loan Balances should add down

EqualWithinThreshold([8827],
     [8820] + [8821] + [8822] + [8823] + [8824] + [8825] + [8826],
     0, 10)

IR004

Subtotal of Financial Institutions TC Non-Resident Loan Balances should add down

EqualWithinThreshold([8835],
     [8828] + [8829] + [8830] + [8831] + [8832] + [8833] + [8834],
     0, 10)

IR005

Subtotal of Canadian governments FC Resident Loan Balances should add down

EqualWithinThreshold([8849],
     [4300] + [8808] + [8848],
     0, 10)

Screenshot of Excel file:

 

North52 Decision Suite Solution

The North52 Decision Suite solution works like this:

  • A custom table is created to store the data for the A2 Non-Mortgage Loans return data
  • A Formula is set up on the custom table which:
    • Triggers on change of the Validate Data field to Yes
    • On the Data Validation sheet each rule is evaluated and if the rule fails validation, a SetVarConcat() Validation Message variable is updated with an error message, and the Valid variable is set to FALSE
    • On the Update Record sheet:
      • If the Valid variable is FALSE, the Validation Messages field is updated with the error messages and the Status Reason is set to Not Valid
      • Otherwise the Validation Messages field is updated with ✅ Data Validated and the Status Reason is set to Valid

 

Set Up Formula

Create Formula

  • Open the North52 App
  • Navigate to Business Process Activities > Formulas
  • Create a new Formula, setting the following values in the Formula Guide:
    • Source Entity set to Non-Mortgage Loans Report
    • Set Formula Type to Save - Perform Action
    • Select the Decision Table editor
  • Change the Name of the formula to Non-Mortgage Loans Report - Validation
  • Set the Source Property to Validate Data
  • Click Save

Sheet - Initial Check

This sheet simply evaluates whether the Validate Data boolean field is set to No, and if so exits from any further processing. Only when this field has been set to Yes will the rules (on the next sheet) be evaluated.

Sheet - Data Validation

This sheet does all the validation work and contains the logic for all the rules defined in the Non Mortgage Loans (A2) Validation Rules spreadsheet. 

If the Validation condition defined in column B is breached, the Valid variable in column C is updated to FALSE, and the Message (column D) is added (SetVarConcat) to the Validation Message variable. If more than one row fails validation the messages are combined to be output as one overall message in the update on the next sheet.

Example of the logic for rule IR001. In the validation rules spreadsheet this is defined as (numbers in square brackets reference specific fields):

EqualWithinThreshold([8807],
     [8800] + [8801] + [8802] + [8803] + [8804] + [8805] + [8806],
     0, 10)

The EqualWithinThreshold is a function defined as: 

EqualWithinThreshold(firstValue, secondValue,percentageThreshold, numericThreshold)

North52 does not have this specific function but we can create the same logic using the following (no percentage threshold required):

Abs([entity.firstvalue] - [entity.secondvalue]) <= [entity.numericthreshold]

When evaluated this will return TRUE or FALSE, if FALSE it will match the condition in cell B3 and update the Validation Message (press F4 or right-click and select Operations > Toggle Advanced Mode to show row 3)

Sheet - Update Record

This sheet updates the record with the validation outcome. 

If the Valid variable is FALSE, then the Validation Messages field is updated with all messages that failed and sets the Status Reason to Not Valid. 

If the Valid variable is not FALSE, then all conditions must have passed and the Validation Messages field is updated with “✅ Data Validated” and sets the Status Reason to Valid. 

Testing

Change the Validate Data field to Yes. The validation formula is triggered and any conditions that fail are shown in the Validation Messages field.