Body
|
Category: Site
|
Frequency: Annually
|
Platform: SQL Server
|
Owner: IDS
|
What Does It Do?
The OUD-DS3 best practice is for sites (locations at UM that are part of the administrative hierarchy) to be consistent with the Financials and Physical Resources System and to only have a single record.
This integrity check identifies sites that are duplicated in DART (by site ID) so that all associated data can be migrated to one record and the duplicated site record can be deleted.
How Is This Done?
This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-SITE-Duplicated Site IDs(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.
That view queries all site records where the site ID is associated with more than one site record.
The Business Objects report called “DART Duplicate Site Cleanup”, which is located in UM-Maintained/Development/OUD IDS Team Reports/_Internal Team Reports for Community/Data Strategy Reports/Integrity Checks, is used to pull all data related to the sites: other sites, designations, constituent affiliations, educational involvements, interactions, plans, and solicit codes. The saved export of this report serves as a snapshot of the data impacted by the duplicate site records.
If the sites have different paths in DART, we keep the one that is consistent with the Financials & Physical Resources System; otherwise, we keep the one that has more records attached to it.
All of the duplicate-site-associated data is first deleted from DART by DMT; the BO report used to pull the data is consistent with the DMT batch templates needed to delete and re-add the data.
After the duplicate-site-associated data has been deleted from DART, a ticket is submitted to ITS-4HELP with the site GUID to delete the site - DS3 is unable to delete sites from DART.
Finally, DMT re-adds the snapshotted data from the duplicated and deleted site to the retained site.
Resources and References
Locate a Site Within the University Hierarchy Using the Financials and Physical Resources System
S:\IDS\MES\SQL Scripts\DATA QUALITY\SITE
Snapshot GUID: BBDW.DIM_SITE.SITEID (this is not a GUID, but does effectively capture which records are duplicated)