Add Missing School/College Alumni Affiliation

What Does It Do?

The OUD-DS3 best practice is for UM graduated education records to have an alumni affiliation to the school or college associated with the degree.

This integrity check identifies graduated education history records without any alumni affiliation attributes so that an alumni affiliation attribute can be added that corresponds with the educational college. 

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 SchoolCollege Alumni Affiliation(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries graduated UM degree records* that do not have any associated alumni affiliation attributes. It creates a batch template to create alumni affiliation attributes associated with that education record for each college listed in the education record’s additional information records (majors only). It ignores any schools/colleges that no longer exist (e.g., School of Nat Resource and Env).

It also looks for two special cases for a missing MARSAL alumni affiliation, as defined in the grad load specifications for alumni affiliations: College = Rackham Graduate School; Major = Education & Psychology or Major = English and Education; Degree = Ph.D..

* This excludes the following degrees: A.M.Hon., D.Arch.Hon., D.Astro.Sci.Hon., D.B.A.Hon., D.Eng.Hon., D.F.A.Hon., Ed.D.Hon., H.D.Hon., L.H.D.Hon., Let.D.Hon., LL.D.Hon., M.Eng.Hon., M.S.Hon., Mus.D.Hon., Sc.D.Hon.

Resources and References

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

Snapshot GUID: BBDW.DIM_EDUCATION.EDUCATIONSYSTEMID

Grad Load