Access Keys:
Skip to content (Access Key - 0)

MITSIS Backfill Documentation

Please note that this page and related pages are being developed as part of the CIM Courses Project and are subject to change.

This article describes the backfill of data from the Container/Template Subject Structure to MITSIS. The MITSIS Backfill was implemented as part of the CIM Courses project.


The MITSIS Backfill was implemented because the scope of the CIM Courses Project did not include repointing systems from MITSIS to the new
Container/Template Subject Structure. Since the Container/Template Subject Structure would become the source for all subject data,
there was a need to backfill that source data to MITSIS.

Backfill Details

MITSIS backfill populates data in container/template tables to "old" MITSIS tables - 

  • scbsu_key - indicates the terms for which a subject is valid.
  • scrsu_var - stores main subject details like titles, units,  meets with subject information and the department and school the subject belongs to.
  • scrrtrm - indicates the terms for which a subject is offerred.
  • screqiv - stores subjects which are equivalent to a subject. This also includes cross-listed subjects. More details on equivalency criteria in section - 5-Year MITSIS Equivalency Rules.
  • scrgmod - grading modes of a subject.
  • scrattr - stores attributes of a subject.

When container/template data is modified by front-end applications, Subject Management API which saves the template data creates an entry in subject_backfill_queue table (alias queue table). In queue table, newly modified data is saved in JSON format in new_data column and subject details before modification is saved in previous_data column. From this JSON data, subjects are backfilled to MITSIS using MITSIS Batch process, MITSIS Backfill On-demand Mule interface (alias on-demand MITSIS API)  or MITSIS Backfill API.  On-demand Mule Interface and API backfills subject immediately at real time and MITSIS batch process runs every few minutes to backfill subject information. 

  • table mapping between container/template structure and "old" MITSIS is explained in Table Counterparts - Old Structure to New Structure in Container/Template Subject Structure.
  • mitsis_backfill_status column in subject_backfill_queue (alias queue table) stores the status of MITSIS backfill. This column reflects the status when backfill is processed by MITSIS Backfill API, Mule interface or Batch Process.
  • all subject types - Administrative, Cross-Reg and Standard subjects are backfilled to MITSIS.
  • only subjects in approved state are backfilled to MITSIS. These subjects could originate from different front-end applications like SCASUBJI or CIMFEED or from XReg service.
  • only subject changes for current Academic Year or earlier are backfilled to MITSIS. Changes for catalog year is backfilled to MITSIS only that year. ie. if current term is 2020FA, changes to subject meant for Catalog year 2021 will be backfilled only in 2021 and not now. 
  • config setting to determine the year for MITSIS backfill is in  scrci_mgmt_config table for config_code = 'mitsisBackfillAllowedUptoYear'. Please note that if backfilled using MITSIS Backfill On-demand Mule interface or MITSIS Backfill API, this year condition is not checked.
  • subjects created from XReg service is currently backfilled using MITSIS Backfill On-demand Mule interface at real time. All other approved subjects created/edited is backfilled to MITSIS using MITSIS batch process. MITSIS Backfill API end point is provided mainly for usage by automated tests and for debugging purposes.
  • screqiv is only partially backfilled by MITSIS backfill. This is because container/template table subject_container_equiv which stores equivalent data  stores equivalency between containers for a period of time cannot be easily backfilled to screqiv (by effective_term) without knowing the history of the subject and its crosslists and equivalencies for that period of time. While editing a subject from SCASUBJI, equivalency for prior terms other than the term for which the subject data is being edited can be changed. So to backfill equivalencies correctly in screqiv (by effective_term), the history of the equivalent subjects ( subjects could be renumbered, subject numbers swapped etc) and their cross-lists and its equivalent subjects are required.  Due to complexity of processing and time constraints, a restriction was added to editing equivalencies of a subject that - equivalency changes of a subject can be done only from the term the subject is edited. ie. if a subject is edited in 2019FA, its equivalent subject changes are also allowed only from 2019FA.  
  • in contrast to equivalents, meets with /scheduling relationship information is correctly backfilled to MITSIS for all terms. 

MITSIS Backfill Data Flow Diagram

Technical Documentation

MITSIS Backfill is implemented using Mule flows in Anypoint Studio IDE. Both MITSIS Batch process, MITSIS Backfill On-demand Mule interface and MITSIS Backfill API uses the same flows and hence processing logic is the same for all except for how it is called. Former is a batch process and picks up records from database at regular intervals while interface and API executes one queueId at a time. When MITSIS batch picks up records, effective_from_term condition is also checked while API and Mule interface ignores this condition and tries to backfill any queueId given to it as long as it is an approved subject. 

