Add Missing Campaign Primary Constituency for Faculty/Staff

What Does It Do?

The OUD-DS3 best practice is for all active and retired faculty or staff of an SCCU to have a Campaign Primary Constituency attribute for that SCCU.

This integrity check identifies constituents with an active or retired faculty or staff affiliation to an SCCU who do not have a Campaign Primary Constituency attribute for that SCCU and are not excluded from having that attribute so that the SCCU Campaign Primary Constituency attribute can be added. 

How Is This Done?

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

That view queries constituents with any of the following affiliations without an end date: U-M Faculty - Active, U-M Faculty - Retired, U-M Staff - Active, U-M Staff - Retired to a site that rolls up to an SCCU that has an associated Campaign Primary Constituency attribute value. If those constituents do not already have the attribute and are not excluded from receiving the attribute, the attribute is added to their record.

The DART Queries used to add the attributes on a monthly basis cannot roll-up the affiliation site; the queries have a long list of sites that are associated with the SCCUs, but some sites may be missed. This integrity check serves as a backstop to fill in attributes for the missing sites.

Resources and References

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

Snapshot GUID: BBDW.USR_DIM_Cnst_Affltn.ConstituentSystemID - SCCU, where the SCCU is mapped from the ConstructedSiteRollup of the affiliation site