Onekey US Data Load from F14

Prerequisite

The below prerequisites must be met before you execute the OneKey US Data load from F14.

Area Prerequisite Details
Reltio and RDM Connections Make sure to create Reltio and RDM connections in IDPs Entity Collection. For more details, see Connection Configuration.
Plugin Views The task groups MDM_Load_Refresh_Reltio_Views_Customer_Master and MDM_Load_Refresh_Reltio_Views_RDM must be executed in IDP platform at least once before loading the OneKey US Data to Reltio. For more details, see Create the Views in Plugin Views Creation.
Notification Channels At least one notification channel must be configured among Email ID, MS Teams and Slack. For more information, see Adding Alert Notification.
Data Files All the OneKey country data files must be available as defined in OK F14 DID. For more information, see Data Interface Documents.

Onekey: Onekey is a global healthcare professionals (HCP) and institutional reference data set that is continually updated. It provides a seamless capability to capture changes and new data in any connected system.

In this chapter, you will find the complete process of loading the Onekey US Data to Reltio.

Follow the below steps in sequence to execute Onekey US Data Load:

  1. Create Groups, Configuration File and Users for Onekey SFTP Users.

  2. Transfer Onekey input files from SFTP to S3.

  3. Download Pipeline Template for Onekey.

  4. Create Task Group for Onekey Data Load.

  5. Load Onekey US Data to Reltio.

  6. Identify the Delta Load.

Each of the above steps are explained below in detail.

Create Groups, Configuration File and Users for Onekey SFTP Users

Do as below to create and configure the Onekey SFTP User Group and Users to transfer data from Onekey to IDP Platform.

  1. Download the Delivery OK SFTP Users.json.template from s3 location s3://<tenant-bucket>/Tools/.

  2. Rename the file to Delivery OK SFTP Users.json.

  3. Open the file in text editor and replace ${s3-bucket-name} with the name of tenant s3 bucket and Save the file.

  4. To create Groups and Users, follow the instructions listed in Create and Configure SFTP Users for OK File Transfer Process.

Transfer Onekey Input Files from SFTP to S3

  1. Connect to the OneKey server using SFTP tool.

  2. Go to SNOWM folder and locate the Onekey flat files.

  3. Download the input files to the local folder.

  4. Login to S3 bucket and create a folder with the name OKUS_F14/Input.

  5. Login to the IDP OA platform and open the OK_FileTransfer_To_Input task group.

  6. Open the task OneKey File Transfer and update the python argument field with the correct folder name and click Save.

  7. Run the Task group OK_FileTransfer_To_Input. After the successful RUN, the OneKey input Files are copied to the OKUS_F14 folder in the S3 bucket.

Download Pipeline Template for Onekey

  1. Connect to the IDP default S3 bucket and create a folder with the name OKUS_F14/input

  2. Go to the folder <bucket_name>/templates/product and  download the latest version of the pipeline template file MDM_Load_Reltio_OK_US_CT_<version>.json (MDM_Load_Reltio_OK_US_CT_6108.json).

  3. Open the pipeline template MDM_Load_Reltio_OK_US_CT_<version>.json in any text editor and replace all the occurrences of below place holders.

Placeholder Replaceable String
**S3_RELTIO_INBOUND** root/mdm.  Note: Do not specify only "root". it should have a folder at-least as mentioned above.
**RELTIO_CONNECTION_NAME** Reltio Connection name configured in Entity Collection. For example RELTIO_MDM
${bucket-name} S3 Bucket Name for Tenant.
${OneKey-SFTP-Path} SFTP Server Remote Path for Onekey Files.

Note:  The search strings given above are case-sensitive, do not enclose with any character, replace as it is.

Create Task Group for Onekey Data Load

  1. Login to IDP OA platform and under the Data Management section, click Data Pipeline.

  2. On the Landing Page, click Data Pipeline again. The Task Groups page appears.

  3. In the Task Groups page, under the Pipeline Flow, click Task Group from Template, select the json file that you want to import and then click Open.

  4. The pipeline task group MDM_Load_Reltio_OK_US_CT will be created. This task group is used for executing the data load from Onekey US Data to Reltio tenant.

    Load Onekey US Data to Reltio

    1. OneKey provides the country specific (US, CA, MX) data zip files through SFTP.

    2. The flat files are placed in a dedicated s3 location (Ex: OKGBL_F14).

    3. Data in the form of flat files is loaded to the specific Landing Tables and then to the
      specific Staging Tables.

    4. Data that is being loaded to the Landing Tables is always truncate and load.

    5. Loading the data to Staging Tables is always incremental (SCD Type 1).

    6. At the Staging Tables, the data is transformed as per the transformation rules and then loaded to the Canonical Tables. The loading here is always incremental.

    7. The country specific OneKey data present in the Canonical Tables will then be posted to the Reltio customer tenant which is incremental (i.e, Only updated or newly inserted data will be posted to Reltio).
      See the figure below.

 

