DronaBlog

Wednesday, April 1, 2020

Top 5 indicators in the Informatica MDM

Are you looking for details about what are the different types of indicators used in the Informatica Master Data Management (MDM) system? Are you also interested in knowing what are the valid values for these indicators and what those values mean? If so, then you reached the right place. In this article, we will understand different indicators such as HUB_STATE_IND, CONSOLIDATION_IND, etc and their values in detail.

Indicators in the Informatica MDM:
Informatica MDM maintains several types of indicators and those are used during internal MDM processing. The indicators maintained in the MDM system are

1. HUB_STATE_IND
2. CONSOLIDATION_IND
3. DIRTY_IND
4. DELETED_IND
5. AUTOMERGE_IND






A)  HUB_STAE_IND indicator
This field present in BO, XREF tables. These indicator fields represent whether the record is in the active, deleted or pending state.
   
Value
Meaning
1
Active Record
0
Pending Record
-1
Inactive Record


B)   CONSOLIDATION_IND indicator

This filed present in the BO table. This indicator field represents whether the record is gone through the match process or not.

Value
Meaning
4
The new record (Unmerged record)
3
The record has gone through the match process and ready for consolidation
2
Queued for the Merge process
1
Consolidated or Golden record
9
The record is on hold. Normally data steward keep records on hold





C)  DIRTY_IND indicator

This field present in the BO table but it is no more in used. It was used for the tokenization process in the earlier release. But now instead of this field, <BO>_DRTY table is used for the tokenization process. Valid values are 1 and 0 for this field. 0 means record is ready for tokenization and 1 means record went through tokenization process.


D)   DELETED_IND indicator

This field present in BO and XREF Tables. It is reserved for future purposes.


E)   AUTOMERGE_IND indicator

This field present in MTCH and HMRG tables. The valid values are 0 and 1.

Value
Meaning
1
Records are queued for auto-merge
0
Records are queued for manual merge






Monday, February 3, 2020

Informatica MDM - Important SQL Query: How to pull all the records from HMRG Table

There are some business use cases during which you may need to analyze data from the HMRG table i.e. History of Merge. Assume that you know the match rule number and Match rule set name then you can use the query below to pull records specific to match rule number from HMRG table.







select * from cmx_ors.c_bo_party_hmrg where rowid_match_rule in
(select rowid_match_rule from cmx_ors.c_repos_match_rule where rowid_match_set in (
select rowid_match_set from cmx_ors.c_repos_match_set where match_set_name='ORG_IDL') and rule_no=1
)



In this query,
rule_no=1 is a rule number from MDM hub for which we are looking for information
match_set_name='ORG_IDL' is a match set name from MDM hub under which rule_no=1 is present.


The above query will result in all records which satisfy the condition. We can join the result with parent party table and fetch other business attributes as per business needs.

Informatica MDM - The differences between Subject Area based IDD Application and Entity 360 application

Are you looking for details about the differences between Subject Area based IDD Application (aka Legacy IDD Application) and Entity 360 application? Would you be interested in knowing what are the limitations of Entity 360 application? Are also interested to know what are the great features of Entity 360 application? If so, then you reached right place. In this article we will discuss the differences between Subject Area based IDD application and Entity 360 application.








