Add Missing Primary Flag for UM Alumni

What Does It Do?

The OUD-DS3 best practice is for the first completed/graduate degree from UM to be the constituent’s primary degree. If multiple degrees are completed/graduated on the same first day, additional education criteria are used to select which will be primary.

This integrity check identifies UM alumni with no primary degree so that a UM primary degree can be identified on their record.

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-EDUCATION-Add Missing Primary Flag for UM Alumni(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries constituents with any graduated or completed UM degrees where none of their degrees are marked as the primary educational record. It identifies the constituent’s first completed/graduated UM degree record to be marked as the primary. If a constituent has multiple education records with the same graduated date, the degree program is used as a tiebreaker (Bachelors Level > Masters Level > Intermediate > Doctoral Level > Professional Level > Certificate > (other)); if there are still multiple tied education records, the primary is selected by the first school/college alphabetically, then by the education degree alphabetically, and finally by the system ID if needed for a tie-breaker.

Resources and References

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

Snapshot GUID: BBDW.DIM_CONSTITUENT.CONSTITUENTSYSTEMID