Note:   

Starting with IDP 6.3 release, remove validation for data load and added penalization to load multiple OneKey files.

Identify the Delta Load

  1. Onekey may send unchanged data which is loaded to Landing Tables. This unchanged data must be identified to avoid loading it to Reltio.

  2. The changed/updated data is identified using the Staging Table's Update Date column (Update DTT) which will be updated when there is a difference between Landing Table record and Staging Table record.

  3. For each RUN, the Update DTT column in the Staging Table compares the Last Run Date and the Current Run Date to identify the Delta.

  4. To run this entire process, the task group MDM_Load_Reltio_OK_US_CT is introduced.

  5. When you RUN this task group, the country specific OneKey data from the Staging Table is transformed using the ODP_CORE_LOG.OK_TRANSFORM_V10 procedure which runs Customer Data - Stage to Canonical to process the delta data. See figure below:

The task group MDM_Load_Reltio_OK_US_CT runs the three Tasks given below:

  1. Set Process Run Log Date (If already not set) - This task will set the Process Run Log Date before loading data to Landing and Staging Tables.

  2. Set Last Run Date - This task will Set the Last Run Date.

  3. Set Current Run Date - This task will set the Current Run Date.

Update History:

Starting with IDP 6.3 release, remove validation for data load and added penalization to load multiple OneKey files.

Enhancements

Transliteration fields mapping

As part of V8, transliteration fields are being mapped to Reltio. For example, China (CN) country's first language is 'Mandarin Chinese' and is receiving the same from OK as FIRSTNAME, LASTNAME etc for HCP. Similarly, the OK team sends FIRSTNAME_2, LASTNAME_2, and so on in English.

Similarly, we have identified the English language translated countries as per OK team input and are populating that English translated value in the Transliterated FirstName, Transliterated LastName etc in Reltio and FIRST_NAME_X, LAST_NAME_X etc in the Canonical table for Individual/HCP. The same way we are doing it for HCO and address as well (please refer to the DID for all the fields).

On the other way, country like Ukraine(UK) OK Team is sending FIRSTNAME_3, LASTNAME_3 etc as a english language. So these fields only mapping to Transliterated FirstName, Transliterated LastName etc in Reltio and FIRST_NAME_X, LAST_NAME_X etc in Canonical table for Individual/HCP.

Included Additional Columns for Ukraine

In 2021, OneKey made changes to F14 format where customer can ask for additional columns in Address, Individual & Workplace Files. See the table below for list of additional columns.

By Default these columns are excluded in data pipeline.

SL NO

ADDRESS

INDIVIDUAL

WORKPLACE

1

ADR_LBL_3

FIRSTNAME_3

WKP_NAME_3

2

ADR_LBL_4

FIRSTNAME_4

WKP_NAME_4

3

ADR_LONG_LBL_3

LASTNAME_3

WKP_OFFICIAL_NAME_3

4

ADR_LONG_LBL_4

LASTNAME_4

WKP_OFFICIAL_NAME_4

5

ADDRESS_LBL_WITHOUT_NUM_3

MAIDENNAME_3

  

6

ADDRESS_LBL_WITHOUT_NUM_4

MAIDENNAME_4

  

7

ADR_EXT_LBL_3

MIDDLENAME_3

  

8

ADR_EXT_LBL_4

MIDDLENAME_4

  

9

CITY_LBL_3

    

10

CITY_LBL_4

    

11

POSTAL_CITY_LBL_3

    

12

POSTAL_CITY_LBL_4

    

Follow the below steps to include the Ukraine columns:

  1. Login to IDP platform using valid credentials and go to Data Pipeline.

  2. Find the task group MDM_Load_Reltio_OK_US_CT and click to open it.

  3. Under the tasks tab, click Stage.

  4. Under the Steps tab, click the flat file *_OK_ADDRESS_*_*.*. See figure below.

  5. In the Column Configuration section, locate the columns listed in the table above and unselect the check box Exclude next to each of them. The columns are at the end of the configuration so if you cannot find the columns in the current page, click Next/Last page until you find them.

  6. Click Save once done.

  7. Perform the steps 4 to 6 again for INDIVIDUAL (pattern - *_OK_INDIVIDUAL_*_*.*) and WORKPLACE (pattern *_OK_WORKPLACE_*_*.*).

  8. Task group MDM_Load_Reltio_OK_US_CT should be run only in the below scenarios:

    • For each IDL or delta onekey US or any north american country(CA,MX etc) data load.

    • When a latest version of this task group released in the subsequent releases.

    • If any changes made to the definition of underlying materialized views or views by client teams.

    • If underlying materialized views are invalid.

  9. Place the input onekey data files in <bucket_name>/OKUS_F14/input before running the task group.

