Wednesday, December 12, 2018

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 
where p.rowid_object = x.rowid_object

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

What is Dynamic Data Masking?

                        Are you looking for details about Dynamic Data Masking? Are you also interested in knowing what are the things we ne...