Add Missing Campaign Primary Constituency for Medical School Faculty and Staff

What Does It Do?

The OUD-DS3 best practice is for constituents with a connection to a unit to have a campaign constituency attribute to identify that connection to improve the ease of reporting.

This integrity check identifies constituents that meet the definition for the Medical School faculty/staff but do not have a campaign primary constituent attribute for Medical School so that the attribute can be added. Due to the complex nature of MED roll-ups, this is done in addition to the monthly process that creates these constituency attributes for all SCUs.

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

That view queries constituents that do not already have the Medical School Campaign-Primary Constituency attribute and who do not have the ‘Not a Major Giving Prospect’ or ‘PDA Research Exclusion’ affiliations for UM, PDA, VP Development, or Medical School, and who are active, former, and retired faculty and staff of Medical School with a current affiliation.

Resources and References

DART Campaign Constituency Best Practices - OUD, Integrated Data Services

Unit Constituencies - Definition Datapoints

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

Snapshot GUID: BBDW.DIM_CONSTITUENT.CONSTITUENTSYSTEMID + ‘-Medical School’