Generic Import Format (GIF) - Codes and Data Load

Prerequisite

The following are the prerequisite conditions for Generic Import Code Load:

  1. To configure reltio tenant details, see Configure Connection.

  2. To configure notification channels such as email id or MS teams channel or slack channel, see Adding Alert Notification. Configure at least one notification channel and Subscribe to the task group pipeline.

  3. Keep GIF file sets ready as per the layout defined in the GIF DID of Generic Import Format in Data Interface Documents.

  4. Run the pipeline MDM_Load_Refresh_Reltio_Views_Customer_Master which creates/replaces one time Reltio Views used for inbound connector.

Generic Import DataLoad Overview

Generic Import Format (GIF) data which is much flexible to extend as per the client source data can be loaded into Reltio using IDP pipelines in OA platform. Pipelines configured with task groups that loads data from S3 files → Staging Area →Canonical Schema → Reltio using Plugins.

Import Pipeline Template

To Import Pipeline Template

  1. Connect to IDP default s3 bucket and go to the folder <bucket_name>/templates/product.

  2. Download the MDM_Load_Reltio_Generic_<version>.json file template to local machine.

    Note:   

    If there are multiple files prefixed MDM_Load_Reltio_Generic_* with different versions, download the latest version.

  3. Open the downloaded template json file with any text editor to find and replace the below placeholders with appropriate values.

    Placeholder Replaceable String
    **reltioconnection** Reltio Connection name configured in Entity Collection for example, RELTIO_MDM
    **S3_RELTIO_INBOUND** S3 path to load the data to MDM

    Note:   

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

  4. Login to IDP platform with valid credentials.

  5. Go to Data Pipeline, click Task Group from Template and import the template using updated template file.

    Note:   

    Process creates the pipeline task group after the successful import. This has to be used for execution.

    Note:   

    MDM_Load_Customer_Payer_Reltio_Generic pipelines should not be used when MDM_Load_Reltio_Generic is used.

Generic Import Data Load PipeLine Steps

  1. MDM_GenericImport Files- S3 connector plug-in loads both initial and delta data files (<S3_Bucket>/<Client_Folder>/input/MDM_Generic_Import*.*) from S3 to Landing and Staging tables with SCD Type 1 Full Refresh Method:

    1. Using Winscp or S3 browser, login to S3 bucket path mentioned and go to the root folder. Place the GIF files inside the root folder.

    2. Data present in the each flat file is loaded to separate landing tables. Before each load, previous data present in landing tables is truncated.

    3. Data present in landing tables is loaded to staging tables. For each load, data in staging is always either inserted or updated.

      FileName Patterns
      MDM_GenericImport_HCO.dat

      MDM_GenericImport_HCP.dat
      MDM_GenericImport_ADDRESS.dat
      MDM_GenericImport_COMM.dat
      MDM_GenericImport_CUST_ADDRESS.dat
      MDM_GenericImport_DEGREE.dat
      MDM_GenericImport_IDENTIFIER.dat
      MDM_GenericImport_PRIVACY.dat
      MDM_GenericImport_Relations.dat
      MDM_GenericImport_SPECIALITY.dat
      MDM_GenericImport_MERGE.dat
      Generic_Code_Import.dat
      MDM_GenericImport_BRICK.dat

  2. Set Current Date

    • sqlExecutor Plugin to set the current date time stamp in the log table for the process to identify the incremental data as of last run date to current timestamp.

  3. Move Staging Codes to Canonical Schema

    • Deformalized Generic Codes that are staged from file gets transformed into canonical schema as a normalized form that helps to generate RDM JSON for plug-in.

      Task Description
      Generic Codes to Canonical Moves data from Staging Generic_Code_Import_table to R_CODE_ITEMS and R_EXTERNAL_ITEMS,R_DEPENDENCY_ITEMS and R_LOCALE_ITEMS table.

  4. Post To Reltio RDM.

    • Plugin-Inbound builds Json message from Canonical schema RDM tables and push them into Reltio RDM tenant as per configuration.

  5. Reltio RDM Extract

    • Generic import data may have it'scode values to be translated before we import them into Reltio. some attributes which are defined at lookup field can do lookup for GIF code and get the relevant MDM code to form the GIF customer data into respective table. for instance if GIF sends gender code as "MALE", this is trsnalated as GEN.M which is Reltio code and pushed into canonical schema. in order to validate the GIF source data against the RDM codes, we need to extract the codes from Reltio and fill in to the landing tables.

  6. RDM Outbound Staging Process

    • To avoid the mapping issues while extracting the RDM codes from two different tenants in the same IDP environment, each RDM_SOURCEMAPPINGS table name is configured with a suffix specifying the Reltio connection name as IDPSchema.MV_@ReltioConnectionName_RDM_SOURCEMAPPINGS. In this task, the RDM_SRCMAPPINGS view references from Staging RDM table for which, this process manages the transaction and sync it from Land to Stage.

  7. Create Views Used in Mapping

    • sqlExecutor plugin for creating any custom views which is referred in the staging to canonical JSON mapping. At present,baselines has two VIEWS to incorporate the CUST_ADDRESS and IDENTIFIERS data that we receive for multiple entities.

  8. Move Staging to Canonical -Data from a Table/View are transformed and copied to MDM Canonical schema tables based on JSON mapping configuration using Move Staging to Canonical process. Data present in canonical tables are the one's to be posted to Reltio tenant.

    1. RDM Validation -Business Rule plug-in picks data from staging and validates the code field defined in the JSON mapping configuration and writes the status for each records in RDM_VALIDATION table. you may see RDM Validation page for the configuration.

    2. Every task below have unique set of mapings based on the fields mapped. see the Staging to canonical copy process for more understanding about mapping source/target fields.

      Task Description
      MDM STG CANONICAL_HCP Moves data from Staging MDM_GENERICIMPORT_HCP table to C_HCO and C_PROFESSION table.
      MDM STG CANONICAL_HCO Moves data from Staging MDM_GENERICIMPORT_HCO table to C_HCO table.
      MDM STG CANONICAL_ADDRESS Moves data from Staging VW_CUST_ADDRESS view to C_CUSTOMER_ADDRESS table.
      MDM STG CANONICAL_COMM Moves data from Staging MDM_GENERICIMPORT_COMM table to C_COMM table.
      MDM STG CANONICAL_DEGREE Moves data from Staging MDM_GENERICIMPORT_DEGREE table to C_DEGREE table.
      MDM STG CANONICAL_IDENTIFIER Moves data from Staging VW_GENERICIMPORT_IDENTIFIER view to C_IDENTIFIER and C_LICENSE tables based on ID_TYPE values.
      MDM STG CANONICAL_SPECIALTY Moves data from Staging MDM_GENERICIMPORT_SPECIALITY table to C_SPECIALTY table.
      MDM STG CANONICAL_PRIVPREF Moves data from Staging MDM_GENERICIMPORT_PRIVACY table to C_PRIVACY_PREFERENCE table.
      MDM STG CANONICAL_CUSTOMER Moves data from Staging HCP/HCO table to C_CUSTOMER table.
      MDM STG CANONICAL_AFFILIATIONS Moves data from Staging MDM_GENERICIMPORT_RELATIONS table to C_AFFILIATION table.
      MDM STG CANONICAL_MERGE Moves data from Staging MDM_GENERICIMPORT_MERGE table to C_MERGE table.
      MDM STG CANONICAL_BRICK Moves data from Staging MDM_GENERICIMPORT_BRICK table to C_ADDRESS_BRICK table.

  9. Set Last RunDate

    sqlExecutor Plugin to sets the step3 captured processing time as the last_run_date in the log table for the process to identify the incremental data.

  10. Inbound Plugin Connector

    Plugin-Inbound builds json message from Canonical schema tables and push them into Reltio tenant as per configuration in the below order:

    • HCP Load

    • HCO Load

    • Relations Load

    • Merge Load

    Note:   

    Plugin process considered the business friendly(BF_ID) data, when the BF_ID supplied in the source, it goes as it is and if not supplied in the source, plugin process can read the BF_ID config table and according to config table, it computes the BF_ID and puts it into the respective BF_ID column in respective canonical table and also puts it in attribute in Reltio. For additional information on BF_ID configuration, for additional information see Plugin Views Creation.

