Matching Data Between Two Sources Using Part of a String

The illustration below explains different scenarios to match data between two sources using part of a string.

Let us understand the requirement with an example. Click on each image if needed to open a larger view of the image.

The MASTER_LIST source contains a list of strings that need to be searched against the BANK_LIST source. The two sources are shown below.

For instance, if the search string is 'INDIA' in the MASTER_LIST source, the matching rows in the BANK_LIST source will be 'Indian Bank' and 'STATE BANK OF INDIA' since both bank names contain the string 'INDIA'. The resultant rows will be loaded into the BANK_MASTER table as shown below.

The matching between the two sources should not be case-sensitive.


Scenario 1: When both the sources are relational tables and reside in the same database. The database for this example is Oracle.

The INSTR function returns the location of a substring in a string. If the INSTR function returns '0', it implies, the substring is not present in the original string. A SQL query that matches the data between the two sources is given below.

SELECT
mstr.master_id,
bank.bank_id,
bank.bank_name
FROM
master_list mstr, bank_list bank
WHERE
INSTR(bank.bank_name, mstr.search_string) > 0

The above query returns only one row as shown below.

The query should not be case-sensitive and it can be re-written as below by converting both the string (bank.bank_name) and the substring (mstr.search_string) to lower case. This query should be added to the Sql Query section in the Source Qualifier properties.

SELECT
mstr.master_id,
bank.bank_id,
bank.bank_name
FROM
master_list mstr, bank_list bank
WHERE
INSTR(LOWER(bank.bank_name), LOWER(mstr.search_string)) > 0

The mapping is a simple pass-through mapping as shown below.

The final output will be the same as shown in the BANK_MASTER table below with the desired four resultant rows.

Scenario 2: When both the sources are flat files or one source is a flat file and the other is a relational table.

In this example, both the sources are flat files. The mapping implementation is shown below.

After, the Source Qualifier transformations, create two expression transformations as shown above. Create two output ports MASTER_KEY and BANK_KEY in the expression transformations EXP_Master_List and EXP_Bank_List respectively and in the expression editor pass the integer value '1'. These values will serve as a dummy join to merge the rows from both the flat files in the joiner transformation.


In the joiner transformation, designate the MASTER_LIST source as the "Master" source since it has fewer rows as compared to the BANK_LIST source. The Joiner condition is shown below. The Join type is "Normal Join".

The joiner transformation essentially does a full outer join i.e. all the rows between the two sources are matched with each other. We need to select only those rows that meet the defined criteria. This is achieved by using a filter transformation with the Filter Condition as shown below.

Scenario 3: When both the sources are relational tables but reside in different databases.



A similar approach to Scenario 2 can be used in this case too, but it would mean joining all the rows between the two source tables in the joiner transformation. Rather we can issue a query to the BANK_LIST table similar to the query in Scenario 1 except that the query won't have the MASTER_LIST table since it is in a different database.


The SQL transformation can be used to process queries midstream and to get the matching rows from the BANK_LIST table.



The source definitions for MASTER_LIST and BANK_LIST tables are shown below. Both the tables are in separate databases and there exists no DB links either between the two databases.

The BANK_MASTER target definition is shown below.

Create a new mapping. Drag the MASTER_LIST source definition and BANK_MASTER target definition into the Mapping Designer workspace as shown below.

Create a SQL transformation SQL_Get_Bank_Details as shown below. Click Create to proceed.

Proceed with the default settings as shown below. Click OK and Done to continue.

The SQL transformation needs to be run in the Query Mode since the SQL queries issued to the BANK_LIST table will be dynamic i.e. since the MASTER_LIST table contains two search strings 'INDIA' and 'AMERICA', two queries will be issued as given below.



SELECT BANK_ID, BANK_NAME
FROM
BANK_LIST
WHERE
INSTR(LOWER(BANK_NAME), LOWER('INDIA')) > 0;


SELECT BANK_ID, BANK_NAME
FROM
BANK_LIST
WHERE
INSTR(LOWER(BANK_NAME), LOWER('AMERICA')) > 0;


Drag the MASTER_ID and SEARCH_STRING ports from the Source Qualifier to the SQL transformation as shown below.

Double click on the SQL transformation to edit it. Go to the SQL Ports tab. Uncheck the SEARCH_STRING as an output port since it is not required in the target as shown below. Only the MASTER_ID needs to be passed to the target, so it remains as an Input/Output port.

Add two SQL output ports BANK_ID and BANK_NAME as shown below ensuring that the correct Native Type and Precision are selected for each.

Next click on the section highlighted in red above to open the SQL Editor that will contain the SQL query that gets issued midstream. Type the query as shown below. Ensure that the order of the fields in the SELECT clause match the order of the SQL output ports.