Technical Details

Transformation Rules - Onekey US: HCP - Address

  1. Onekey US individual address data present in staging tables is added/updated to canonicals' C_CUSTOMER_ADDRESS_ID table.

  2. Below are the staging tables involved in this transformation:

    • OK_US_INDIVIDUAL_ADDRESS

    • OK_US_ADDRESS

    • OK_US_ACTIVITY

    • OK_US_WORKPLACE

    • OK_US_WORKPLACE_ADDRESS_RELATION

    • OK_US_INDIVIDUAL

  3. For a given individual any one or both of the below types of address are possible:

    • Indirect address

    • Direct address

  4. Always both direct and indirect addresses are added/updated to canonical C_CUSTOMER_ADDRESS table.

  5. For a single individual id, if both direct and indirect address ids are same then below changes are made to direct address before adding/updating to canonical:

    • Direct address can take priority and gets loaded to Canonical table.

    • Primary affiliation and HCO name is picked from indirect address and added to direct address.

Enhancements to Address Line2

  • If the AddressLine2 data already exists in AddressLine1, we are populating a null value for AddressLine2.

  • If the AddressLine2 data is not present in AddressLine1, then we can populate the AddressLine2.

  • To achieve this, we have referred to the OneKey team's provided countries list. We have parametrized this for future work. We can add or remove any country in the future based on our needs. In any case, OK team adds the new country to the list. We can go ahead and add it. Similarly, we can remove the country if OK team changes the data in the future.

  • Follow the below steps to add or remove the country from the list:

    1. Login to IDP platform using valid credentials and go to Data Pipeline.

    2. Locate and click Task Group MDM_Load_Reltio_OK_Global_CT.

    3. Click Task Stage and go to parameter section.

    4. Edit the ADDRESS_RULE_COUNTRIES parameter value and add the new country code at the end as mentioned below. (Example: \'AT\') Follow the same format.

Setup of the Future Date for STA.9 Status Activities

As part of V8 Patch 3, a logic has been implemented to set the future end date for the activities with status STA.9. When the profile with status STA.9 and associated activities from OneKey with status STA.9 are created, we can set the future end date for those activities (default 90 days and it is configurable). Since we are picking the addresses from activities, that has Address relation also is set to future end date.

By default, the future end date is set to 90 days, but it is configurable based on client requirements.

To configure the future date based on client requirements:

  1. Login to IDP platform using valid credentials and go to Data Pipeline.

  2. Locate and click Task Group MDM_Load_Reltio_OK_US_CT.

  3. Click Task Customer Data - Stage To Canonical and go to parameter section.

  4. Edit the DATE_CONFIG parameter value and add the new value as requested by the client. (Data type is number, please make sure you have pass the number only, not string).

  5. Save the task group successfully.

Activity and Relation Consolidation

Consolidation of relations and activity if associated with the same HCP and HCO. If valid activity is present between HCP1 and HCO2 also same valid relations between same HCP1 and HCO2  with LINK_TYPE_CODE in (ENTITIES_LINK.WUS.0, ENTITIES_LINK.WUS.1, ENTITIES_LINK.WUS.2,ENTITIES_LINK.WUS.3, ENTITIES_LINK.WUS.4 ) we are  ignoring loading such relations to canonical tables and Reltio Tenant.

Ohio License Identifier

Ohio licence identifier data is mapped in V9 with all the information. Please refer to the HCO Identifier section in DID for more information. This identifier is applicable only to HCO entities and US countries.

Special Instructions for Code Migration for Those Clients Who Have Migrated from MDM1.0 to MDM2.0

Clients who are currently using MDM1.0 Informatica workflow to load OK data and migrating to MDM2.0 IDP OK pipeline. Below are the steps that need to be followed to maintain the same WS canonical codes in the RDM tenant.

  1. After importing the MDM_Load_Reltio_OK_US_CT pipeline, go to Code - Stage To Canonical task and chose parameters section to modify the parameter CONVERT_CODES_TO_WS_FORMAT as true.

  2. After updating the parameter value, save the pipeline.

    Note:   

    • By default, CONVERT_CODES_TO_WS_FORMAT can return false.

    • This code conversion can pick only the codes that can fall under the current run(delta). In order to convert all the codes available in staging, update the pipeline code's LAST_RUN_DATE in ODP_CORE_LOG.MDM_PROCESS_CONTROL_LOG table with 1900-01-01.

    • In MDM1.0 codes, the sourcecode description is concatenated with the parent code and lis code. The same change is addressed in the above pipeline. Example: CA:CPH. If parent code is null populating only code.

    • In MDM1.0 codes, the canonical code description is concatenated with the code and code description. The same change is addressed in the above pipeline. Example: WCA.CPH.DAV-Davidson Drugs, Inc.

One Time Data Correction Script

Important: Fixed the unnecessary updates to Reltio from OK without any change and loser merge inactivation issue. In this update, there is no change in the pipeline except the change for OK procedure. Once the V9 patch 3 is applied successfully, follow the below one-time data correction script steps to correct the impacted data and this must be done only once.

Note:  Below instructions are applicable for the users who are migrating from V9 to V10 directly without V9.3 patch. Ignore the below scripts if these instructions are taken care as part of V9.3 patch.

Populate the delete date for the impacted loser profile

This script is to populate delete date for those impacted profiles and make the status change to post to Reltio.

  1. Run the below update query to populate delete date for the loser profile in merge scenario's in the MDM_CANONICAL.C_CUSTOMER table.

    Copy 
    UPDATE MDM_CANONICAL.C_CUSTOMER C
    SET C.LOAD_STATUS = 'PENDING_PLUGIN',
        C.SYS_END_DT = CURRENT_TIMESTAMP()
    WHERE C.CUSTOMER_ID IN ( SELECT LOSER_CUSTOMER_ID FROM MDM_CANONICAL.C_MERGE WHERE MERGE_TYPE = 'M')
        AND C.SYS_END_DT IS NULL
        AND C.SOURCE_NAME = 'OK'; 

  2. Run the below update query to remove the delete date for the loser profile in split scenario's in MDM_CANONICAL.C_CUSTOMER table, if any.

    Copy 
    UPDATE MDM_CANONICAL.C_CUSTOMER C
    SET C.LOAD_STATUS = 'PENDING_PLUGIN',
        C.SYS_END_DT = NULL
    WHERE C.CUSTOMER_ID IN ( SELECT LOSER_CUSTOMER_ID FROM MDM_CANONICAL.C_MERGE WHERE MERGE_TYPE = 'S')
        AND C.SYS_END_DT IS NOT NULL
        AND C.SOURCE_NAME = 'OK';

  3. Login to IDP portal → Open Data pipeline App → Find the V9 OK US CT data load task group (MDM_Load_Reltio_OK_US_CT) → Open the task group (MDM_Load_Reltio_OK_US_CT)..

    1. Run HCO - Canonical To Reltio steps to load OneKey data.

    2. Run HCO - Canonical To Reltio steps to load OneKey data.

Data correction script for OHIO TDDD license information

Note:  Below instructions are applicable for the users who are migrating from V10 to V11 directly without V10 patch. Ignore the below scripts if these instructions are taken care as part of V10 patch.

  1. This script corrects the OneKey Workplace OHIO TDDD license data in Canonical tables as well as HCO entity in Reltio for the State Attribute.

  2. Follow the below instructions to correct the OHIO TDDD license data:

  • Run the below create sql statement which will identify onekey ids for which Workplace OHIO TDDD license data need to be loaded to Reltio.

    Copy 
    CREATE OR REPLACE TABLE ODP_CORE_STAGING.ONEKEY_DATA_CORRECTION_US AS (
    SELECT DISTINCT ENTITY_ID_ONEKEY AS ID, ENTITY_TYPE FROM ODP_CORE_STAGING.OK_US_ENTITY_UCI 
    WHERE UCI_TYPE_CODE = 'REX.WUS.OH_TDDD');

  • Login to IDP portal →  Open Data pipeline App → Find the V11 OK US CT data load task group (MDM_Load_Reltio_OK_US_CT) and run it to load OneKey data.

    Note:  After executing the step.1(Create statement), this can be continued with delta file as usual. Step 1 will identify the records to be processed and will be loaded to Canonical and Reltio in next concurrent data load. Above table will be truncated at the end of the load so no issues with next day load.

Data Interface Document:

Refer the below mentioned DID for OneKey F14 File format:

See DID of OK F14 DID and OneKey US MDM Mapping DID

in Data Interface Documents.