Optimizer IC Integration

Overview

The objective is to create a data pipeline to map data from OCE-O to IC program table structures and make it available in the IC Staging Area.

Prerequisites

The following are the prerequisite conditions:

• CSM requests the OEC-O team to share the snowflake secure views to the IDP snowflake account which IC is using by providing the OCE-O tenant id and IDP url, dbname, and snf account.

• Once it is shared, ProdOps/DevOps needs to create a database using this ,in order to access the objects in the IDP snowflake account. Email:oceproductsupport@iqvia.com.

• ProdOps/DevOps needs to grant the above shared DB access to the OPERATOR user so that the operator role should have access to the IDP snowflake DB + shared DB from pipeline or sqlexplorer.

Data Flow High Level Design

OCE-O objects are shared with IDP using snowflake secured views. Using these shared objects, views will be created according to the business rules. Based on views, required fields can be mapped to the IC staging tables based on stage mapping configuration.

Data Pipeline Specifications and Design

The data pipeline will have global parameters which are created at the task group level.

Parameter Name	Parameter Value
TGT_SCHEMA_NAME	ODP_CORE_STAGING
TGT_DB	<Target Database> Target IC database Example:   IDP_IC_OA_SANDBOX_USV_ENV_DWH
SRC_SCHEMA_NAME	DATASHARE  schema name of source database
PIPELINE_CODE	OCEO_IC_DATASET2
SRC_DB	<Source Shared Database> Source shared Database name  Example: TOCEOIDPDEV_PROD_21088AAE8180484EA362867299E711B7_SH_DB

Pipeline

Note:

If a new baseline Data pipe line is released and if there are any client team changes, they need to apply them manually in the baseline Data pipe line.

Tasks and Step Level Parameters

The following are the tasks and task level parameters:

1. Validate Secure Views

   This task can check whether the OCE-O Share objects are available in the Shared database. It uses global parameters and connects to the shared database and checks for required views using the below stored procedure.

   Copy

   CALL ODP_CORE_LOG.SP_GET_INF_SCHEMA_TABLES('@SRC_DB','@SRC_SCHEMA_NAME','OMSALESFORCE');

   Parameter	Description
   @SRC_DB	Source database name
   @SRC_SCHEMA_NAME	Source schema name
   OMSALESFORCE	Object / view which we want to check

   If required objects are not exist, then task can fail.

2. Create IC Tables:

   This task can create required tables in “ODP_CORE_STAGING” schema as per specifications given in “IC Input Data Model - OCE Optimizer -V5.xlsx”.

   Copy

   create table IF NOT EXISTS   ODP_CORE_STAGING. <TABLE_NAME> (
   
   COLUMN1                          DATA_TYPE,
   
   COLUMN2                          DATA_TYPE
   
   );

3. Intermediate Transformation Views

   This task can create views suing shared objects as per specifications given in “IC Input Data Model - OCE Optimizer -V4.xlsx”.release.

   Client teams can modify the views as per the requirement to have new columns.

   Copy

   create or replace view @TGT_DB.@TGT_SCHEMA_NAME.VW_<VIEW_NAME> AS
   
   select
   
   ST.COLUMN1,
   
   …
   
   from @SRC_DB.@SRC_SCHEMA_NAME.<SRC_TABLE>  ST;

   Parameters used in this task

   Parameter Name	Parameter Value	Description
   TS_Status	'ACTV'	TerritorySalesfoce Status possible values 'ACTV', 'INAC'
   SRC_SCHEMA_NAME	DATASHARE
   TGT_SCHEMA_NAME	ODP_CORE_STAGING
   IS_OAT_ENDDATED	OAT."EndDate" IS NULL OR OAT."EndDate" >= GETDATE()	Acount Territory end date condition use 1=1 for no filter
   OG_Type	'POST'	Territory type possible values 'POST', 'STAT'