Category
IDD Data View
IDD Business Entity
Customization
Use of IDD User Exits
1.       Integral part of IDD Configuration
2.       Easy to implement customization
3.       Easy to deploy as component of IDD
4.       No separate resource configuration required, resources allocated to IDD will be used for User Exist
5.       Error handling follows MDM standard practice, no additional handling is required
6.       No additional security required as it integral part of IDD Application
No User Exit support
1.       Need to write external services (Restful or SOAP based Web Services)
2.       Required additional efforts to build and implement and deploy these external services
3.       For scalability, high availability of external services, additional dedicated servers are required
4.       Need to apply and maintenance security  as these are external services to IDD Business entity
5.       Extra error handling is required to follow MDM standard practice
6.       Extra configuration is required to call external services
7.       Dedicated resources need to be allocated to handle user requests
Fuzzy Search
Extended search functionality using MDM Match Engine to achieve fuzzy search
Elastic search uses Synonym properties file to achieve fuzzy search.
Note: We need to maintain fuzzy keywords in the Synonym file in order to Fuzzy search work.
Data Import template
IDD Data View provides feature to import data. It is very helpful tool when business would like to import bulk data in need basic. No need to create or update requires manually
Do not support Bulk import template.
Need to create or update bulk volume of data manually
Unmerge functionality
It supports both Tree unmerge and linear unmerge.
Note: During Tree unmerge unmerged unmerge records get separated from group. During linear unmerge children records of unmerged record remain associated.
Supports only Tree unmerge
Report
Easy to integrate repots in the IDD application using Jasper Reports
Jaspersoft reports work in a Home page only if it is the only component in the Home page.
Workflow
If IDD application includes workflows, we must generate the business entity schema as a requirement for Data Director to manage the workflow tasks. However, we need to migrate to business entities
The business entity schema will be generated as part of Business Entity application publish event using Provisioning Tool.
Both Entity and IDD Data Views- Hybrid mode
Informatica recommends that you the Hybrid mode only on a temporary basis
Manual Override of matched record
Manual override  of a value in the Matches view is allowed
Manual override a value in the Matching Records view is not allowed
Hierarchy View
Hierarchy relationships can be configured to show in a section to show duplicate hierarchy records.
The Hierarchy view does NOT permit the following actions:-
·         Finding a duplicate entity.
·         Initiating a merge.
·         Sharing a bookmark URL
Limitations
1.       In the task inbox on the Home page, you cannot filter tasks by the creation date.
2.       When you export search results that are based on a timeline, the export process ignores the timeline and exports all data.
1.       The Cross Reference page and the Merge Preview page have pagination issues.
2.       In the search results, some rows are empty. The rows represent records that are filtered out because the user does not have permission to view the records.
3.       When a user role does not include the create and read privileges for a business entity, users with this role can still view the tasks associated with the business entity.
4.       In the History view, the timescale labels in the Options menu do not appear correctly initially.
5.       In the Hierarchy view, business entities in the Relationships tab of the history do not open in Business Entity view.
6.       In the Timeline view, you cannot open the relationship records that appear on the Relationships tab.
7.       In the Hierarchy view, in the Entity Details dialog box, when you click More Details, the dialog box closes without opening the selected business entity.
8.       In the Matching Records view, when you merge records, the system can appear unresponsive.
9.       If you delete a record and then search for the record, the ROWID of the deleted record still displays.
10.   In the History view, when you try to view an event detail, an error might occur.





Tuesday, January 21, 2020

Informatica MDM - How to generate match report?

Once you are done with a running match job, if you would like to create a match report so that business users can review it before finalizing it then you can use the below query to generate a match report. The SQL query can be tuned to accommodate your data model changes. The result of the match report includes attributes from the Party as well as the child tables. You can adjust the result parameters as per your needs.





