Important MDM SQL Queries

This article provides important queries used for daily activities in the Informatica MDM. This article will continuously keep updating for interesting and important SQL queries which are used in day to day activities in MDM.

Survivorship Verification

Order of survivorship is a very important concept in the Informatica MDM. As per Informatica below is the order of precedence, 

1. By trust score (only if a column is trust-enabled). The data with the highest trust score wins. If the trust scores are

equal, or if trust is not enabled for a column, then proceed to the next comparison.

2. By SRC_LUD in the cross-reference(XREF) record. The data with the more recent cross-reference SRC_LUD value

wins. If the SRC_LUD values are equal, then proceed to the next comparison.

3. By ROWID_XREF in the cross-reference. ROWID_XREF values are evaluated in numeric descending order. The

data with the highest ROWID_XREF wins.

In order to verify the order of precedence working correctly or not, use below query -

select P.party_name, x.party_name as party_nm, x.rowid_object, x.orig_rowid_object, x.rowid_system,x.src_lud, x.rowid_xref, x.LAST_UPDATE_DATE,
rank() over (partition by x.rowid_object order by x.rowid_system desc , x.src_lud desc, cast(x.orig_rowid_object as decimal) desc , cast(x.rowid_xref as decimal) desc)) as r1 
from 
    CMX_ORS.C_BASE_PARTY P, 
    CMX_ORS.C_BASE_PARTY_XREF X 
where p.rowid_object = x.rowid_object