4. Stage the Data

   This task loads ‘IC views’ data into ‘IC staging tables’. It uses below stored procedure and parameters.

   Copy

   call ODP_CORE_LOG.COPY_OCE_SHARE_TO_IC_STG('@PIPELINE_CODE','@ORGANIZATION_TYPE', false);

   Stored Procedure Parameters

   Parameter	Description
   @PIPELINE_CODE	Pipeline code
   @ORGANIZATION_TYPE	A Json config given in parameter “ORGANIZATION_TYPE” refer 4.1 parameters used
   False	IS_DEBIG is set to false

   Mapping Parameters

   Parameter	Parameter Value
   SALESFORCE_NAME	{"dataSource":"OCEC","sourceSchema":"ODP_CORE_STAGING","targetSchema":"ODP_CORE_STAGING","dateFormat":"DD-MM-YYYY","mapping":[{"sourceTable":"VW_SALESFORCE_NAME","targetTable":"SALESFORCE_NAME","fields":[{"sourceField":"CONCAT(st.SALESFORCE_CODE,nvl(st.EFFECTIVE_START_DATE,''-1''))","targetField":"CONCAT(SALESFORCE_NAME.SALESFORCE_CODE,nvl(SALESFORCE_NAME.EFFECTIVE_START_DATE,''-1''))","targetFieldType":"COMPOSITEKEY","fieldMasking":"true"},{"sourceField":"SALESFORCE_CODE","targetField":"SALESFORCE_CODE","targetFieldType":"String"},{"sourceField":"SALESFORCE_DESC","targetField":"SALESFORCE_DESC","targetFieldType":"String"},{"sourceField":"EFFECTIVE_START_DATE","targetField":"EFFECTIVE_START_DATE","targetFieldType":"String"},{"sourceField":"EFFECTIVE_END_DATE","targetField":"EFFECTIVE_END_DATE","targetFieldType":"String"},{"sourceField":"TIME_PERIOD_ID","targetField":"TIME_PERIOD_ID","targetFieldType":"String"}]}]}
   ZIP_BRICK_TERR	{"dataSource":"OCEC","sourceSchema":"ODP_CORE_STAGING","targetSchema":"ODP_CORE_STAGING","dateFormat":"DD/MM/YYYY","mapping":[{"sourceTable":"VW_ZIP_BRICK_TERR","targetTable":"ZIP_BRICK_TERR","fields":[{"sourceField":"CONCAT(st.SALESFORCE_CODE,nvl(st.ZIP_BRICK_CODE,''-1''),nvl(st.EFFECTIVE_START_DATE,''-1''))","targetField":"CONCAT(ZIP_BRICK_TERR.SALESFORCE_CODE,nvl(ZIP_BRICK_TERR.ZIP_BRICK_CODE,''-1''),nvl(ZIP_BRICK_TERR.EFFECTIVE_START_DATE,''-1''))","targetFieldType":"COMPOSITEKEY","fieldMasking":"true"},{"sourceField":"SALESFORCE_CODE","targetField":"SALESFORCE_CODE","targetFieldType":"String"},{"sourceField":"ZIP_BRICK_CODE","targetField":"ZIP_BRICK_CODE","targetFieldType":"String"},{"sourceField":"ORG_CODE","targetField":"ORG_CODE","targetFieldType":"String"},{"sourceField":"EFFECTIVE_START_DATE","targetField":"EFFECTIVE_START_DATE","targetFieldType":"String"},{"sourceField":"EFFECTIVE_END_DATE","targetField":"EFFECTIVE_END_DATE","targetFieldType":"String"},{"sourceField":"TIME_PERIOD_ID","targetField":"TIME_PERIOD_ID","targetFieldType":"String"},{"sourceField":"Type","targetField":"Type","targetFieldType":"String"}]}]}
   ROSTER	{"dataSource":"OCEC","sourceSchema":"ODP_CORE_STAGING","targetSchema":"ODP_CORE_STAGING","dateFormat":"DD/MM/YYYY","mapping":[{"sourceTable":"VW_ROSTER","targetTable":"ROSTER","fields":[{"sourceField":"CONCAT(st.PAYEE_ID,nvl(st.EFFECTIVE_START_DATE,''-1''))","targetField":"CONCAT(ROSTER.PAYEE_ID,nvl(ROSTER.EFFECTIVE_START_DATE,''-1''))","targetFieldType":"COMPOSITEKEY","fieldMasking":"true"},{"sourceField":"PAYEE_ID","targetField":"PAYEE_ID","targetFieldType":"String"},{"sourceField":"FIRST_NAME","targetField":"FIRST_NAME","targetFieldType":"String"},{"sourceField":"MIDDLE_NAME","targetField":"MIDDLE_NAME","targetFieldType":"String"},{"sourceField":"LAST_NAME","targetField":"LAST_NAME","targetFieldType":"String"},{"sourceField":"HIRE_DATE","targetField":"HIRE_DATE","targetFieldType":"String"},{"sourceField":"TERMINATION_DATE","targetField":"TERMINATION_DATE","targetFieldType":"String"},{"sourceField":"EMAIL_ID","targetField":"EMAIL_ID","targetFieldType":"String"},{"sourceField":"USER_NAME","targetField":"USER_NAME","targetFieldType":"String"},{"sourceField":"EFFECTIVE_START_DATE","targetField":"EFFECTIVE_START_DATE","targetFieldType":"String"},{"sourceField":"EFFECTIVE_END_DATE","targetField":"EFFECTIVE_END_DATE","targetFieldType":"String"},{"sourceField":"TITLE","targetField":"TITLE","targetFieldType":"String"},{"sourceField":"TIME_PERIOD_ID","targetField":"TIME_PERIOD_ID","targetFieldType":"String"}]}]}
   ASSIGNMENTS	{"dataSource":"OCEC","sourceSchema":"ODP_CORE_STAGING","targetSchema":"ODP_CORE_STAGING","dateFormat":"DD/MM/YYYY","mapping":[{"sourceTable":"VW_ASSIGNMENTS","targetTable":"ASSIGNMENTS","fields":[{"sourceField":"CONCAT(st.Person_ID,nvl(st.EFFECTIVE_START_DATE,''-1''))","targetField":"CONCAT(ASSIGNMENTS.Person_ID,nvl(ASSIGNMENTS.EFFECTIVE_START_DATE,''-1''))","targetFieldType":"COMPOSITEKEY","fieldMasking":"true"},{"sourceField":"Person_ID","targetField":"Person_ID","targetFieldType":"String"},{"sourceField":"ORG_CODE","targetField":"ORG_CODE","targetFieldType":"String"},{"sourceField":"STATUS","targetField":"STATUS","targetFieldType":"String"},{"sourceField":"EFFECTIVE_START_DATE","targetField":"EFFECTIVE_START_DATE","targetFieldType":"String"},{"sourceField":"EFFECTIVE_END_DATE","targetField":"EFFECTIVE_END_DATE","targetFieldType":"String"},{"sourceField":"TIME_PERIOD_ID","targetField":"TIME_PERIOD_ID","targetFieldType":"String"}]}]}
   HCP_UNIVERSE	{"dataSource":"OCEC","sourceSchema":"ODP_CORE_STAGING","targetSchema":"ODP_CORE_STAGING","dateFormat":"DD/MM/YYYY","mapping":[{"sourceTable":"VW_HCP_UNIVERSE","targetTable":"HCP_UNIVERSE","fields":[{"sourceField":"CONCAT(nvl(st.SALESFORCE_CODE,''-1''),nvl(st.HCP_ID,''-1''),nvl(st.EFFECTIVE_START_DATE,''-1''))","targetField":"CONCAT(nvl(HCP_UNIVERSE.SALESFORCE_CODE,''-1''),nvl(HCP_UNIVERSE.HCP_ID,''-1''),nvl(HCP_UNIVERSE.EFFECTIVE_START_DATE,''-1''))","targetFieldType":"COMPOSITEKEY","fieldMasking":"true"},{"sourceField":"SALESFORCE_CODE","targetField":"SALESFORCE_CODE","targetFieldType":"String"},{"sourceField":"HCP_ID","targetField":"HCP_ID","targetFieldType":"String"},{"sourceField":"ZIP","targetField":"ZIP","targetFieldType":"String"},{"sourceField":"VALID","targetField":"VALID","targetFieldType":"String"},{"sourceField":"EFFECTIVE_START_DATE","targetField":"EFFECTIVE_START_DATE","targetFieldType":"String"},{"sourceField":"EFFECTIVE_END_DATE","targetField":"EFFECTIVE_END_DATE","targetFieldType":"String"},{"sourceField":"TIME_PERIOD_ID","targetField":"TIME_PERIOD_ID","targetFieldType":"String"}]}]}
   HIERARCHY	{"dataSource":"OCEC","sourceSchema":"ODP_CORE_STAGING","targetSchema":"ODP_CORE_STAGING","dateFormat":"DD/MM/YYYY","mapping":[{"sourceTable":"VW_HIERARCHY","targetTable":"HIERARCHY","fields":[{"sourceField":"CONCAT(st.ORG_CODE,nvl(st.EFFECTIVE_START_DATE,''-1''))","targetField":"CONCAT(HIERARCHY.ORG_CODE,nvl(HIERARCHY.EFFECTIVE_START_DATE,''-1''))","targetFieldType":"COMPOSITEKEY","fieldMasking":"true"},{"sourceField":"SALESFORCE_CODE","targetField":"SALESFORCE_CODE","targetFieldType":"String"},{"sourceField":"SALESFORCE_NAME","targetField":"SALESFORCE_NAME","targetFieldType":"String"},{"sourceField":"ORG_CODE","targetField":"ORG_CODE","targetFieldType":"String"},{"sourceField":"ORG_NAME","targetField":"ORG_NAME","targetFieldType":"String"},{"sourceField":"PARENT_ORG_CODE","targetField":"PARENT_ORG_CODE","targetFieldType":"String"},{"sourceField":"PARENT_ORG_NAME","targetField":"PARENT_ORG_NAME","targetFieldType":"String"},{"sourceField":"ORG_TYPE","targetField":"ORG_TYPE","targetFieldType":"String"},{"sourceField":"PARENT_ORG_TYPE","targetField":"PARENT_ORG_TYPE","targetFieldType":"String"},{"sourceField":"DEFAULT_TITLE","targetField":"DEFAULT_TITLE","targetFieldType":"String"},{"sourceField":"EFFECTIVE_START_DATE","targetField":"EFFECTIVE_START_DATE","targetFieldType":"String"},{"sourceField":"EFFECTIVE_END_DATE","targetField":"EFFECTIVE_END_DATE","targetFieldType":"String"},{"sourceField":"TIME_PERIOD_ID","targetField":"TIME_PERIOD_ID","targetFieldType":"String"}]}]}
   ORGANIZATION_TYPE	{"dataSource":"OCEC","sourceSchema":"ODP_CORE_STAGING","targetSchema":"ODP_CORE_STAGING","dateFormat":"DD/MM/YYYY","mapping":[{"sourceTable":"VW_ORGANIZATION_TYPE","targetTable":"ORGANIZATION_TYPE","fields":[{"sourceField":"CONCAT(st.ORG_TYPE_ID,nvl(st.EFFECTIVE_START_DATE,''-1''))","targetField":"CONCAT(ORGANIZATION_TYPE.ORG_TYPE_ID,nvl(ORGANIZATION_TYPE.EFFECTIVE_START_DATE,''-1''))","targetFieldType":"COMPOSITEKEY","fieldMasking":"true"},{"sourceField":"ORG_TYPE_ID","targetField":"ORG_TYPE_ID","targetFieldType":"String"},{"sourceField":"ORG_TYPE","targetField":"ORG_TYPE","targetFieldType":"String"},{"sourceField":"EFFECTIVE_START_DATE","targetField":"EFFECTIVE_START_DATE","targetFieldType":"String"},{"sourceField":"EFFECTIVE_END_DATE","targetField":"EFFECTIVE_END_DATE","targetFieldType":"String"},{"sourceField":"TIME_PERIOD_ID","targetField":"TIME_PERIOD_ID","targetFieldType":"String"}]}]}