Operational Steps

  1. Keep the GIF filesets ready as per the layout defined in the GIF DID of Generic Import Format in Data Interface Documents.

  2. Login to IDP console and open the MDM_Generic_Import_DataLoad pipeline. If GIF Dataload Template is not registered then see Import GIF Pipeline.

  3. Open the MDM_Generic_Import_DataLoad Pipeline and go to MDM_GenericImport_Files task to know the S3 path.

  4. Using Winscp or S3 browser, login to S3 bucket path mentioned and go to the root folder. Place the GIF files inside the <root folder>.

  5. Click Run, once files are placed.

  6. Click Show Execution Logs, next to Run icon for verifying the task status.

  7. Monitor jobs

    1. Both Data pipeline Jobs or API call jobs used to post data to Reltio, can be monitored through spring cloud data flow (SCDF).

    2. To monitor, Login to IDP portal, click Data Pipeline in Landing Page.

    3. Identify the Task Group/Pipeline name for which monitoring is required (For Example; OK_US_Data_Load). Click Show Execution Logs icon.

    4. Spring cloud data flow page are opened in a new tab. Click Jobs.

  8. Once the tasks are completed successfully then execute the below queries to get counts of HCP, HCO, Affiliation and Merge from canonical table present in snowflake data warehouse under the schema name MDM_CANONICAL. These counts are used to validate against the counts of data posted to Reltio

    • SELECT COUNT(0) FROM MDM_CANONICAL.C_CUSTOMER where LOAD_STATUS = 'SUCCESSFUL' AND CUSTOMER_TYPE = 'HCP' AND SOURCE_NAME='GIF';

    • SELECT COUNT(0) FROM MDM_CANONICAL.C_CUSTOMER where LOAD_STATUS = 'SUCCESSFUL' AND CUSTOMER_TYPE = 'HCO' AND SOURCE_NAME='GIF';

    • SELECT COUNT(0) FROM MDM_CANONICAL.C_AFFILIATION where LOAD_STATUS = 'SUCCESSFUL' AND SOURCE_NAME='GIF';

    • SELECT COUNT(0) FROM MDM_CANONICAL.C_MERGE where LOAD_STATUS = 'SUCCESSFUL';

Troubleshooting

  • Mapping failure issues has to be fixed in the staging to canonical configurations. Make sure that Reltio attribute and data type is matching with it.

  • If table not found exception for RDM_SRCMapping then you did not run the RDM extract process. It should get executed once.

  • In case of failure in any task, fix the error and restart the task from the failed task till the end.

See DID of Generic Import Format in Data Interface Documents.