Now, since the 'SEARCH_STRING' needs to change dynamically as shown in the two queries above, we need to use String Substitution. Click on the SEARCH_STRING port below String Substitution to add it to the query as shown below.

Modify the query as shown below, so that it matches the above two queries that need to be issued to the BANK_LIST table.

Click OK to continue. Link the MASTER_ID_output, BANK_ID and BANK_NAME ports from the SQL transformation to the target definition. The complete mapping is shown below.

In the session task, mention the correct relational connections. A relational connection (Database_B) needs to be specified for the SQL transformation too. As shown below, the MASTER_LIST table is in Database_A, BANK_LIST table is in Database_B and BANK_MASTER target table is in Database_C.

Creating a SCD Type 2 mapping using the Informatica PowerCenter Mapping Wizard

The Mapping Wizard available in the Informatica PowerCenter Designer client provides pre-designed mapping templates to create mappings based on specific requirements like SCD Types 1, 2 & 3.

The example below explains the creation of an SCD Type 2 mapping using the Mapping Wizard. The source table is EMPLOYEES that contains employee information like Employee ID, Name, Role, Department ID, Location, Employment Status and the Date of joining.

The EMPLOYEES table is shown below.

EMPLOYEES
EMP_ID	EMP_NAME	EMP_ROLE	DEPT_ID	LOCATION	EMPL_STATUS	JOIN_DT
1321	Shaun Mathews	Clerk	209	Atlanta	Active	13-Apr-08
1487	Shane Smith	Supervisor	110	Atlanta	Active	4-Aug-08
1678	Katie Wells	Manager	198	Atlanta	Active	20-Aug-08

The field EMP_ID is the primary key for the EMPLOYEES table. The fields on which history needs to be maintained are EMP_ROLE, DEPT_ID, LOCATION and EMPL_STATUS.

Import the source definition EMPLOYEES using the Source Analyzer workspace. Go to Sources > Import from Database.

This opens the Import Tables window. Assuming that a system DSN is already created for this connection, specify all the necessary details and click Connect.

Select the EMPLOYEES table to import and click OK to continue.

The EMPLOYEES source definition is created and appears in the workspace. Click Save to save the source definition in the repository.

The source table EMPLOYEES contains only current data and doesn't have any historical data. This mapping would be run daily to capture the historical data in the EMPLOYEES_SCD2 target table. The Effective Date logic would be used for SCD Type 2 mapping.

Click on the Mapping Designer tab.

Go to Mappings >  Wizards > Slowly Changing Dimensions.

Provide a suitable mapping name as shown and select the Type 2 Dimension radio button. Click Next to continue.

Select the correct Source definition from the Select Source Table drop-down list and type the New Target Table name as EMPLOYEES_SCD2 as shown. Click Next to continue.

This opens the Target Field Selection window as shown.

Add the EMP_ID field as Logical Key Fields as shown as it is the primary key in the EMPLOYEES source table and it will be a part of the Lookup Transformation condition to check if the employee record is present in the EMPLOYEES_SCD2 target table.

Add the remaining fields on which history needs to be maintained as Fields to compare for changes as shown.

Click Next. Select Mark the dimension records with their effective date range as the versioning method to maintain history.

This adds two more fields PM_BEGIN_DATE and PM_END_DATE to the EMPLOYEES_SCD2 target table, which helps identify the effective start date and the end date respectively for each employee's record and if any of the fields on which history needs to be tracked undergo a change in the source table, a new record for that employee will be created with new effective start date and end date will be null. The PM_END_DATE value will be null for all current version of the records in the EMPLOYEES_SCD2 table. The date logic is indeed useful in scenarios wherein the source system doesn't have an effective date or last updated date field and it is binding on the ETL system to provide such a function. However, the definiteness of the effective start and end date being loaded into the history table will also depend on the frequency at which the SCD Type 2 mapping is run.

Click Finish.

The SCD Type 2 mapping is generated.

Save the mapping in the repository by pressing Ctrl+S. Check the Output Window below which displays messages stating that the mapping is valid with no parsing errors.

The new target definition EMPLOYEES_SCD2 is created in Informatica Designer, but not in the database.

Drag the target definition EMPLOYEES_SCD2 from the Repository Navigator into the Target Designer workspace.

Go to Targets > Generate/Execute SQL.

This opens the Database Object Generation window. Mention the path and filename for the DDL file to be created. Select the Create table radio button under the Generation options. Select the Create index radio button.

Click Generate and execute.

This opens the Connect to an ODBC Data Source window. Mention the necessary database details where the target table should be created and click Connect.

Check the Output Window to verify if the script has been successfully generated and executed in the database.

Click Close to close the Database Object Generation window.

The iconic view of the mapping is shown below.

A brief description of the transformations used in the mapping is given below.

