# Data Migration Engine
## Where to Use
DMEngine is a tool used to manage transformation script instead of manually writing. When you are struggling to manually manage the script and find it hard to maintain document against script, it is the time to use this. 

Besides, DMEngine integrates all the migration steps from data file loading to target database uploading, including reconciliation report.

## Who is the Potential Audience
* Migration team members.
* Any developer who is going to do data transfer from one system to another system with transfer logic.
## How to Start
### Apply to insureMO Portal  

1. Log in to [insureMO Portal](https://visitor.insuremo.com/#/home) with your personal account.  

<div class="docs-tip"><span class="docs-admonitions-text">tip</span>  

If you do not have a personal account, click **Sign up** to register one. Then log in to the portal.  

</div>  

2. Click **Catalog** at the top of the home page.  

3. In the left panel of the **Catalog** page, navigate to **Non Insurance Services** > **Data Consolidation and Utilization**.  

4. Find the **Data Migration Engine** card and click **Apply** to apply for authority.  

5. In the left panel of **Data Migration Engine page**, click **Profile Setup**. Create a profile space to manage your own profiles with password.  

 
   ![Create Profile Space](./image/data_migration_engine/0.0_DMEngine_profile_space_create.png)

   a. Key in any profile name after **Auth** succeeds. 

   b. Default system will generate a set of sample configuration for new profile and can run directly.

Now, you can explore the migration world.

## Concept or Term Clarification

### Profile

It's a series of configuration data to be used during migration phases, including template, mapping, sequence, report, etc.

Profile name should be unique. If it's used by other profile space, system will raise errors.

### Initial Data

This refers to all kinds of statistical data to be used during migration process, such as product configuration data, reference data, special code table.

### Template

It's a standard format to define the table structure in Excel format.

### Mapping Scripts

As key migration script configuration in CVS format, there are 5 files in total to configure the mapping script. DMEngine will generate migration script based on configured files.

### Stage

As the area to keep the process and target data during migration process, it's a memory DB.

### Code Table

It will list values to be referred from template or mapping rule.

### Report or Verification Abbreviation

| Abbreviation | Name                        | Description                                                                                       |
|--------------|-----------------------------|---------------------------------------------------------------------------------------------------|
| DVR          | Data Verification Rules     | Driven by template definition.                                                                    |
| BVR          | Business Verification Rules | Driven by BVR configuration and maintained manually.                                              |
| MVR          | Mapping Verification Report | Verifies that mapping script matches the expected mapping rules.                                  |
| TRR          | Technical Reconciliation Report | Verifies whether the migration tables count matches the expected count or not.                   |
| BRR          | Business Reconciliation Report | Verifies the key values between source and target from business users' points of view.           |
| PRR          | Premium Recalculation Report | Compares migration premium values against system API recalculated values for Gemini APP. Also, it ensures the alignment between product configuration and migration data. |


## High Level Process
Here is the migration high level process.

![Migration Process](./image/data_migration_engine/0.0_DMEngine_Process.png)
### Step 1: Configure Preparation
Download the sample document.

![ConfigPrepare](./image/data_migration_engine/1.1_Configure_Prepare_1.png)

Configure and update based on sample format.

![ConfigSample](./image/data_migration_engine/1.1_Configure_Prepare_2.png)
### Step 2: Configure Data Clarification and Upload
1. Initial data

   This needs to be uploaded to stage and can support CSV files or ZIP files which contain CVS files. The table name will be the same as CVS file name without extension.

   **Delete Existing Files?** will clear all the uploaded data files.

   ![Initial Data](./image/data_migration_engine/2.1_Initial_Data_1.png)

2. Mapping template
   
   The templates will be processed during migration process, which supports multiple templates uploading. The table will be created based on template definition. 

   You can search the result after running verification or all steps.  

   ![Template](./image/data_migration_engine/2.2_Template_1.png)
   
   See the template format sample document for detailed format.

   ![TemplateFormat](./image/data_migration_engine/2.2_Template_2.png)


   How to generate template format data from database?

   - Key in the DB conn str to extract data.

   - Key in the tables that will be extracted.

   - Click **Download Template**.
    
   Then the template format file can be generated automatically.

   ![TemplateGenerate](./image/data_migration_engine/2.2_Template_3.png)

 
3. Data files

   There are two options to upload data files (`.csv`) to stage, and the data file will be uploaded based on template definition fields:
  
   - Upload from local computer.
  
     Upload ZIP or CVS files.

     Data cut-over date is the deadline for providing data.
    
     Support incremental upload (don't click **Del Existing File**).
    
     `Generate Empty Data File` is used for fine-tuning mapping script without actual data uploaded.

       ![DataFile](./image/data_migration_engine/2.3_DataFile_1.png)

   - Download from **AWS S3** bucket by **S3** key or **AssumeRole**. The options are the same as loading from local files.
  
     ![DataFileS3](./image/data_migration_engine/2.3_DataFile_2.png)
  
     Create data files from database/stage.
    
        a. Generate the data file based on the specified tables.

        b. Download the data file or directly upload it to server.
   
       ![DataFileDB](./image/data_migration_engine/2.3_DataFile_3.png)

4. Mapping script
  
   Upload 5 mapping script configuration files from local.
  
   ![MappingScript](./image/data_migration_engine/2.4_mappingScript_1.png)
 
   
   Upload options:
     
    - **Validate?**
    
      Check the mapping script files using DVR rules.
   
    - **Auto Generate Log and CX Mapping Rule**
      
      For Gemini log table to generate mapping rules by configuration files.
      
      Upload configuration files.

      ![MappingScriptCX](./image/data_migration_engine/2.4_mappingScript_2.png)


    Generate the auto mapping configuration files from tables (for simple and direct mapping).
    
    Tools will generate mapping based on extracted table name, and the column name will be the same.
    
    ![MappingScriptAuto](./image/data_migration_engine/2.4_mappingScript_3.png)

5. BVR configuration
 
   BVR initialization
  
   - For BVR, if `In Project= P`, you need to do initialization for one time to decide which to use in this project at beginning.

   BVR priorities
   * There are 5 priority codes: `C - Critical, H - High, M - Medium, L - Low, W - Warning`.

   * `C - mandatory` to fix before transformation.
  
   * `H - mandatory` to fix before functional testing.

   * `M, L, W -` to be fixed or aligned with solutions before UAT (User Acceptance Testing).
 
   BVR maintenance
   * BVR should be updated based on customization function.

   BVR verification
 
   * BVR verifies data from two phases: Pre-Stage and Stage.

     ![BVRUpload](./image/data_migration_engine/2.5_BVR_1.png)
 
   BVR configuration format

   ![BVRFormat](./image/data_migration_engine/2.5_BVR_2.png)
   - Pre-Stage
     
     Verifies template tables with BVR rules, ensuring the data quality before loading transfer.

   - Stage
    
     Verifies target tables with BVR rules, ensuring the data quality with target data business logic.

6. Target sequence configuration
  
   Configure the sequence column for the primary key in target table:

    ![SequenceUpload](./image/data_migration_engine/2.6_SequenceCfg_1.png)

   Configuration format:

    - Download the sample to add new one.
    
    - See the screenshot.

      ![SequenceFromat](./image/data_migration_engine/2.6_SequenceCfg_2.png)

   Configuration options:
   
    * **Sync Start Value**
   
      When you sync Gemini sequence value and migration data, it will start from the next value. Besides, the configuration start value will be replaced from the database.
   
    * Fix sequences
    
      For some sequence columns, such as `policy_id`, `item_id`, they can be fixed during different round migration. So, this will make it easier to follow PRR related issues. See the following:
      - Set `fix_seq_indi` to **Y** in the sequence. This step will fix the sequence.
     
      - Do not click **Sync Start Value** if the sequence is fixed. It may be invalid because the sequence can possibly exceed the allowed range.
     
      - The allowed range specifies that the change in the starting value should not exceed 50,000.
7. Code table

   Upload the code table value to verify the foreign key in DVR code table.

    ![CodeValueUpload](./image/data_migration_engine/2.7_CodeTable_1.png)

   See the sample document for details about code table format:

    ![CodeValueFormat](./image/data_migration_engine/2.7_CodeTable_2.png)

   For code value maintenance, extract from database based on the list.

     ![CodeValueMaintain](./image/data_migration_engine/2.7_CodeTable_3.png)

      Extract options:

      - **Copy If No Table**
       
        This will copy from existing value if code tables are not in the code table list.

      - **Copy If Error**

        This will copy from existing value if extract error occurs.
 
      - **Initial Version?**
      
        Check if this is the initial version.

8. Report configuration
  
   Upload TRR and MVR configuration.
  
    ![ReportUpload](./image/data_migration_engine/2.8_Report_1.png)
  
   MVR 

    Configure expectation mapping of table level and column level.

    * Table level 
    
      ![ReportMVRTable](./image/data_migration_engine/2.8_Report_2.png)

    * Column level 
    
      ![ReportMVRColumn](./image/data_migration_engine/2.8_Report_3.png)
  
   TRR
  
    Reconcile table level count, comparing actual count with expected results.
  
    ![ReportTRR](./image/data_migration_engine/2.8_Report_4.png)
  
   BRR

   See the BRR format

      ![ReportBRR](./image/data_migration_engine/2.8_Report_5.png)

     BRR source data will follow a data file named `dm_brr_report.csv` to be uploaded from legacy system.

### Step 3: Verification
To verify the data quality of template data file, there are two types of verification: DVR and BVR.

After uploading templates configuration and BVR configuration, click **Run** under the **Stage Verification** and then download the report.

![ValidationStage](./image/data_migration_engine/3.1_validation_1.png)

- DVR

  Before data loading, passing DVR is mandatory, except for `VERI_MIN_MAX_DATE`, which is for warning only and needs to be covered in the mapping.

  The following are 9 categories of DVR to verify.

  ![ValidationDVR](./image/data_migration_engine/3.1_validation_DVR_2.png)

  See the DVR report.

  ![ValidationDVRReport](./image/data_migration_engine/3.1_validation_DVR_3.png)

 
- BVR

   For BVR report, filter the BVR.
   

   `In_Project='Y'`.
   
   `SQL_FOR_PRE_STAGE` is not `null`.
   
  Values which are not equal to 0 under `SQL_RESULT_FOR_PRE_STAGE` are wrong BVR.

   ![ValidationBVRReport](./image/data_migration_engine/3.1_validation_BVR_4.png)



### Step 4: Run for All

1. Run all the transfer steps, from data loading, verification, transfer to report.

2. The target table data is ready after execution.

3. Reports will be ready to download after the execution succeeds.

   ![RunForAll](./image/data_migration_engine/4.1_RunForAll_1.png)
    
   See the Reports for DVR, BVR, MVR and TRR.

   ![RunForAllReport](./image/data_migration_engine/4.1_RunForAll_2.png)

 4. Click **Status** to check running status. If you don't run anything, the last running status will be listed.

    ![RunForAllStatus](./image/data_migration_engine/4.1_RunForAll_3.png)

### Step 5: Loading
- Target table loading

  Load the specified tables (`target, stage`) into target database (`Oracle/MySQL`).
  
   ![LoadingRun](./image/data_migration_engine/5.1_loading_1.png)
   - DVR check
   
     Verify target table data with DVR before loading to ensure consistency in definitions.

   - Post loading

     Analyze the database (`Oracle`) after data loading.
    
     Sync the sequence after data loading based on migration data volume.

- API call (`proposal`)

  Some data cannot be directly loaded into the target table and will instead be loaded via an API.

  ![LoadingProposal](./image/data_migration_engine/5.1_loading_2.png)

  See the following steps:

   1. Generate JSON parameters from migration table.
  
   2. Configure JSON relationship configuration table.
  
   3. Call API to transmit JSON data to the specified API.

### Step 6: Reconciliation
- BRR report
 
  ![ReportBRRRun](./image/data_migration_engine/6.1_RunForAll_1.png)

  BRR for template to target

  ![ReportBRRLegacyTarget](./image/data_migration_engine/6.1_RunForAll_2.png)

  BRR for source to target

  ![ReportBRRLegacyTarget](./image/data_migration_engine/6.1_RunForAll_2_2.png)

  Report sample

  ![ReportBRRSample](./image/data_migration_engine/6.1_RunForAll_3.png)
 
- PRR report

  Pre-condition

  - Migration data has been loaded into Gemini .

  - Product configuration has been deployed the latest version.

  Purpose of PRR

  - To verify premium migrated value against system recalculated value.

  - To conduct a policy-level comparison.

  - To ensure that product configuration and policy premium are aligned.

    ![ReportPRR](./image/data_migration_engine/6.1_RunForAll_PRR_1.png)

   Running options of PRR

  - Fully running

    Extract all the records to `dc_contract_product` table based on **Extract Recalc Data SQL**.
   
    ![ReportPRRAll](./image/data_migration_engine/6.1_RunForAll_PRR_3.png)

   - Partial running

     For failed cases running PRR, you need to update that `processed='N'`, `errorm_msg=''` for specified conditions. See the following:

     ![ReportPRRPartial](./image/data_migration_engine/6.1_RunForAll_PRR_4.png)

   - Next due 

     By default, calculate the next due premium to compare it with the migration premium.
 
     ![ReportPRRDueOption](./image/data_migration_engine/6.1_RunForAll_PRR_5.png)

  Following up on PRR issues
  
  1. Pre-condition.

     Sequences of `Item_id` and `policy_id` should be set to `fix_seq='Y'`.
    
     Do not click **Sync Start Value?** when you re-upload sequence configuration file.
  
     ![ReportPRRSyncOption](./image/data_migration_engine/6.1_RunForAll_PRR_6.png)

   2. Fix sequences.
 
      Ensure that the sequences of `policy_id` and `item_id` are fixed for each migration.

   3.  Upload failed reasons. Only 4 columns can be used.

       ![ReportPRRFailReason](./image/data_migration_engine/6.1_RunForAll_PRR_7.png)

   4. Analyze and update the reasons based on system's PRR report for the first 4 columns.

   5. Upload the file before new round PRR report is generated.

      ![ReportPRRFailReasonUpload](./image/data_migration_engine/6.1_RunForAll_PRR_8.png)

   6. New report will include the updated reasons to continue analyze.

## Data Query
See the following for stage query.

![StageQuery](./image/data_migration_engine/7.1_query_1.png)

The following pull-down list shows 4 different stage DBs which can be queried.

![StageQUeryDBList](./image/data_migration_engine/7.1_query_2.png)

Stage data range:

 * All the template tables.
 * Target tables if you execute **Run for All**.

 Premium recalculation DB:

 * The latest recalculation results.

 * Historical recalculation results.

 * Recalculation failed reasons.
## Debug
- Check Syntax.

  ![DebugStatus](./image/data_migration_engine/8.1_debug_1.png)

  Use `empty` to verify transfer script syntax.

  Execute **Run for All** to check the script running status.

- Check running results.

  There are 4 ways to check the running status. See the following navigation bar.

  ![DebugSummary](./image/data_migration_engine/8.1_debug_2.png)

  * ETL tasks errors summary
  
    Show ETL errors and their information.

    ![DebugSummaryError](./image/data_migration_engine/8.1_debug_3.png)

  - ETL status detail log

    Show each ETL running status.

    ![DebugSummaryErrorDetail](./image/data_migration_engine/8.1_debug_4.png)

   - ETL status debug log

     Show debug information to check for detailed error information.

     ![DebugSummaryErrorDebugLog](./image/data_migration_engine/8.1_debug_5.png)

   - ETL running status with dependency diagram
     
     Show the running ETL dependency diagram and status, to check which is the blocked ETL script.

     ![DebugSummaryETLMonitor](./image/data_migration_engine/8.1_debug_6.png)

## Admin Features
To manage exception issues, you need to configure relevant info and address data security concerns.

![AdminSummary](./image/data_migration_engine/9.1_Admin_TaskSummary_1.png)

**Long Task Clear**

 Clear some long-run tasks from backend if they are not closed abnormally. You can create a new long run task after clear.

 Clear specified tasks or all tasks.

   ![AdminSummaryLongTask](./image/data_migration_engine/9.1_Admin_LongTask_2.png)

This function will only clear the task-control session itself, instead of the actual running item.
 
   1. End the actual task with task execute function.

   2. After clear, the task running status will be updated to **close**.

   3. Ensure that you do not close running tasks. Run the closed one once again. This may cause an unexpected result, since the actual backend tasks are still running.

**S3 Config**

This will support storing profile-related configuration files, data files and system logs in an S3 bucket. By default, they will be stored in the DMEngine local server.

![AdminS3Config](./image/data_migration_engine/9.1_Admin_S3config_1.png)

There are 3 options available:
 
- MinIO - local S3.

- AWS-public cloud AWS S3 by key access.

- AWS-public cloud AWS S3 by assuming role.

  <div class="docs-note"><span class="docs-admonitions-text">note</span>

  AWS S3 is NOT suggested because it may cost additional fees.

  </div>

**Admin PWD**

This password is used for current profiles only. It is designed for some sensitive processes to ensure that only profile owners have access to the Admin.

 ![AdminPWD](./image/data_migration_engine/9.1_Admin_AdminPWD_1.png)

**Stage Data Clear**

To clear the updated data and stage related DB files, you need to key in the profile's password.

See the following clear options:

   * Stage DB only.

     Clear the uploaded data file and stage DB (only stage DB will be cleared if you click **Stage DB Only?**). 

   * DB upgrade.

     Clear all stage-related data in the database due to the upgrade of the APP's memory database, including stages, tasks, recalculations, and more.

**Log Clear**

Clear profile related backend log. You can also back up and download the log files. 

   ![AdminLogClear](./image/data_migration_engine/9.1_Admin_LogClear_1.png)
 
**Run Param**

Show the backend log level.

   ![AdminRunParam](./image/data_migration_engine/9.1_Admin_RunParam_1.png)

**Fix Sequence**

<div class="docs-note"><span class="docs-admonitions-text">note</span> 

Do not use this function unless you completely understand its operation.

This operation is based on the DMEngine server level, not on the profile level. So, you will need the DMEngine Admin password. Please check with DMEngine Vendor.

</div>

![AdminFixSequence](./image/data_migration_engine/9.1_Admin_FixSequence_1.png)

  * **Backup**
   
    Back up the fixed sequence values for all profiles.

  * Restore

    * You may key in the DMEngine Admin password.

    * It will remove the existing data.

    * Replace the previous data with the uploaded data.

## Mapping Script Syntax
Mapping rule script syntax

Default to use DuckDB syntax. See [SQL introduction](https://duckdb.org/docs/sql/introduction).

Customize Keywords.

 * nvl (same as coalesce)
  
   If it's null, set to a specified default value.

 * autoset 
 
   Set the sequence value using target table sequence configuration.

 * rownum
 
   Use the auto-increment sequence.

 * `map_code_table`
 
   Use code-table-configuration data to map the value automatically.

 * `date_over_range`
 
   Replace some key dates (over pandas process range) with allowed values and update back after loading into the target.

## Questions and Answers

Q: How to quickly start template data verification?

A: There are 6 steps.

1. Upload initial data (essential for BVR, such as product related configuration data).

2. Upload templates to be verified.

3. Upload code table values.

4. Upload BVR rules. Upload the configuration for one time and update accordingly when there is a change.

5. Upload data files (re-upload after changes).

>> * Q: How to quickly start a profile?
>> * A: Start from baseline profile instead of build from zero.
>>> * Get the baseline configure package.
>>> * Upload the package in the new profile.
>>> * Upload the data file and start to run.
>> * Q: How to refresh the environment and reload data?
>> * A: If the target environment with migration data and no any modification for migration data by function, then you delete the migration data with Del migration data function.
>> * If migration data modified by function then need recreate the environment with deployment script.
6. Click **Stage Verification** and download the report.

Q: How to quickly transfer one profile configuration data from one profile to another?

A: Use **Profile Maintain**.

1. Download the profile using **Profile Maintain**.

2. Switch to another profile.

3. Upload the file from first step.

Q: How to quickly debug script issues?

A: You can verify script syntax issues using blank data files.

1. Ensure the template file has been uploaded.

2. Generate blank data files using **Generate Empty Data File**. 

3. Execute the **Run for All** function.

4. Check the running status and identify issues using **Status**.

5. Update the mapping script, re-upload, and iterate this testing.

Q: How to set fixed sequences for some key columns during different rounds?

A: Set the sequence configuration with `fix_seq_indi='Y'`.

1. Set specific sequences (e.g., `policy_id`, `item_id`) with `fix_seq_indi='Y'`.

2. Ensure the update range for target environment sequences is less than 50,000 during different rounds.

3. Avoid clicking **Sync Start Value?** during loading.

Q: How to quickly initiate a profile?

A: Start from baseline profile instead of starting from scratch.

1. Obtain the baseline configuration package.

2. Upload the package into the new profile.

3. Upload data files and commence running.