5. Adding new fields to IC staging tables

   Check required field is available in the shared database objects, if it is available.

   1. Add the NEW_COLUMN to the corresponding table in the "CREATE IC TABLES" task.

      • Check if the table already exists. Use the Alter command to add a new column using the SQL explorer in IDP.

      • If not present, include the new column in the CREATE IC TABLE task definition.

   2. Open Intermediate Transformation Views and add the new column to the corresponding view.

      Copy

      create or replace view @TGT_DB.@TGT_SCHEMA_NAME.VW_<VIEW_NAME> AS
      
      select
      
      ST.COLUMN1,
      
      ST.NEW_COLUMN,
      
      …
      
      from @SRC_DB.@SRC_SCHEMA_NAME.<SRC_TABLE>  ST;

      For Roster, Currently the title is picked form Role, If it needs to be picked from User Tile or User Territory Tile table. we can use the below join in Roster view.

      Example:

      create view IF NOT EXISTS @TGT_DB.@TGT_SCHEMA_NAME.VW_ROSTER AS

      SELECT

      OU."UniqueIntegrationId" PAYEE_ID,

      OU."FirstName" FIRST_NAME

   OUT.TitleDescription Title or OUTT.TitleDescription Title

   FROM @SRC_DB.@SRC_SCHEMA_NAME.OMUSER OU

   left outer join (Select * from(select "SOMUserId","TitleDescription",row_number() over (partition by "SOMUserId" order by "EffectiveDate" desc) rank from @SRC_DB.@SRC_SCHEMA_NAME.OMUSERTITLE) where rank =1) OUT on OU."SOMUserId"= OUT."SOMUserId"

   left outer join (Select * from(select "SOMUserId","TitleDescription",row_number() over (partition by "SOMUserId" order by "EffectiveDate" desc) rank from @SRC_DB.@SRC_SCHEMA_NAME.OMUSERTERRITORYTITLE) where rank =1) OUTT on OU."SOMUserId"= OUTT."SOMUserId" ;

   In case of any getting duplicates in any one of the staging tables, we can remove them by identifying the unique column combination and apply partition by with rank as shown above.

   1. Open Stage the Data and navigate to Parameters as below.

      

   2. Click to can edit the respective table parameter json.

      

   3. Click Save.

      

   To include a new column under the "SALESFORCE_NAME" object, follow the steps below.

   Take json named “SALESFORCE_NAME”

   Edit and Add below highlighted key- value pair as highlighted.

   Copy

   {
   
     "dataSource": "OCEC",
   
     "sourceSchema": "ODP_CORE_STAGING",
   
     "targetSchema": "ODP_CORE_STAGING",
   
     "dateFormat": "DD-MM-YYYY",
   
     "mapping": [
   
       {
   
         "sourceTable": "VW_SALESFORCE_NAME",
   
         "targetTable": "SALESFORCE_NAME",
   
         "fields": [
   
           {
   
             "sourceField": "CONCAT(st.SALESFORCE_CODE,nvl(st.EFFECTIVE_START_DATE,''-1''))",
   
             "targetField": "CONCAT(SALESFORCE_NAME.SALESFORCE_CODE,nvl(SALESFORCE_NAME.EFFECTIVE_START_DATE,''-1''))",
   
             "targetFieldType": "COMPOSITEKEY",
   
             "fieldMasking": "true"
   
           },
   
           {
   
             "sourceField": "<source new_column>",
   
             "targetField":  "<target new_column>",
   
             "targetFieldType": "<dataType>"
   
           },
   
   
   
           {
   
             "sourceField": "SALESFORCE_CODE",
   
             "targetField": "SALESFORCE_CODE",
   
             "targetFieldType": "String"
   
           },
   
           {
   
             "sourceField": "SALESFORCE_DESC",
   
             "targetField": "SALESFORCE_DESC",
   
             "targetFieldType": "String"
   
           },
   
           {
   
             "sourceField": "EFFECTIVE_START_DATE",
   
             "targetField": "EFFECTIVE_START_DATE",
   
             "targetFieldType": "String"
   
           },
   
           {
   
             "sourceField": "EFFECTIVE_END_DATE",
   
             "targetField": "EFFECTIVE_END_DATE",
   
             "targetFieldType": "String"
   
           },
   
           {
   
             "sourceField": "TIME_PERIOD_ID",
   
             "targetField": "TIME_PERIOD_ID",
   
             "targetFieldType": "String"
   
           }
   
         ]
   
       }
   
     ]
   
   }