WITH
CTE_GOLDEN_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT ROWID_OBJECT_MATCHED AS GR_GROUP_ID, ROWID_OBJECT_MATCHED AS ROWID_OBJECT, 'FIRST_RECORD' AS RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM (  SELECT DISTINCT ROWID_OBJECT_MATCHED, ROWID_MATCH_RULE, AUTOMERGE_IND, ROW_NUMBER() OVER(PARTITION BY ROWID_MATCH_RULE ORDER BY DBMS_RANDOM.VALUE) RANDSEL
          FROM CMX_ORS.C_BO_PARTY_MTCH where
          ROWID_OBJECT_MATCHED in (select rowid_object from cmx_ors.c_bo_party
                where PARTY_ROLE_TYPE='ACCT' )
        ) GR
       
  WHERE RANDSEL <= (SELECT CEIL(30000/COUNT(RULE_NO)) AS RULE_FETCH_COUNT
                   FROM CMX_ORS.C_REPOS_MATCH_RULE
                   WHERE MATCH_FOR_ROWID_TABLE = ( SELECT ROWID_TABLE FROM CMX_ORS.C_REPOS_TABLE WHERE TABLE_NAME = 'C_BO_PARTY')
                          AND RULE_ENABLED_IND = 1)

),
CTE_SUSPECT_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT SR.ROWID_OBJECT_MATCHED, SR.ROWID_OBJECT, 'OTHER_RECORD' AS RECORD_TYPE, SR.ROWID_MATCH_RULE, SR.AUTOMERGE_IND
  FROM CMX_ORS.C_BO_PARTY_MTCH SR
  JOIN CTE_GOLDEN_RECORDS GR ON GR.ROWID_OBJECT = SR.ROWID_OBJECT_MATCHED AND GR.ROWID_MATCH_RULE = SR.ROWID_MATCH_RULE
),
CTE_TRANS_SUSPECT_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT SR.ROWID_OBJECT_MATCHED, SR.ORIG_ROWID_OBJECT_MATCHED, 'OTHER_RECORD' AS RECORD_TYPE, SR.ROWID_MATCH_RULE, SR.AUTOMERGE_IND
  FROM CMX_ORS.C_BO_PARTY_MTCH SR
  JOIN CTE_GOLDEN_RECORDS GR ON GR.ROWID_OBJECT = SR.ROWID_OBJECT_MATCHED AND GR.ROWID_MATCH_RULE = SR.ROWID_MATCH_RULE
),
CTE_MATCH_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM CTE_GOLDEN_RECORDS
 
  UNION ALL
 
  SELECT GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM CTE_SUSPECT_RECORDS
 
  UNION ALL
 
  SELECT GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM CTE_TRANS_SUSPECT_RECORDS
),
CTE_PARTY_IDENTIFICAITON (ROWID_OBJECT, LINK_ID, TIN, EID, MBR_ID, ACCT_ID) AS
(
  SELECT ROWID_PARTY, LINK_ID_IDFCTN_NBR, TIN_IDFCTN_NBR, EID_IDFCTN_NBR, MBR_ID_IDFCTN_NBR, ACCTID_IDFCTN_NBR
  FROM (SELECT PR.ROWID_PARTY, PI.IDFCTN_TYPE_CODE, PI.IDFCTN_NBR
        FROM CMX_ORS.C_BO_PARTY_ROLE PR
        JOIN CMX_ORS.C_BO_PARTY_ROLE_IDFCTN PI ON PR.ROWID_OBJECT = PI.ROWID_PARTY_ROLE
        JOIN CTE_MATCH_RECORDS MR ON MR.ROWID_OBJECT = PR.ROWID_PARTY
        ) PI
  PIVOT (
          MAX(IDFCTN_NBR) AS IDFCTN_NBR FOR IDFCTN_TYPE_CODE IN ('LINK ID' AS LINK_ID, 'TIN' AS TIN, 'EID' AS EID, 'MBR_ID' AS MBR_ID, 'Account Number' AS ACCTID)
        )
),
CTE_PARTY_ADDRESS (ROWID_OBJECT, RESIDENT, CORRESP) AS
(
  SELECT ROWID_PARTY, RESIDENTIAL_ADDRESS_VALUE, CORRESPONDENCE_ADDRESS_VALUE 
  FROM (  SELECT PR.ROWID_PARTY, PRA.ADDR_TYPE_CODE, POADDR.ADDR_LINE_1, POADDR.ADDR_LINE_2 , POADDR.ADDR_LINE_3 , POADDR.CITY, POADDR.ZIP_POSTAL_CODE
  , POADDR.STATE_CODE , POADDR.CNTRY_CODE
          FROM CMX_ORS.C_BO_PARTY_ROLE PR
          JOIN CTE_MATCH_RECORDS MR ON MR.ROWID_OBJECT = PR.ROWID_PARTY
          JOIN CMX_ORS.C_BO_PARTY_ROLE_ADDR PRA ON PR.ROWID_OBJECT = PRA.ROWID_PARTY_ROLE         
          JOIN CMX_ORS.C_BO_POSTAL_ADDR POADDR ON PRA.ROWID_ADDR = POADDR.ROWID_OBJECT
        ) PRA
  PIVOT (
          MAX(ADDR_LINE_1 || ' ' || ADDR_LINE_2 || ' ' || ADDR_LINE_3 || ' ' || CITY || ' ' || ZIP_POSTAL_CODE || ' ' ||  STATE_CODE || ' ' || CNTRY_CODE) AS ADDRESS_VALUE
            FOR ADDR_TYPE_CODE IN ('RSDNTL' AS RESIDENTIAL, 'CRSPDC' AS CORRESPONDENCE)
        )
),
CTE_PARTY_PHONE (ROWID_OBJECT, HOME, MAIN, WORK, MOBILE) AS
(
  SELECT ROWID_PARTY, HOME_PHONE_NUM, MAIN_PHONE_NUM, WORK_PHONE_NUM, MOBILE_PHONE_NUM
  FROM (  SELECT PR.ROWID_PARTY, PP.PHONE_USG_TYPE_CODE, PP.CNTRY_CODE, PP.AREA_CODE, PP.PHONE_NBR, PP.EXTN_CODE
          FROM CMX_ORS.C_BO_PARTY_ROLE PR
          JOIN CMX_ORS.C_BO_PARTY_ROLE_PHONE PP ON PR.ROWID_OBJECT = PP.ROWID_PARTY_ROLE
          JOIN CTE_MATCH_RECORDS MR ON MR.ROWID_OBJECT = PR.ROWID_PARTY
          ) PRP
  PIVOT (
          MAX(CNTRY_CODE || ' ' || AREA_CODE || ' ' || PHONE_NBR || ' ' || EXTN_CODE ) AS PHONE_NUM
            FOR PHONE_USG_TYPE_CODE IN ('HOME_PHONE' AS HOME, 'PHONE' AS MAIN, 'WORK_PHONE' AS WORK, 'MOBILE' AS MOBILE)
        )
)