When a subject is edited/created, multiple queue records may be created in subject_backfill_queue table. All the generated record(s) need to be processed to reflect the status of the subject correctly in CIS or MITSIS. If any queue record has a processing error in MITSIS all subsequent records for the subject and its related subjects will be blocked and will not be processed until the error is cleared and subject_backfill_error table is cleared for the subject. 

Backfill Processing

  • MITSIS backfill processing processes one subject_backfill_queue record at a time. This record is usually referred to as queue record.
  • for queue record, if mitsis_backfill_status = null or 2 indicates backfill was not done on the record and is ready to be processed. 1 indicates already processed, -1 error etc.. More details under queue section of Backfill Documentation. 
  • checks if the record change needs to be backfilled. If backfill_process_type is null, SR or TP and proposed_change_flag <> Y,  MITSIS backfill is attempted on the record. All other backfill_process_type values RQ, DE etc. are ignored for MITSIS backfill. SR type record is used to backfill meets with/scheduling relationships subjects into scrsu_var table and has different logic as explained in SR Backfill Processing Logic section.
  • data in old MITSIS tables are saved effective a term (effective_term)  and record is inserted into tables only when some column information is different. So when no parameters change for a subject, no record is inserted/updated/deleted in any table for the term till a change happens. Also when a change happens only the specific table where the change occurred changes. 
  • previous_data column (old data column) and new_data column is read from queue record. Note that these columns could have different JSON structure according to backfill_process_type.
  • old data and new data is read and compared to determine if the queue record was a special change operation  - renumber (MR),  master swap (MS) and partial master swap (PMS) or whether the operation is a mere new subject creation or editing an existing subject or deletion of a proposed subject.
  • MITSIS processing then collects existing data in MITSIS tables with data for the subject received from queue. Comparisons are done by java class SourceSubjectToOldSubjectTransformer and helper classes and would return List<BackfillSubject> with BackfillAction type set which indicates whether insert or update or delete of the data should happen for each of the MITSIS tables.
  • operation specified by BackfillAction for each of the BackfillSubject objects are then executed by flows in backfill-mitsis.xml so the change is reflected in all MITSIS tables.
  • records are created in subject_backfill_log to indicate the successful processing of the subject.
  • Subjects with Arranged units must have a value of "1" in the Lab Units field and all other units are backfilled as 0.
  • Lab Institute Requirements are backfilled as explained in LAB/LAB2/LB Attribute Mapping in Backfill Documentation.

SR Backfill Processing

When subjects with scheduling relationships are created or edited, Subject Management API creates an SR (Scheduling Relationship)  record in subject_backfill_queue table. This record is created in addition to the regular queue record and stores the history of scheduling relationships in JSON format in the new_data column in queue table. This record is processed differently.

  • SR record is used to backfill meets with/scheduling relationships of subjects into scrsu_var table in MITSIS database.
  • If there are any errors in subject_backfill_errorkey table for the subject being processed or its related subjects (in subject_backfill_queue_usedkey table),  then further processing is skipped.
  • previous_data column holds the history of scheduling relationships before the change and new_data column holds scheduling relationship information after the change.
  • Data in in previous_data column and new_data column in subject_backfill_queue table is read and compared to check if there are any differences. 
  • If no differences are found, then mitsis_tracking_internal column is set as '; No new scheduling relationships to be added' and further processing is skipped.
  • If there are differences, then the whole SR history in the columns are scanned and compared with existing data for the subject in scrsu_var table.
  • Mule flow backfill-main-mitsis-process-sr  implements SR Backfill Processing logic
  • First of all, existing master data from is read from scrsu_var table to create a baseline for comparison. 
  • Relevant columns read are subj_code, subj_numb, effective_term, cluster_type, subj_code_master and subj_numb_master in scrsu_var which determines scheduling relationships and the master subject in the relationship. 
  • After reading, collect all the terms where any CHANGE in cluster_type or subjectId or master or subjectLevel occurred. When any of these columns change, meets with cluster (MWC) could change. 
  • The terms are then analysed to create term ranges to give the most granular term range when change occurred for any of the subjects in the cluster. 
  •  Subjects which meets together (Meets With Cluster - MWC) groups are determined from existing scrsu_var data for each of the identified term ranges.  Since data is ported into container/template tables only from 2013FA, MWC groups for terms earlier than 2013FA are ignored and term ranges are conditioned for effective comparison. Existing master in scrsu_var is also identified and noted for each MWC.
  • The same way, MWC subjects for the same term range(s) are determined from JSON in new_data column. 
  • Then master is set as much as possible to same as existing data in scrsu_var.  Master assignment is detailed in - Assignment of Master Subject for EQSRs.
  • Both MWC cluster groups (existing and new) are compared and MWC group differences are identified.
  • Changes found are then translated and saved back to scrsu_var. 
  • subject_backfill_log record created for queue_id with success message