6. Data Validation: This task can check for primary keys and referential integrity as per DID mapping.

   1. Logging this can create log table if it is not exist.

      Copy

      create  table  IF NOT EXISTS @TGT_DB.ODP_CORE_LOG.OCEO_IC_VALIDATION_LOG (
      FILED_NAME    Varchar(50),
      MISSING_ID    Varchar(50),
      TABLE_NAME  Varchar(50),    
      validation_type Varchar(50),
      VALIDATION_DATE    Datetime,
      LATEST_RECORDS VARCHAR(1)
      );
      UPDATE @TGT_DB.ODP_CORE_LOG.OCEO_IC_VALIDATION_LOG SET LATEST_RECORDS = 'N' ;

   2. This task can check for primary key validation and if any record fails it can move the record to log table "OCEO_IC_VALIDATION_LOG". Check for VALIDATION_TYPE = 'PRIMARY KEY'.

   3. This can check for referential integrity(FK) and invalid records are moved to "OCEO_IC_VALIDATION_LOG" table. Check for VALIDATION_TYPE = 'FOREIGN KEY'.

   4. Null checks are added as per DID and invalid records are moved to "OCEO_IC_VALIDATION_LOG" table. check for VALIDATION_TYPE = 'NULL CHECK'.

Troubleshooting

Sl. No	Issue Description	Solution
1	Process Failure	In the case of an entire process failure, check the error log to see the error message or try running individual tasks in sequence to isolate the issue against a specific task.
2	"Validate Secure Views" failure	If the required objects do not exist, then the task will fail. Check below if objects are present in the shared database. OMTERRITORY OMSALESFORCE OMTERRITORYHIERARCHY OMUSER OMUSERASSIGNMENT OMACCOUNTTERRITORY OMGEOGRAPHYTERRITORY OMTERRITORYSALESFORCE
3	Intermediate Transformation Views failure	In case of process failure, check the error log to see the error message. Check that table definitions are not changed in the shared database.
4	Stage the Data failure	Check the error log. If it's related to mapping, check the mapping json configuration and validate it.