Adding New Data Quality Rules in SAS Solution for IFRS 17
Introduction
SAS Solution for IFRS 17 uses pre-configured data quality rules to assure that the right data is utilized by the solution. These quality rules are applied at the initial stages in the standard workflow immediately after the data is loaded into the solution. This helps to reduce any calculation issues in the downstream processes. The solution also enables data administrators or implementers to edit existing rules and add new rules based on the specific requirements. The objective of the article is to provide developers, data administrators or implementers an overview of steps to add and test new data quality rules. The article assumes that you have a basic knowledge of SAS Solution for IFRS 17 on SAS Risk Stratum and you know basic skills to update or modify SAS tables.
|
You can obtain more information on SAS Risk Stratum and SAS Solution for IFRS 17 through the following training programs.
Data Preparation, Configuration, and Administration in SAS Risk Stratum
Technical Overview of SAS Solution for IFRS 17 in SAS Risk Stratum |
A Brief Idea of Data Quality Rules
The solution applies the data quality rules to the incoming data during the Run Data Quality subtask under the main Data Processing task in the workflow for IFRS 17 process. Note that the IFRS 17 standard for insurance contracts do not provide specific rules for data quality. SAS provides pre-configured rules (that are editable) based on the best practices in several insurance and non-insurance industries. The data quality rules in the solution are based on the principles of accuracy, integrity, and completeness. The solution hence provides flexibility to modify existing rules and add more rules according to your requirements. Here is a snapshot of types of rules that SAS applies to ensure data quality.
| Data Quality Rule Type | Description | Example |
| NUMERIC | Verifies whether the numeric values have been provided | The values of the variable CASHFLOW_AMT cannot be missing. |
| CHARACTER | Verifies whether appropriate character values have been provided | If a value for the variable CURRENCY_CD is supplied, then it must be a two-character string. |
| DATE | Verifies whether the necessary dates values have been provided and the relationship between such dates has been respected | Any value of the variable BEGIN_COV_DT for an insurance contract cannot be missing and must be less than the value of END_DT. |
| STRING_LIMITED_CHOICES | Verifies whether provided values fall within the list of acceptable or required values | The values of the variable CEDED_FLG must be ‘Y’ or ‘N’. |
All the existing rulesets can be accessed from the Rule Sets workspace available in the main menu of the solution. Following is an illustration of Rule Sets workspace.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
Rules are organized into rule sets based on the underlying solution data model and corresponding data definitions or tables. For example, rules for all the market data (information related to risk factors, risk factor curves and quotes) are organized into four rule sets. In the solution, each rule set instance of a rule set object is associated with input data (for example, INSURANCE_CONTRACT_PORTFOLIO for insurance contract portfolio data) and each rule is associated with a column in that specific table. The following is a list of the solution data model's input tables for which the rules are configured.
| INSURANCE_CONTRACT_GROUP | INSURANCE_CONTRACT_GROUP_ASSOC |
| INSURANCE_CONTRACT_GROUP_DETL | INSURANCE_CONTRACT_PORTFOLIO |
| ENTITY | INSURANCE_CONTRACT_GROUP_X_CURVE |
| QUOTES | INSURANCE_RISK_DRIVER |
| ALLOCATION_DIMENSION | INSURANCE_CASHFLOW |
| ALLOCATION_DRIVER | INSURANCE_CONTRACT_GROUP_PVCF |
| CALC_INPUT_MEASURE | INSURANCE_CONTRACT_GROUP_PVCF_DT |
| CSM_RUNOFF_SCHEDULE | INSURANCE_CSV |
| FINANCIAL_DISCLOSURE_INPUT | JOURNAL_ADJUSTMENT |
| GL_ACCOUNT | LIAB_RUNOFF_SCHEDULE |
| GL_ACCOUNT_BALANCE_SEGMENT | RISK_FACTOR |
| GL_ACCOUNT_BALANCE_SEGMENT_AGG | RISK_FACTOR_CURVE |
| INSURANCE_AOC | RISK_FACTOR_X_RISK_FCTR_CURVE |
| INSURANCE_AOC_CALC | RUN_DETAIL |
| INSURANCE_CASHFLOW_AGG | RUN_UNIVERSE |
| INSURANCE_CONTRACT_GROUP_ALLOC | SLAM_OPEN_SUBLEDGER_BALANCE |
For more information on the data model and data definitions you can follow the Technical Overview of SAS® Solution for IFRS 17 in SAS® Risk Stratum training program.
On the Rule Sets workspace you can click a specific ruleset to see the details of all the rules configured in the ruleset. Following is an illustration of all the rules configured for the ENTITY table.
Two methods of adding new rules
- Manually using the user interface
- Bulk Loading rules using an excel loader
Most of the customizations to the rules can be carried using the interface. If you have a few rules to be configured then you can directly add new rules in a rule set or modify existing rules directly using the interface. When dealing with large number of rules , you can first exporting rules to an Excel file, modify it or add all your rules, and then import it back . If you are adding any new rules, you must update the configuration table MAP_RULE_X_DATADEF data by making the new entries of the data definitions or tables and the new rules.
Following diagram illustrates these steps.
1. Choose
At this stage you need to know your input table and a column in the table for which you want to configure the new rule. The need to add a rule is specific to your site. The current state of data quality of the source systems and the underlying ETL (Extract Transform and Load) process may demand more rules to be configured for certain input tables. You can navigate the entire list of available rules from the rule set workspace or the Data Quality section of the help documentation available in the Home workspace of the solution.
As an example, we will choose Risk factor rule set for the table RISK_FACTOR and we will add or set a new rule for the column MATURITY_TIME_UOM_CD. The rule will check for a valid list of values, which are MONTH, YEAR, and SPOT.
2. Access
You can access all the available rules sets from the Rule Sets workspace. You can search for a specific rule set and then click to open the rule set. Note that in order to modify or add a new rule you must have administrator access. Following is an illustration of the default Risk Factor Rule set which has only one rule configured.
3. Add
To add your new rule set from the UI, you must click Add a row in the menu available at the top right corner of the rule set table. You must add all the relevant details like primary key, rule id, rule name and so on. In our example, the following data must be entered. Note that the comments column in the table below is only for your information. It is not meant for data entry.
| Column in the Rule definition | Data to be Entered | Comments |
| Primary Key | RISK_FACTOR_ID | Copy from the first row. This key is same for the rules for a given data definition or a table. |
| Rule Id | RISK_FACTOR_RULE_02 (ifrs17.2021.10) | Rule 2, it differentiates it from other rules. |
| Rule Name | Check valid values of MATURITY_TIME_UOM_CD (ifrs17.2021.10) | The name reflects the name and basic logic of the rule. |
| Rule Description | Check Missing Risk Factor ID (ifrs17.2021.10) | Same as Rule Name. |
| Rule Component | Condition | Use the drop-down menu to select value for the rule component. The other option is Action. |
| Operator | Leave blank. Not required. | |
| Parenthesis | Leave blank. Not required. | |
| Column Name | MATURITY_TIME_UOM_CD | Use the drop-down menu |
| Rule Type | Not in a list of values | Use the drop-down menu to select values for rule type. |
| Rule Details | "Month","Year","Spot" | Provide a list of valid values. |
| Message | MATURITY_TIME_UOM_CODE must be set to "Month","Year", or "Spot". | Provide a message for display in reports. |
| Aggregation Reference Name | Leave blank. Not required. | |
| Aggregation Expression | Leave blank. Not required. | |
| Group-By Variables | Leave blank. Not required. | |
| Aggregated Rule Flag | Leave blank. Not required. | |
| Rule Reporting Level 1 | Completeness | Copy or Type. Useful in report filters. |
| Rule Reporting Level 2 | Instrument Features | Copy or Type. Useful in report filters. |
| Rule Reporting Level 3 | Not In List | Copy or Type. Useful in report filters. |
| Rule Weight | 1 | Treat rule with equal importance to other rules. The value presently is 1 for all rules. |
The other way is to click Export tab and save the resultant excel file. The entries described in the table can be added to the excel. To bring back the data entered in the excel, you need to import the file into the solution from the same page by clicking the Import tab. The following diagram indicates the import and export tabs available in the rule set menu for managing rules.
4. Save
You must save the newly added rule either by importing from the excel file or manually adding it directly to the UI. The save button is available at the top right of the screen.
5. Configure
You must add the information regarding the new rule to the configuration table MAP_RULE_X_DATADEF. The table is available in the federated area. The location is known to your system or solution administrator. An example of a location is:
D:\SAS\Config|Lev1\AppData\SASIRM\fa.ifrs17.10.2021\irm\landing_area\base\ifrs17.v10.2021\mapping
You can edit the table and manually add the row to the SAS table using SAS 9 or you may prefer to write a SAS SQL script to update the table with a new row. In our case, following values must be added as a row must be added to the table. Note that the comments column in the table below is only for your information. It is not meant for data entry.
| Column Name | Value | Comments |
| LIBRARY_NM | ic_stg | Staging library name |
| TABLE_NM | risk_factor | Name of the table for which the rule is added |
| DATA_DEFINITION_KEY | 10032 | Data definition key available in UI |
| RULE_SET_KEY | 10026 | Rule set key available in UI |
| CONFIG_CHANGE_DT | Leave blank | |
| CONFIG_CHANGE_TYPE_CD | Leave blank | |
| CONFIG_CHANGE_USER_ID | Leave blank | |
| CONFIG_CHANGE_COMMENT_TXT | Leave blank |
Post Activities
The five steps described above adds the rule to the solution. The new rule is available to be used in any new cycle you create for testing or direct usage. It is recommended to test the rule by creating a new cycle on a test data. You may enter known violation or test cases in the relevant table. The rule should reflect in the data quality report and must point out all the data error in the testing data. Typical data quality errors are presented in the Data Quality Details section in the Data Quality report.
Conclusion
The article presents a series of steps to add a new data quality rule to the SAS solution for IFRS 17. These steps enable you to try out and test several new rules and any customizations to the existing rules done based on your requirement. Such steps might be beneficial in the development and implementation stages of SAS Solution for IFRS 17. For a working demo of above steps refer to the training program Technical Overview of SAS Solution for IFRS 17.
Find more articles from SAS Global Enablement and Learning here.
Special offer for SAS Communities members
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