1.    LKP_GetData: This is a lookup on the target table EMPLOYEES_SCD2 and will compare the incoming data from the EMPLOYEES source table based on the key field EMP_ID with that of the target table, EMPLOYEES_SCD2. All the currently active records in the EMPLOYEES_SCD2 table will have a null PM_END_DATE. Hence, only these records should be compared for changes with the incoming data and therefore, an unconnected input port INPUT_NULL_DATE is also matched with the PM_END_DATE field of the lookup table as part of the Condition. The condition used in the lookup transformation LKP_GetData is shown below.

2.    EXP_DetectChanges: This expression transformation will generate two flags - ChangedFlag and NewFlag. The ChangedFlag will check if the employee information in the EMPLOYEES_SCD2 target table has undergone a change in the source EMPLOYEES table. The NewFlag will check for the occurrence of new employee records in the source EMPLOYEES table.

3.    SEQ_GenerateKeys: This sequence generator generates unique keys for the PM_PRIMARYKEY field in the EMPLOYEES_SCD2 table for both new records and records that have undergone change in the fields on which history is maintained in the EMPLOYEES source table, which will be inserted as a new record in the target table.

4.    EXP_KeyProcessing_InsertNew & EXP_KeyProcessing_InsertChanged: The expression transformations EXP_KeyProcessing_InsertNew & EXP_KeyProcessing_InsertChanged generate the effective start date PM_BEGIN_DATE for the new records and the changed records that are inserted into the EMPLOYEES_SCD2 target table respectively.

5.    FIL_InsertNewRecord, FIL_InsertChangedRecord & FIL_UpdateChangedRecord: The filter transformation FIL_InsertNewRecord passes the new rows if the NewFlag is TRUE while the filter transformations - FIL_InsertChangedRecord & FIL_UpdateChangedRecord passes the changed rows if the ChangedFlag is TRUE.

6.    UPD_ForceInserts, UPD_ChangedInserts & UPD_ChangedUpdate: The update strategy transformations UPD_ForceInserts & UPD_ChangedInserts are used to manage inserts for new rows and changed rows respectively while the UPD_ChangedUpdate is used to update the old version rows based on the PM_PRIMARYKEY field.

7.    EXP_CalcToDate: This expression transformation generates the effective end date PM_END_DATE for the old version of an employee’s record in the EMPLOYEES_SCD2 target table.

The only optimization needed in the mapping is replacing the three filter transformations with a router transformation.

Create a valid session and workflow for this mapping.

Start the Workflow Manager client tool and click on the Task Developer tab. Go to Tasks > Create to create a new task.

This opens the Create Task window. Select the Session task from the drop-down and enter a name for this task as shown below.

Click Create to continue. Select the mapping created in the previous steps to associate with this session.

Click OK to continue. Click Done in the Create Task window.

A new task is created in the Task Developer workspace as shown above. Double click on the session to edit it. Click on the Mapping tab and select the Connections option on the left and apply the correct relational connections as shown below.

Click OK to continue. Right click on the session task and click Validate to validate the session as shown below.

A notification is generated in the Output Window as shown below stating that the session is valid.

Press Ctrl+S to save the session task.

Click on the Workflow Designer tab. Go to Workflows > Create to create a new workflow.

This opens the Create Workflow window. Provide the workflow name as shown below.

Click OK. Drag the session created in the previous steps from the Repository Navigator into the Workflow Designer workspace. Go to Tasks > Link Task.

Link the Start task to the session task as shown below.

Click Ctrl+S to validate and save the workflow.

Assuming the workflow ran for the first time on the 24th March, 2010, the data loaded in the target table is shown below. Click on the image to see the enlarged view.

Observing the data in the target table, it is evident that since all these records are the current version, the PM_END_DATE for all these records are null.

Assuming that the role of Shane Smith changes to Manager and the department ID of Katie Wells changes to 151 in the source system and a new employee, Jim Mason joins the organization on the 25th March, 2010, the EMPLOYEES table is shown below.

EMPLOYEES
EMP_ID	EMP_NAME	EMP_ROLE	DEPT_ID	LOCATION	EMPL_STATUS	JOIN_DT
1321	Shaun Mathews	Clerk	209	Atlanta	Active	13-Apr-08
1487	Shane Smith	Manager	110	Atlanta	Active	4-Aug-08
1678	Katie Wells	Manager	151	Atlanta	Active	20-Aug-08
2050	Jim Mason	Clerk	171	Chicago	Active	25-Mar-10

After the workflow runs on the 25th March, 2010, the data loaded in the target table is shown below. Click on the image to see the enlarged view.

The old version of records for Shane Smith and Katie Wells have been updated with a PM_END_DATE of 25-Mar-10 and two new versions of records having PM_PRIMARYKEY values 5 and 6 and a null value for PM_END_DATE get inserted into the target table. The new record for Jim Mason also gets inserted into the target table with a null value for PM_END_DATE, indicating it is the current version of the record in the target table.