# Configuration Table
## Basic Concept
There are many configurations that may be associated with tables in insurance domain.
The most common scenario is to configure a rate table, but there may be other types of configurations that need to be performed in a table format.
Our configuration table is designed in such a way that any user can use this engine to configure his/her configuration table.
## User Scenario
For anyone who wants to use our platform to configure a dynamic logic table
## Training Videos
Here are the training videos for the configuration table.
## Composition of Configuration Table
### Table Usage
Normally, a configuration table can support a variety of business scenarios including:
- Rate Table
Rate table is the most common scenario, as each client will have different settings of rates in table format.
There can be many premium rates that are calculated by certain criteria. For example, the base rate for a vehicle can be determined by several vehicle factors, such as age or usage, while the accident premium can be determined by the age of different people and different products.
- Decision Table
Sometimes the lookup result is not a rate, but a decision result judged by business rules. For example, whether a policy requires manual underwriting based on certain criteria. By configuring such decision conditions and results, users can easily change the risk control method without changing the script every time.
- Mapping Table
There are a large number of mapping tasks that need to be performed during internal or external integration. It includes not only field-level mappings, but also mappings related to code tables.
The amount of mapping for such tables can be large and difficult to manage in a script. Configuring these tables will greatly help users to manage record changes in a user-friendly way.
### Table Structure
Basically, there are two column elements for a configuration table:
* **Condition**: it's the table search condition column against the business input value.
* **Result**: it's the final result value after search.
Take a simple "Age/Product -> Rate" table for example. After the table structure is configured properly, when users need the results, they can call respective lookup API to search by the age and product criteria against value passed in policy, then the lookup API will return the rates configured.
### Table Versioning
Table versioning is a very important feature especially for those premium rate related tables which usually keep constant update.
Now each table can have multiple table versions and there are key date fields in the table version level to facilitate application program to pick up the right version according to business requirement.
Take policy rate table for example, there can be multiple ways to judge the rate table version:
- Direct Field
In some simple scenarios that version judgement rule is straightforward, users can directly configure to take either proposal creation date or proposal effective date to compare and take the right rate version.
- Customized Field
In some complex scenarios that version judgment rule is complex, application team can create a new customized field such as version date at the policy level and provide the value.
- Manual Field
For some manual underwriting cases, application team can even expose version date field in proposal entry UI for user to manually input it or another version name/no code table to cascadingly judge the version date.
Please note that the version field judgement logic is achieved at rate table invoke side e.g. rating script or rule script or BFF service.
Also, table upgrade is limited to the value change only and table structural is not allowed to change. If there's a new version which requires table structure change, we would suggest user to create a new table with different name instead because it's more easier for table invoke side to manage such structural differences.
## UI Operation Guide
caution
* All operations in this text need to be performed in the portal master configuration environment (MC env). They are not allowed in other environments unless for troubleshooting purposes.
* Please make sure you have all read and write authority to make the operation beforehand.
### Table Definition
Click **Add** to add a configuration table.
* **Table Code**, unique code for a code table
* **Table Name**, business name to identify a code table
* **Quick configuration**, to identify whether table needs to be maintained in a customized UI for information purpose. Just a flag not in use current, please set it as **No**
* **Update Records Remotely**, allow to update records from client. Once it's marked as **Yes**, the system will only export table structure without any data inside. In other environments, it is not allowed to add version on UI
* **Context Type To Filter Column**, if set, system will auto add the column auto-populated in **Context Field To Filter Column**
* **Context Field To Filter Column**, auto populated after **Context Type To Filter Column** is chosen.
* **Description**, description for table
* **Is DB Table(V2)**, if yes, then it's in the V2 DB table mode, otherwise it is in V1 mode.(V1 is limited file size to 3M, V2 is limited to 50M by system default configuration.)
### Comparision Between Table V1 and V2
Starting from 24.01 version, we will encourage a new configuration table to run in V2 mode with better performance if table records is big. While for small table, V1 can be still the choice. User can refer to below table for the difference comparision between V1 and V2.
| Configuration Table | V1 - Default | V2 |
|---------------------|-----------------------------------------|------------------------------------------------------------------|
| Mode | Memory Table | DB Table |
| Performance | Good for small data，Medium for big data | Good for both small and big data |
| Others | Dynamic Lookup("$OTHER$") | [Static Lookup](#static-lookup-of-v21) ("$ELSE$") | | Range | Support | Support |
| All | Support("$ALL$") | Not Support |
| Array | Support | Not Support |
| Sensitive | Support | Not Support |
| Column Context | Support | Not Support (Planned in future) |
| Upload Max File Size| < 3M | No Limit |
| Upload Max Record Lines| < 10,000 | No Limit |
| Upload Duplicate Data Validation | Local Validation | Local for less than 100,000 records，Lambda for more than 100,000 records|
| Upload Compression | Required | Not Required |
| Use Big-bizasset to Deploy | Not Required | Not required for less than 50,000 records，Required for more than 50,000 records |
tip
Every condition else need to be in the condition you lookup.
If table columns are increased or decreased when compared to existing version then we need to delete the older version and add the new version.
$ELSE$ will be used instead of $OTHER$ in V2 table.
### Table Version Definition
Find this configuration table and then add a version.
tip
Table Version and records can be added/edited not only in the UI, but also in Excel and then uploaded.
To accelerate table version creation, users can download other tables and change the Name, Code, Version and start date to make a desired new table, then change the conditions/results and records and upload to the newly added rate sheet.
For any table which has the new version, system will not allow structure change after version upgrade.
Table Version:
* **Table version**, integer, auto generated by system
* **Table version start/end date**, in 'yyyy-MM-dd' format, to control the lookup versioning
* Table version comment
As in the image below, the column **ProductCode** that is auto populated for the **Context Type To Filter Column** is set.
Table Column:
* **Column Type**: Condition/Result.
* **Column Data Type**: See the reference tab for details of the data type; DATE format is 'yyyy/MM/dd', DATETIME format is 'yyyy/MM/dd HH:mm:ss'.
* **Scope Type**: Available only if the data type is 'Date', 'DateTime', 'Long' or 'Decimal'.
* **Approximate Match**: If an exact match record is not found in rate table, either the closest value is not reached or doesn't exist; Then only one condition can be defined as Sensitive. More details, see "interpolation" section in this document.
* **IsArray**: The condition can be set as an array, the values can be separated by comma (,). Example: `A,B,C`.
* **Sequence**: The sequence in which data is searched in the rate table. When a rate table has more than 1 condition, it is recommended to set the Sequence for all the condition fields, which will speed up the performance of the lookup. Also, in the rate table UI & export, the column display sequence will be dynamically adjusted.
### Version Data
After table column structure is defined, the next step is to put data into the table.
### Search Context Type
#### Table Context
Table context refers to those tables whose context is at the table level.
For example, let's assume there are two products, A and B. By use of table context, each product has a whole set of rate table and it means that product A has a set of tables and product B has a set of other tables. Mostly, it can be used for general insurance business scenario as the entire configuration structure can be totally different for one GI product and another.
| Table A - Product Context A | |
|-----------------------------|------|
| Sum Insured | Rate |
| Table B - Product Context B| |
|-----------------------------|------|
| Sum Insured | Rate |
#### Column Context
Column Context refers to those tables which belong to multiple contexts defined at each table record level.
For example, let's assume there are two products, A and B. If it's column context, there can be just one single table which has a product code column to indicate whether it's product A or B. Mostly, it can be used for life business scenario as most of life structure are same and product variance can be illustrated as the value differences.
| Table - Common Context| | |
|-----------------------| ------ |------|
| Product | Sum Insured| Rate |
| Product A | | |
| Product B | | |
#### Comparison
When searching, you can search by table context or column context. By a combination of both contexts, user can manage the whole attribute impact of each product.
There are two major differences between table context and column context:
1. Table Setup Consideration - Whether invoke program is written at a common level or product level
If we are talking about premium calculation like a basic vehicle rate or a basic home rate, usually, there will be separate piece of calculation logic for motor and home, and table structure can be totally different. Thus, they can be set up as tables with different table columns.
If we are talking about a common system logic like different product having different campaigns or discounts, usually, campaign/discount calculation logic is common for all the product lines. Thus, the setup of a common campaign table with a product column for context will be much easier to maintain rather than setting up each campaign table product by product.
2. Table Deployment Consideration - Whether deployment is conducted at table level or record level
Except table structure, another major differences are various deployment methods.
For table context, as long as the table is tagged to specific product, then it will be always deployed at the product package as a whole.
For column context, two deployment steps need to be performed: to deploy the table structure at a common level and to deploy the table row value by each product.
#### Deployment Consideration
Now the system supports to tag multiple context to one configuration table. This often happens for table context when one table is used for multiple products.
Although it provides some facility for maintenance, it is indeed a very risky move as the mentioned table will be exported and check-in all these product file. Thus, if there’s update on one table itself, users might only remember to check in specific products while not checking others, which might cause there are different version data in the git packages that are mistakenly deployed later on.
In order to reduce such deployment risk, our suggestion will be either:
* Change the table to common context and remove all product context binding.
* Change the table to column context to flag the product item by item level.
* If users choose to continue to use multiple table context, each time the table to be changed, users need to remember to re-check in all the products. We have specifically built mandatory duplicated check-in validation to avoid such discrepancy data.
### Keywords
There are some specific keywords which are allowed to be configured.
* **Number**: **MIN..MAX**, **0..100**, **80..MAX**
* **Other**: **$OTHER$**. It can work like $ALL$ if there is only $OTHER$ under the same condition. If there are too many conditions and records, to make the logic clearer, we can set $ALL$ to $OTHER$.
* **All**: **$ALL$**. Under the same condition, it takes $ALL$ first. So for all conditions, $ALL$ cannot be set in the table if there are other conditions.
### Scope/Range
When the Scope is set, value must be confined into the restricted format:
* **Number**: 0..100, MIN..MAX
* **Date**: 2020-01-01..2020-05-01
* **Date Time**: 2020-01-01T00:00:00..2020-01-01T23:59:59
#### Example 1 - Keywords and Range
As shown below,
* **Scope** is set for **DaysRange** and **AgeRange**.
* **SEQUENCE** is set to query **ProductId** first.
As shown below,
* $OTHER$ is set for **ProductId**.
* DaysRange is set as: 0..10, 0..365.
* AgeRange is set as: 0..MAX, 0..100.
#### Example 2 - Date and DateTime
As shown below,
* **EffDate** is set as **Date**.
* **MasterPolicyStartDate** and **MasterPolicyEndDate** are set as **DateTime**.
As shown below,
#### Example 3 - IsArray
As shown below,
* IsArray is set to be **YES**.
Data Example,
#### Example 4 - Interpolation
The property "**Approximate Match**" of a rating factor column of rate table is present as the **SENSITIVE** when it is downloaded as excel.
If the "**Approximate Match**" is set to be **Yes** on UI, in the excel, the **SENSITIVE** is **YES**. Then this factor and the returned rate work like a linear function.
As shown below,
* **SENSITIVE** of **Age** is set as **YES**.
As shown below,
* if `ProductID=373362647,CoverageCode=MTN,Age=10,SocialInsurance=Y`
then
`Rate= RateOfAgeMin + (Age*(RateOfAgeMax-RateOfAgeMin)/(AgeMax-AgeMin)) = 0.1+10*(1.1-0.1)/(100-0)=0.1+0.1=0.2`
* if `ProductID=373362647,CoverageCode=MTN,Age=40,SocialInsurance=Y`
then
`Rate= RateOfAgeMin + (Age*(RateOfAgeMax-RateOfAgeMin)/(AgeMax-AgeMin)) = 0.1+40*(1.1-0.1)/(100-0)=0.1+0.1=0.5`
* if `ProductID=373362647,CoverageCode=MTN,Age=60,SocialInsurance=Y`
then
`Rate= RateOfAgeMin + (Age*(RateOfAgeMax-RateOfAgeMin)/(AgeMax-AgeMin)) = 0.1+60*(1.1-0.1)/(100-0)=0.1+0.1=0.7`
Generally speaking, for example, if a rate table that the premium/rate is linearly increased by age,
* if 1 years old, the flat premium is 100.
* n years old, premium is n*100.
You can code the logic as DSL. Also, if a rate table is provided, you can just input 2 records with the min age and max age's premium, and then all the ages between the min and max can automatically get the flat premium by rating automatically.
* If the Age is out of the (0,100), an error will show up.
### Download and Upload Table
If the rate table records are quite a lot, you can try to download and edit the excel in local, and then upload it to the system.
There are two boxes showing on upload UI. One is for uploading and another one for compression.
* If the file size is smaller than 100K, the file will be uploaded directly. You can see the result on the current UI.
* If the file size is larger than 100K but smaller than 3M, the file will be uploaded asynchronously in background. After the uploading is done, you can find a new message with the uploading result in message center.
* If the file size is larger than 3M, upload will be blocked and compression is necessary. Please try rate table V2.
If file size > 3M, the uploading asynchronously will be done through batch, you can see the batch job status dialog as below. Enable auto refresh, you can see the status of uploading.
### Compress
If you find the data records are quite a lot, after checking, some conditions with the same results can be combined to be array. As for array details, see [array](#example-3---isarray1).
Before compression, please set the column's property "**IsArray**" of the condition to "**YES**".
If the rate table records are similar as below,
When you drop the rate table excel file to the compression box, after a few minutes, a compressed excel will be automatically downloaded.
tip
The rate table code in a file must be the same as the one you choose to upload.
The compressed file is named after "**rateTableCompression.xlsx**". The data combined is as below,
Although in the compressed excel, some String columns will be changed into Array, which actually will not persist. And users need to upload excel for persistence again.
If it's the first time of compression and table structure has not been changed, users might not be able to upload the compressed excel directly. Instead, users must go to table structure tab to change the Column to be Array first and also DSL to invoke the table, and then upload compressed excel.
### Update Records Remotely
Sometimes, there might be some tables that users want to directly maintain in production instead of in MC environment. If we interpret this requirement, it actually means two things:
1) MC definition is still required but will be structural definition only. We still need the configuration deployment process to deploy the table structure to all environments so that program can take and function it.
2) The data defined in MC need to be overridden in each runtime environment. To play things safely, even if there are some test data configured in MC, it should not be exported and override the configuration setting in the target environment.
For such cases, we have supported "Update Records Remotely". This is a flag upon any of the table creation. By default, it will be "N" which means that table structure and data need to be both maintained in MC environment. In such case, users need to click "**Export Release Data**" for deployment.
If users change it to "Y", it means that only table structure will be maintained in MC and deployed without any data, and users will find no data actually deployed in the runtime. In such case, users need to click "**Export Structure Package**" for deployment.
This might be a difficulty for initial production setup. Because if there's only structure to be deployed, then users need to manually input values in all environments. One possible solution is to initialize all table to "N". Then only after production launch, change it to "Y", which will again stop any new data override by mistake. Please pay special notice that if users want to carry out this "N" to "Y" process, users need to perform two operations to export both release data and structure package separately.
note
1. Please proceed carefully when changing the Update Records Remotely from N to Y. Once saved as Y, this field becomes read-only and can no longer be modified, the module and group cannot be changed either.
2. Although there's no hard constraint from system, we dont' suggest any user to upgrade any configuration table version in production. All version upgrade must be done in MC otherwise there will be rate table version error in production.
### Lookup
#### LookUp and LookUpForList
All ratetable search behavior is called rate table lookup. Let's assume a table with Age (Scope), Gender (Y/N) as condition and true/false as result:
* If we use LookUp, users must specify all Age & Gender as conditions and will only get one result.
* If we use LookUpForList, users can choose whether to specify Age or Gender as condition and will get multiple results.
caution
For V2 DB table, all the conditions are required for lookup. If some condition is not input, there will no result returned. This is different from V1 table. In v1 table, if there will be 1 record found by using the input conditons partially, the result will be returned.
More details, see [DSL Lookup Rate Table/Configuration Table](https://docs.insuremo.com/common/doc_00000019#rate-table--configuration-table).
#### Static Lookup of V2
The static lookup is totallly different from dynamic lookup. The dynamic lookup works just like the excel filter the conditions one by one as set sequence. But the static lookup works as filter as each column filtered seperately. The static lookup works as query data in DB,
```
Select * from xxx_table where conditionA=ValueA and conditionB = ValueB and conditionC = ValueC;
```
For example, if the rate table of V1 is as below, if we use condition (MakeId=27, ModelID=150, Segment=HATCHBACK), then system will return the result.
| MakeId | ModelID | Segment | Remark |
| ------ | ------ |------ |----- |
| 27 | 150 |$OTHER$ | Matched, then returned |
| 27 | 145 | HATCHBACK| |
If it is new created to be V2. The condition(MakeId=27) and condition(ModelID=150) will find the 1st record, but condition(Segment=HATCHBACK) will find the 2nd record.
And there is no record matches all the 3 conditions, the no result found will be return as warning.
| MakeId | ModelID | Segment |Remark |
| ------ | ------ |------ |----- |
| 27 | 150 |$ELSE$ | |
| 27 | 145 | HATCHBACK| |
### One Table One Page Display
If there are some tables we want business users to maintain and we do not want to provide them access to all the tables, we can configure them into separate menus.
Configuration Step:
1. Go to URP module and add a new menu permission.
2. Input entrance URL in the format of `$originalUrlPlaceholder$/ui/parameter/#/paramTablePage?tableCode=XXX`.
3. Add all API authority required for configuration table operation
Final Display:
note
For such tenants that if their intent is for business users to maintain in production environment instead of MC, please remember to flag "Update Records Remotely" to Yes so that deployment package contains configuration table structure only.
### Change History
Due to the large number of detailed records (some tables have a significant amount of data), we are streamlining the audit logs for batch operations (Excel uploads, batch APIs), recording only the basic information of the event once. For example, when uploading records of a table via Excel, only one audit log will be recorded to indicate that an Excel upload operation occurred, and the details of the changes will not be recorded.
For operations recorded individually, audit logs will be maintained (create/delete/update). For create/delete operations, all attributes of the information will be recorded. For update operations, only the attributes that have changed will be recorded, both before and after the change.
## Development Guide
### Methods to Invoke Configuration Table
#### Use Configuration DSL Lookup
For details, see [DSL Lookup Rate Table/Configuration Table](https://docs.insuremo.com/common/doc_00000019#rate-table--configuration-table).
#### Use Lookup Methods in Backend Development
Add the dependency
```java
com.ebao.vela
vela-ratetable-runtime
${vela.version}
compile
```
Sample:
```java
@Autowired
private RateTableRuntimeService rateTableRuntimeService;
// If you don't have special logic for version date and release, just use local datetime
LocalDateTime versionDate = DateContext.getLocalDateTimeForCurrentUser();
LocalDateTime releaseDate = DateContext.getLocalDateTimeForCurrentUser();
List> lookup(String rateTableCode, Map params, LocalDateTime versionDate, LocalDateTime releaseDate);
```
### Use Configuration Table API to Develop Your Own Dynamic Table
For complex table configuration user experience which exceed the capabilities of existing configuration table UI, you can choose to develop you own UI by utilizing our configuration table management API.
#### Define the Configuration Table Structure
The custom table is implemented by the **Configuration Table** solution. The table structure is defined in the configuration table UI and can be uploaded through the online editor or Excel.
Usually, such extension happens on production which means business user want to directly change the table value in production instead of changing in MC and go through deployment. If this is the case, please remember to flag **Update Records Remotely** to Yes and deploy table structure to respective environments first.
#### Maintain Configuration Data
Development team have two options for user to maintain configuration data:
* Upload via Configuration Table Online Editor or Excel
This approach can satisfy most requirements.
* Develop Custom UI
If you have special requirements for maintaining configuration data, you can develop a custom UI to maintain the configuration data and call the RESTful API of the data sheet to read or save the data.
For both approach, users can refer to [Table Management API Documentation](https://docs.insuremo.com/tech/pro/sdk/api/tablesmgmt) for choose the API to call.
For saving the record, user can call:
* Update a single record **/ratetable/public/ratetable/v1/rateTableData/update**
Request Body
```
{
"DataId": "",
"VersionId": 10000077556152,
"Conditions": {
"Con1": 77
},
"Results": {
"Res1": "77"
}
}
```
Response Body
```
{
"@pk": 10000079730106,
"Conditions": {
"Con1": "77"
},
"DataId": 10000079730106,
"RecordUsage": 5,
"Results": {
"Res1": "77"
},
"VersionId": 10000077556152
}
```
* Update multiple records **/ratetable/public/ratetable/v1/rateTableData/saveInBatch?versionId=10000077556152**
We recommend use saveInBatch when you have 100 or more data, it reduces round-trips and improves overall efficiency.
To use the lookup, you must clear the cache first; otherwise, you’ll still read the old values. After saveInBatch, simply call:
`GET {{server}}/api/platform/common/public/cache/v1/clear/bucket?proxyTo=platform-busi-config&bucketCode=RATETABLE`
Request Body
```
[
{
"Conditions": {
"Con1": "55"
},
"RecordUsage": 5,
"Results": {
"Res1": "55"
},
"VersionId": 10000077556152
},
{
"Conditions": {
"Con1": "66"
},
"RecordUsage": 5,
"Results": {
"Res1": "77"
},
"VersionId": 10000077556152
}
]
```
Response Body
```
[
{
"@pk": 10000079732064,
"Conditions": {
"Con1": "55"
},
"DataId": 10000079732064,
"RecordUsage": 5,
"Results": {
"Res1": "55"
},
"VersionId": 10000077556152
},
{
"@pk": 10000079732065,
"Conditions": {
"Con1": "66"
},
"DataId": 10000079732065,
"RecordUsage": 5,
"Results": {
"Res1": "77"
},
"VersionId": 10000077556152
}
]
```
## FAQ
### 1. Manually choose rate table version?
**Question:**
Suppose my business user want to choose the rate table version, can I do it?
**Answer:**
We don’t suggest business users to directly choose the rate table version no considering there can be multiple rate tables for one product and it’s impossible to list all rate table versions in the proposal entry UI.
Instead, we suggest adding some intermediary fields at the policy level to link them. For example, you can create one or two fields at the policy level to indicate a version. At the backend, they will still be transformed into a date format for the rate table to obtain the correct version.
| Policy Level | | Rate Table Definition to be Against Rate Version Date at Policy Level | |
|-----------------|-----------------------------------------------------------------------|--------------|--------------|
| Rate Version No | Rate Version Date | Rate Table 1 | Rate Table 2 |
| 1 | 2023-01-01 | Version 1 | Version 1 |
| 2 | 2023-02-01 | Version 2 | |
| 3 | 2023-03-01 | | Version 2 |
| 4 | 2023-04-01 | | Version 3 |
| 5 | 2023-05-01 | Version 3 | |
About how to set “Against Rate Version Date at Policy Level”, you can do it in calculation script which invokes rate tables.
### 2. Records over 30 million rows
**Question:**
If I have a rate table with more than 30,000,000 rows of data, how to configure?
**Answer:**
For such tables with more than 1 million records, it will face performance issue during deployment later on, thus we need to apply some special treatment on it.
Basically, user need to follow below steps to narrow the table records:
1. Make sure you are adopting rate table V2 instead of V1
2. Split the big table into multiple smaller tables by key conditions e.g. region or province column in the table
3. Apply manual check-in instead of auto check-in for GIT repository
4. Apply independent GIT repo & CICD for deployment
To understand more on point 2 & 3, user can refer to [how to deploy large table guide](https://docs.insuremo.com/common/doc_00000016#if-i-have-large-table-with-a-lot-of-data-how-to-check-in1) for more details.
### 3. Runtime data be automatically cleared?
**Question:**
For tables updated remotely, after a table-structure change is published, can the runtime data be automatically cleared?
**Answer:**
It will not be automatically cleared to avoid irreversible data loss in production. When Bizdata is published, dataio sends an event to EventBridge. Tenants can subscribe to this event, assess structural compatibility, and decide whether to clear/update the data themselves.
Optional approaches:
1) After production deployment, manually clear the Configuration Table once via the UI, then let the core system synchronize.
2) Modify the core system’s push message to call the GIMO API to wipe data during a full sync before re-inserting. Since the core supports incremental sync, each record must return a sync result. Full-sync traversal via GIMO API updates is required, which poses performance issues.
3) Create a new Configuration Table for each production release. Update code in each release to insert/read data from the new table. Long-term maintenance cost is high.
### 4. Return the failed records?
**Question:**
Does the GIMO API support returning the failed records after a batch update so we can report the results back to the core?
**Answer:**
Batch updates follow an “all-or-nothing” pattern; any failure triggers a full rollback, and partial success is not supported.