Review Constituents with Multiple @UMich.EDU Emails

What Does It Do?

The OUD-DS3 best practice is for constituents to have one @umich.edu email.

This integrity check identifies constituents with multiple valid @umich.edu emails so that they can be reviewed for correctness, and any incorrect emails can be inactivated and marked as not belonging to that constituent.

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-EMAIL-Review Multiple atUMichEDU Emails(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries constituents who have multiple unique valid (not DNE) @umich.edu email addresses. The output is in “wide” format, such that each constituent is listed once, and their email address, primary flag, start date, and information source is listed out for as many valid @umich.edu emails as they have. The emails are sorted by whether the prefix of the email matches the constituent’s uniqname and then whether the email address is their primary.

The output of this view is not batchable because it is meant to be reviewed. 

Resources and References

S:\IDS\MES\SQL Scripts\DATA QUALITY\EMAIL

Snapshot GUID: BBDW.FACT_ConstituentEmailAddress.ConstituentSystemID