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, Development Major Gifts Prgm, 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’