SELECT DISTINCT
  MR.GR_GROUP_ID,
  MR.ROWID_OBJECT AS MDM_PARTY_ID,
  MR.RECORD_TYPE,
  RL.RULE_NO AS RULE_DESCRIPTION,
  MR.AUTOMERGE_IND AS AUTO_MERGE,
  PARTY_XR.ROWID_SYSTEM AS SOURCE,
  PARTY_XR.PKEY_SRC_OBJECT AS SOURCE_KEY,
  PARTY.PARTY_TYPE_CODE AS PARTY_TYPE,
  PARTY.PARTY_ROLE_TYPE AS ROLE_TYPE,
  PARTY.ORG_NAME AS ORG_NAME,
  PARTY.ACCT_TYPE_CODE AS ACCT_TYPE_CODE,
  PARTY.ACCT_LVL_CODE AS ACCT_LVL_CODE, 
  PARTY.DISPLAY_NAME AS FULL_NAME,
  PARTY.FIRST_NAME ,
  PARTY.MID_NAME ,
  PARTY.LAST_NAME ,
  PARTY.DOB AS BIRTH_DATE,
  PARTY.GENDER_CODE AS GENDER,
  PARTY.INDUSTRY_CLS_CODE,
  PARTY.ACCT_NBR,
  PI.LINK_ID,
  PI.TIN TIN,
  PI.EID EID,
  PI.MBR_ID AS MEMBER_ID,
  PI.ACCT_ID AS ACCT_ID,
  PA.RESIDENT AS RESIDENTIAL_ADDR,
  PA.CORRESP AS CORRESPONDENCE_ADDR,
  PP.HOME AS HOME_PHONE,
  PP.MAIN AS MAIN_PHONE,
  PP.WORK AS WORK_PHONE,
  PP.MOBILE AS MOBILE_PHONE
 
FROM CTE_MATCH_RECORDS MR
JOIN CMX_ORS.C_REPOS_MATCH_RULE RL ON RL.ROWID_MATCH_RULE = MR.ROWID_MATCH_RULE
JOIN CMX_ORS.C_BO_PARTY PARTY ON PARTY.ROWID_OBJECT = MR.ROWID_OBJECT
JOIN CMX_ORS.C_BO_PARTY_XREF PARTY_XR ON PARTY_XR.ROWID_OBJECT = PARTY.ROWID_OBJECT
LEFT JOIN CTE_PARTY_IDENTIFICAITON PI ON PI.ROWID_OBJECT = MR.ROWID_OBJECT
WHERE PARTY_XR.ROWID_OBJECT = PARTY_XR.ORIG_ROWID_OBJECT
LEFT JOIN CTE_PARTY_ADDRESS PA ON PA.ROWID_OBJECT = MR.ROWID_OBJECT
LEFT JOIN CTE_PARTY_PHONE PP ON PP.ROWID_OBJECT = MR.ROWID_OBJECT
ORDER BY GR_GROUP_ID, RULE_NO, RECORD_TYPE;




Monday, January 13, 2020

Informatica Active VOS - How to delete all the tasks

Suppose you are building MDM hub and Active VOS components e.g. configuration of Merge tasks or Update tasks. During the development phase, you may come across a situation when you may want to delete all the tasks. You can use Active VOS console and delete one task at a time or group of tasks at a time. But you cannot delete all the tasks in a single action using Active VOS console. In such a case, how can we delete all the tasks? In this article, we are going to see a simple and easy way of deleting all the tasks.







Tables involved in the Active VOS tasks:

There are 3 tables in AVOS schema which maintains the tasks related details and these tables are
1. AeProcessJournal
2. AeProcess
3.AeProcessLogData

As part of deleting all the tasks, we need to deal with these tables.





Delete records from AeProcessJournal:

First, delete records from AeProcessJournal using the delete statement below.

DELETE FROM AVOS.AeProcessJournal table WHERE ProcessId IN (SELECT ProcessId FROM AVOS.AeProcess);
COMMIT;

Delete records from AeProcessLogData:

Then, delete records from AeProcessLogData table using the delete statement below.

DELETE FROM AVOS.AeProcessLogData WHERE ProcessId in (SELECT ProcessId FROM AVOS.AeProcess);
COMMIT;




Delete records from AeProcess:

Finally, delete records from AeProcess table using the delete statement below.
DELETE FROM AVOS.aeprocess;
COMMIT;



How to delete orphan task:


You can download bpr file to delete orphan tasks and deploy it as a workflow in the ActiveVOS console. The details available at Informatica KB


Are you interested in knowing more about AVOS then you can refer to this video?


What is Glue Job in AWS?

An AWS Glue job is a managed ETL (Extract, Transform, Load) job used to process data in AWS. AWS Glue makes it easy to discover, prepare, an...