"DART Cleaner" is the branded name for the OUD suite of DART integrity checks. An integrity check or "audit" refers to any process aimed at correcting a data 'error' within DART data. These checks identify incorrect data, flag it, and then submit it to the necessary person or team for review and cleanup. The majority of integrity check processes run on a monthly basis; however, specific use cases may involve bi-weekly or annual timeframes. Each documented integrity check will specify its frequency of execution.
SQL Server Integrity_Checks Database
The preferred platform for integrity checks is SQL Server (SSMS) in the INTEGRITY_CHECKS database within DB-OUD. Utilizing Jenkins and other integration tools, integrity checks within SSMS can be automated for improved efficiency.
Naming Conventions
Integrity checks within SSMS follow a very strict naming convention: [dbo].[IDS.V-INTEGRITY CHECK-CATEGORY-Name of Check(DW)]
-
[dbo] is the standard database schema
-
IDS is Integrated Data Services
-
V indicates that the object is a view
-
INTEGRITY CHECK is standard/default
-
CATEGORY must be capitalized and be one of the following values: ADDRESS, ADDRESS ATTRIBUTE, AFFILIATION, ATTRIBUTE, PHONE, EMAIL, RELATIONSHIP, PERSONAL INFORMATION, SOCIAL MEDIA, MIMED, SITE, FACULTY/STAFF, REVENUE, RECOGNITION, CONSTITUENT. To request a new category, please contact the IDS team.
-
Name of Check should be concise, precise, and consistent with existing objects
-
(DW) indicates that the object is linked to BBDW data
Integrity Check View Definitions
Integrity check scripts should be saved in the appropriate category folder within S:\IDS\MES\SQL Scripts\DATA QUALITY.
The script that is saved should be the BBDW query - it is unnecessary to save the view definition that writes the view to DB-OUD INTEGRITY_CHECKS*. The script must have a flowerbox that has the (human-readable) category and name, the KB article ID, the name of the INTEGRITY_CHECKS view in the above naming convention, and developer notes for any significant changes made to the script (YYYY-MM-DD uniqname: Description of changes).
* There is a SQL script template in S:\IDS\MES\SQL Scripts\DATA QUALITY\0 Templates that provides the backbone structure of defining the view in DB-OUD INTEGRITY_CHECKS. The BBDW query saved in the file can be pasted into the DW_PROD_LINK OPENQUERY() function to write the view.
Figure 1. Backbone template for defining a view in DB-OUD INTEGRITY_CHECKS, including appropriate document header.
Integrity checks should be written with the final output in mind. The final output should be a batch-able template so DMT can process the file. Please use this website to find DMT templates. Please connect with the DMT team to verify the output if a template does not exist.
All integrity check views must have the same first three columns. The first column is [IC_Record_GUID] and should contain the system ID for the relevant level of detail, usually consistent with the category. The second column is [IC_Run_Date] and will always be `CAST(GETDATE() AS DATE)`. The third column is [DMT_Action] and should describe what fix should be applied to these records so that they are compliant with best practices (e.g., add end-date, switch to primary) - note that this field should start with a verb.
Resolving Identified Integrity Check Issues
Unless otherwise specified in documentation, the results of an integrity check are sent to the Data Management Team (DMT) for resolution. DMT prefers to process fixes if 20 or more records are impacted for improved efficiency and cost-benefit trade-off. As noted above, DMT templates should be used to format this data, which can be submitted to DMT via a TDX ticket.
Documenting Integrity Checks
Knowledge Base Article
Each active integrity check is documented in an individual Knowledge Base article within the Team Dynamix system - an example KB can be found here.
The template for integrity checks can be found here. The documentation should explain the relevant data best practice(s), what the integrity check does, and the technical specifications of how the code works or the process that is used to update the data. Unless otherwise specified, the output of an integrity check will be sent to DMT for resolution.
Integrity Check Repository
Once an integrity check is created and documented, it should be added to the DART Integrity Check Repository on the tab “Active Integrity Check - Community Wide.” If the integrity check is accomplished using SQL Server, it should also be added to the tab “Integrity Check Database.”
Adding the integrity check to this document will feed the DART Cleaner dashboard so the community can view the integrity check.
Integrity Check Scorecard
At this time, an integrity check scorecard is planned for the DART Cleaner dashboard so that the community can see how many integrity check ‘errors’ are identified every month and resolved by the DS3 team. To facilitate this dashboard, a snapshot is run on the first business day of each month that saves the count of records for each integrity check view in DB-OUD INTEGRITY_CHECKS and saves the IC_Record_GUIDs for each integrity check ‘error’ record that is identified. Over time, this will allow us to see the velocity of integrity checks issues and resolutions.
Requesting New Integrity Checks
Community members can request integrity checks by opening a Data Strategy Consultation request.