Assignment of Master Subject for EQSRs   (current implementation as of 11/7/2018)

  • For edits to existing EQSRs** If the previous master is undergraduate, it will be retained as the master of the cluster
    • If the previous master is graduate and there are no undergraduate subjects in the cluster, then the previous master will be retained
    • If the previous master is graduate and there are one or more undergraduate subjects already in or added to the cluster, an undergraduate subject will become the master for previous and new data (lowest alphanumeric undergraduate subjects will be assigned as master)
  • For new EQSRs between  undergraduate and graduate subjects, the undergraduate will be assigned as the master
  • For new EQSRs between two or more undergraduate subjects, the subject with the lowest alphanumeric subject number will be assigned as the master

Error Logging

When an error is encountered while processing SR record or regular record, 

  • a detailed error message is logged into mitsis_tracking_internal column of subject_backfill_queue table.
  • mitsis_backfill_status is set as -1 in subject_backfill_queue table.
  • errored subject key and related subject keys are entered into subject_backfill_errorkey table with queue_id as subject_backfill_queue.subject_backfill_queue_id. Related subject keys are the subject_key values entered into subject_backfill_queue_usedkey table by Subject Management API at the time of creating the queue record.
  • record is created in subject_backfill_log with error message.

5-Year MITSIS Equivalency Rules

Applicable System Rules:

  1. If Subject A is EQ with Subject B - and Subject B is deactivated, the two numbers will stay equivalent to each other for 5 years in MITSIS.
    This rule also applies to the removal of cross-lists and SWEs.
    1. Example: Subject A and Subject B had an EQ that began in 2010FA. Subject B was then deactivated - last active term was 2013SU.
      So the EQ persists in MITSIS for 2014, 2015, 2016, 2017 & 2018. The equivalency should not be place for 2019FA.
  2. Subject A is EQ to Subject B - and Subject B is deactivated. In a subsequent proposal year, Subject C is added as an EQ to Subject A.
    In this case, Subject C will also get Subject B as an EQ in MITSIS until Subject B has been inactive for 5 years. This rule also applies
    to the removal of cross-lists and SWEs.
    1. Example: Subject A and Subject B had an EQ that began in 2010FA. Subject B was then deactivated - last active term was 2013SU.
      Subject C was then added as an EQ to Subject A in 2015FA. Subject C would then be equivalent to Subject B for 2015, 2016, 2017, 2018.
  3. If a Subject is renumbered, the previous number and the current number will stay equivalent to each other for 5 years in MITSIS.
    1. Example: Subject 1.88 is renumbered to 1.702, effective 2014FA. The EQ persists in MITSIS for 2014, 2015, 2016, 2017, and 2018.
      The equivalency should not be place for 2019FA.

SCRSU_VAR Cross-Registration Master Mapping

Institution Description Subj_Code* Master_Subj_Code Subj_Num Master_Subj_Num
Harvard Subject Code  and Master Subject Code should be the same value.
Master Subject Number should be “0000”.
HAB HAB 1234 0000
Wellesley Subject Code and Master Subject Code should be the same value. 
Subject Number and Master Subject Number should be the same value.
WED WED 325 325
Mass College of Art Master Subject Code should be “ MC”.
Master Subject Number should be “0000”
MCA MC 207 0000
Brandeis Subject Code and Master Subject Code should be the same value.
Subject Number and Master Subject Number should be the same value.
BR0 BR0 S12 S12

*Please note these are only example Subject Codes. Subject Codes for these institutions can vary.


I was testing the MITSIS backfill and my test subject is not being processed (it remains in subject_backfill_queue). Why won't it backfill?
The effective term of the change may be greater than the SUBJECT_MGMT_CONFIG.mitsisBackfillAllowedUptoYear value. For example, if your test subject has an effective term of 2019FA but MITSIS may only be expecting subject changes for AY2018.

I was testing the MITSIS backfill and my test subject edit processed but I'm not seeing a new record in SCRSU_VAR. What gives?
Unless your test contains a change specific to one of the SCRSU_VAR fields, the backfill will not create a new SCRSU_VAR record. For example, if your test is only adding the UROP attribute to a subject, no SCRSU_VAR fields would have changed - and therefore there is no need to create a new record in that table. Only SCRATTR will get the new record(s).

Subject Management Documentation Index

The Subject Management Documentation Index is the central listing for documentation pertaining to Subject Management.

IS&T Contributions

Documentation and information provided by IS&T staff members

Last Modified:

November 27, 2018

Get Help

Request help
from the Help Desk
Report a security incident
to the Security Team
c-scasubji c-scasubji Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
This product/service is:
Easy to use
Difficult to use

This article is:
Adaptavist Theme Builder (4.2.3) Powered by Atlassian Confluence 3.5.13, the Enterprise Wiki