Source Layer
The Anthology Student and/or CampusNexus CRM databases serve as the source of data throughout Student Analytics.
Day-to-day changes made to the databases (insert, update, and delete operations) are captured using the Change Data Capture feature of SQL Server 2016/2017/2019/2022 releases.
Anthology Student
To track changes to the required Anthology Student database source tables, change data capture is further enabled for these individual tables with specific lists of columns to be captured. This, in turn, creates new change data capture metadata tables under a new database schema ‘cdc’, called ‘change tables’, for each of the source tables. The change tables serve as a repository for the changes that occur to the data in the individual source tables, supporting incremental updates to the Data Warehouse. Upon upgrading an Anthology Student database with Student Analytics, an additional database file group ‘C2000_FG_CDC_Analytics’ and database file are created to contain the change tables.
Tables Enabled for Change Data Capture
Refer to Anthology Student Tables and Columns enabled for CDC.xlsx for a detailed list of Anthology Student source tables along with the only subset of columns that needed to be tracked. For performance reasons, not all columns are tracked.
Before release of Student Analytics 5.5.2, the installation process involved a verification step to disable Change Data Capture on columns that were not part of the Student Analytics configuration. The introduction of 5.5.2 prevents the disabling of Change Data Capture on columns that are not included in the Student Analytics configuration.
CampusNexus CRM (Not available for Student Analytics on Cloud 2.0)
To track changes to the required CampusNexus CRM database source tables, Change Tracking is further enabled for tables that are being tracked. Change Tracking functions are used to obtain information about the changes.
The Higher Education Foundation (Higher Ed) setup must be installed on the CampusNexus CRM environment. Campaign Support for the Lead object must be installed prior to the installation of CRM Analytics.
Objects and Related Tables Enabled for Change Tracking
The tables of the following CampusNexus CRM objects are used as source tables for the Data Warehouse to support the current model.
| ObjectName | TableName | ObjectName | TableName |
|---|---|---|---|
| Area of Interest | tblObjectType20018 | Education Level | tblObjectType20016 |
| Area of Study | tblObjectType20023 | Enumerations | tblEnum |
| Campaign | tblCampaignMain | tblEnumLangName | |
| tblCampaignAction | Ethnic Group | tblObjectType20029 | |
| tblURLClickStatus | Lead | tblObjectType20005 | |
| tblMailOpenRecord | tblObjectType20005_x | ||
| tblOBMReportMailer | Lead Source | tblObjectType20015 | |
| tblSMSCampaignDetails | Lead Type | tblObjectType20021 | |
| tblSMSDetails | Mailer | tblOutBoundMailer | |
| tblSMSReport | Nationality | tblObjectType20030 | |
| tblCampaignTarget | Program | tblObjectType20008 | |
| tblURL | Program Level | tblObjectType20020 | |
| tblTrackableURLClickRecord | Program Version | tblObjectType20009 | |
| Contact | tblCustomer | tblObjectType20009_x | |
| tblCustomer_SisConnector | SIS User | tblObjectType20036 | |
| tblObjectType3_x | State | tblObjectType20017 | |
| Country | tblObjectType20014 | Team | tblTeam |
| Dependency | tblDependency | Term | tblObjectType20007 |
Note: The “_x” suffixed to a table name indicates one or more underlying secondary tables of an Object.
CDC Capture and Cleanup Jobs
Student Analytics on Cloud 1.0 and On-Premise SQL Databases are typically associated with two SQL Server Agent jobs: one to populate the change tables (i.e., the “capture” job), the other to be responsible for change table cleanup (the “cleanup” job). Both the capture and cleanup jobs are created using default parameters when the Change Data Capture feature is enabled.
-
The capture job is started immediately. It runs continuously, processing a maximum of 1000 transactions per scan cycle with a 5-second waiting period between cycles.
-
The cleanup job runs daily at 2 a.m. It retains change table entries for 4320 minutes (3 days), removing a maximum of 5000 entries with a single delete statement.
For additional information about SQL Server Agent jobs, refer to the MSDN Library topic Change Data Capture Agent Jobs.
Student Analytics on Cloud 2.0 Azure SQL Database automatically manages the CDC process through background jobs, including a "capture job" that regularly reads the transaction log to populate the change tables, and a "cleanup job" that periodically removes old change data to optimize storage.
-
The CDC capture job in Azure SQL Database operates seamlessly, running every 20 seconds to track changes efficiently and simultaneously.
-
The cleanup job runs every hour, ensuring your CDC tables remain optimized. Users can be rest assured that CDC management occurs automatically without manual intervention.
For additional information refer to Microsoft topic Change data capture (CDC) with Azure SQL Database.
