BookmarkSubscribeRSS Feed

Adding New Data Quality Rules in SAS Solution for IFRS 17

Started ‎12-16-2023 by
Modified ‎12-16-2023 by
Views 422

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.  


01_SunilB4SolutionRules-1.pngAn 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.


02_SunilB4EntityRuleSet.pngAn illustration of Entity rule set and its rules. 

 

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.


03_SunilB4Steps-1024x498.png Overview of steps to add a new data quality rule. 

 

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.


04_SunilB4RiskFactorRuleSet.pngAn illustration of Risk factor ruleset and its default rule. 

 

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. 

 
05_SolutionB4-Tabs-1024x205.pngAn illustration of important tabs in the rule set menu.

 

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.

Version history
Last update:
‎12-16-2023 08:28 AM
Updated by:
Contributors

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags