CA ODS Reports [.2140 - .2219]

Classic View: CA State Reporting > CA ODS

Search Terms: ODS

ODS Reports compare student, staff and course data that exists in Infinite Campus against student, staff and course data that exists in CALPADS. Users generate the desired CALPADS extract (SENR, SELA, SPRG, SINF, SDEM, CRS, SCS, or SASS) in the state format in Infinite Campus and save it, then generate the same extract with the same selections in the CALPADS ODS (Operational Data Store) and save that.

Then, upload both files in the CA ODS editor to either compare the results of the files or upload the results of the files. The ODS tool generates a report that shows which student, staff or course data doesn't exist in CALPADS and should be added to ODS, which student, staff or course data doesn't exist in Campus and should be deleted in ODS, which students, staff or courses have different values between the two files and should be updated, and which students, staff and courses exist in both systems and no action is taken.

The goal is to have matching data in both systems. These records are considered synchronized.

CA ODS Editor

Tool Rights

Full rights to use this tool require R(ead) rights to CA State Reporting > CA ODS.

Classic View - CA ODS Tool Rights
New Look Navigation - CA ODS Tool Rights

Report Logic

The Comparison between the Campus-generated extracts and the ODS-generated extracts matches on particular fields, detailed in the File Comparison Detail section. The Campus-generated extracts must have the Reporting Date and at least one Calendar selected in order to generate. 

When adding the files to the Queue to update ODS data, an action of Add, Delete or Update will be taken for the data in ODS, causing records in Campus and ODS to be synchronized.

File formats need to be uploaded in the State Format (carat delimited).

Records included the file that do not match the selected calendars are ignored in the comparison.

Note that the CALPADS ODS files include two additional date fields at the end of the file that Infinite Campus does not use.

File Comparison Details

This section provides information on which fields are used in the comparison logic that determines the Add, Delete, Update or Synchronized status. 

Fields are assigned a Match Weight. This weight is used to match multiple records in the same file and indicate the correct record. For example, two records for the SPRG exist in both Campus and ODS. Fields like Membership Start Date have a higher weight than fields like First Name. The Match Weight is listed in parentheses in the Additional Fields section below for each extract where applicable.

SPRG

See the CALPADS Student Program (SPRG) article for the extract layout.

Record Keys

These fields are ignored and are not used in the comparison or in the synchronization process:

  • Field 1, Record Type Code
  • Field 2, Transaction Type Code
  • Field 3, Local Record ID

Exact Match

An exact match is required for the following fields: 

  • Field 4, Reporting LEA
  • Field 5, School of Service
  • Field 6, Academic Year ID
  • Field 7, SSID

Additional Fields

Each of the remaining fields (8-26) are compared for differences in the file and the Comparison Status column on the file returns a value of Add, Delete, Update or Synchronized.

For assistance in trouble shooting and debugging potentially erroneous data, review the following fields.

(1) LocalStudentID(100) MembershipCode(100) AcademicID
(1) FirstName(100) MembershipStartDate(100)  MigrantStudentID
(1) LastName(100) MembershipEndDate(100) HomelessDwellingType
(1) BirthDate(100) EducationalServiceAcademicYear(100) UnaccompaniedYouthIndicator
(1) Gender(100) EducationServiceCode(100) RunawayYouthIndicator

SELA

See the CALPADS Student English Language Acquisition (SELA) article for the extract layout.

Record Keys

These fields are ignored and are not used in the comparison or in the synchronization process:

  • Field 1, Record ID
  • Field 2, Transaction Type Code
  • Field 3, Local Record ID

Exact Match

An exact match is required for the following fields: 

  • Field 4, Reporting LEA
  • Field 5, School of Attendance
  • Field 7, SSID

Additional Fields

Each of the remaining fields (8-15) are compared for differences in the file and the Comparison Status column on the file returns a value of Add, Delete, Update or Synchronized.

For assistance in trouble shooting and debugging potentially erroneous data, review the following fields.

(1) FirstName(1) LocalStudentID
(1) LastName(100) ELASStatus
(1) BirthDate(100) ELASStartDate
(1) Gender(100) primaryLanguage

SENR

See the CALPADS SSID Enrollment article for the extract layout.

Record Keys

These fields are ignored and are not used in the comparison or in the synchronization process:

  • Field 1, Record Type
  • Field 2, Transaction Type Code
  • Field 3, Local Record ID

Exact Match

An exact match is required for the following fields: 

  • Field 4, Reporting LEA
  • Field 5, School of Attendance
  • Field 6, School of Attendance NPS
  • Field 7, Academic Year ID
  • Field 8, SSID

Additional Fields

Each of the remaining fields (9-39) are compared for differences in the file and the Comparison Status column on the file returns a value of Add, Delete, Update or Synchronized.

For assistance in trouble shooting and debugging potentially erroneous data, review the following fields.

(1  LocalStudentID(1) Gender(100) CompletionStatus
(1) FirstName(1) BirthCity(100) ReceiverSchool
(1) MiddleName(1) BirthState(100) UC_CSUCourseRequirementMet
(1) LastName(1) BirthCountry(100) StudentSchoolTransferCode
(1) Suffix(1000) SchoolStartDate(100) DistrictOfGeographicResidence
(1) AliasFirstName(100) EnrollmentStatus(100) StudentGoldenStateSealMeritDiploma
(1) AliasMiddleName(100) GradeLevel(100) StudentSealofBiliteracyIndicator
(1) AliasLastName(100) SchoolExitDate(100) AdultAgeWithStudentsWithDisabilitiesTransition
(1) BirthDate(100) ExitReasonCode

SINF

See the CALPADS Student Information (SINF) article for the extract layout.

Record Keys

These fields are ignored and are not used in the comparison or in the synchronization process:

  • Field 1, Record Type
  • Field 2, Transaction Type Code
  • Field 3, Local Record ID

Exact Match

An exact match is required for the following fields: 

  • Field 4, Effective Start Date
  • Field 5, Effective End Date
  • Field 6, Reporting LEA
  • Field 7, School of Attendance
  • Field 8, Academic Year ID
  • Field 9, SSID

Additional Fields

Each of the remaining fields (10-54) are compared for differences in the file and the Comparison Status column on the file returns a value of Add, Delete, Update or Synchronized. Each field is equally weighted when matching results from ODS.

For assistance in trouble shooting and debugging potentially erroneous data, review the following fields. 

LocalStudentID BirthDate  RaceCode2 ResidentialState Guardian2LastName
 FirstNameGender RaceCode3 ResidentialZip PreferredFirstName
 MiddleName BirthCityRaceCode4 InitialSchoolEnrollmentDate PreferredLastName
 LastName BirthState RaceCode5 EnrolledLessThanThreeYears MailingAddressLine
 Suffix  BirthCountryRaceMissingIndicator GuardianHighestEducation MailingAddressLine2
 AliasFirstNameHispanicEthnicityResidentialAddressLine1 Guardian1FirstNameMailingCity
 AliasMiddleNameMissingEthnicityIndicatorResidentialAddressLine2 Guardian1LastName MailingState
AliasLastNameRaceCode1ResidentialCityGuardian2FirstNameMailingZip

SDEM

See the CALPADS Staff Demographics (SDEM) article for the extract layout.

Record Keys

These fields are ignored and are not used in the comparison or in the synchronization process:

  • Field 1, Record Type
  • Field 2, Transaction Type Code
  • Field 3, Local Record ID

Exact Match

An exact match is required for the following fields: 

  • Field 4, Effective Start Date
  • Field 5, Effective End Date
  • Field 6, Reporting LEA
  • Field 7, Academic Year ID
  • Field 8, Statewide Educator Identifier

Additional Fields

Each of the remaining fields (9-31) are compared for differences in the file and the Comparison Status column on the file returns a value of Add, Delete, Update or Synchronized. Each field is equally weighted when matching results from ODS.

For assistance in trouble shooting and debugging potentially erroneous data, review the following fields.

LocalStaffIDGenderRaceMissingIndicator
FirstNameHispanicEthnicityHighestDegreeCode
MiddleNameMissingEthnicityIndicatorEmploymentStatus
LastNameRaceCode1EmploymentStartDate
AliasFirstNameRaceCode2EmploymentEndDate
AliasMiddleNameRaceCode3ServiceYearsLEA
AliasLastNameRaceCode4TotalYearsService
BirthDateRaceCode5

SASS

See the CALPADS Staff Assignment (SASS) article for the extract layout.

Record Keys

These fields are ignored and are not used in the comparison or in the synchronization process:

  • Field 1, Record Type
  • Field 2, Transaction Type Code
  • Field 3, Local Record ID

Exact Match

An exact match is required for the following fields: 

  • Field 4, Reporting LEA
  • Field 5, School of Assignment
  • Field 6, Academic Year ID
  • Field 7, Statewide Educator Identifier

Additional Fields

Each of the remaining fields (8-21) are compared for differences in the file and the Comparison Status column on the file returns a value of Add, Delete, Update or Synchronized.

For assistance in trouble shooting and debugging potentially erroneous data, review the following fields. 

(1) LocalStaffID

(100) StaffJobClassificationFTEPercentage

(100) NonClassrmSupportCode4

(1) StaffFirstName

(100)  NonClassrmSupportCode1

(100) NonClassrmSupportCode5

(1) StaffLastName

(100) NonClassrmSupportCode2

(100) NonClassrmSupportCode6

(1) StaffBirthDate

(100) NonClassrmSupportCode3(100) NonClassrmSupportCode7
(1) StaffGenderCode

CRS

See the CALPADS Course Section (CRS) article for the extract layout.

Record Keys

These fields are ignored and are not used in the comparison or in the synchronization process:

  • Field 2, Transaction Type Code
  • Field 3, Local Record ID

Exact Match

An exact match is required for the following fields: 

  • Field 1, Record Type
  • Field 4, Reporting LEA
  • Field 5, School of Course Delivery
  • Field 6, Academic Year ID
  • Field 8, Local Course ID
  • Field 14, Course Section ID
  • Field 15, Academic Term Code
  • Field 16, Statewide Educator Identifier

Additional Fields

Each of the remaining fields (7, 9-13, 17-36) are compared for differences in the file and the Comparison Status column on the file returns a value of Add, Delete, Update or Synchronized. Each field is equally weighted when matching results from ODS.

For assistance in trouble shooting and debugging potentially erroneous data, review the following fields.

StateCourseCode

LanguageofInstructionDepartmentalizedCourseStandards GradeLevelRangeCode
CourseNameInstructionalStrategyCodeContentStandardsAlignmentCode
CTEPostsecondaryArticulatedCourseIndependentStudyIndicatorCharterNonCoreNonCollegePrepCourseIndicator
UC_CSUApprovedDistanceLearning

APIBCourseCodeCrossReference

LocalStaffIDMultipleTeacherCodeOnlineCourseInstructionTypeCode
ClassIDProgramFundingSourceMiddleSchoolCoreCourseIndicator
CourseInstructionalCodeCTECourseProviderLocalAssignmentOptionCode
EducationServiceCodeCourseContentAreaSubcategoryHighQualityCTECourseIndicator

SCS

See the CALPADS Student Course Section (SCSE, SCSC) article for the extract layout.

Record Keys

These fields are ignored and are not used in the comparison or in the synchronization process:

  • Field 2, Transaction Type Code
  • Field 3, Local Record ID

Exact Match

An exact match is required for the following fields: 

  • Field 1, Record Type Code
  • Field 4, Reporting LEA
  • Field 5, School of Course Delivery
  • Field 6, Academic Year ID
  • Field 7, SSID
  • Field 13, Local Course ID
  • Field 14, Course Section ID
  • Field 15, Academic Term Code

Additional Fields

Each of the remaining fields (8-12, 16-21) are compared for differences in the file and the Comparison Status column on the file returns a value of Add, Delete, Update or Synchronized.

For assistance in trouble shooting and debugging potentially erroneous data, review the following fields.

(1) LocalStudentID(1) Gender(100) UC_CSUApproved
(1) FirstName(100) CreditsAttempted(100) MarkingPeriod
(1) LastName(100) CreditsEarned(100) CarnegieUnitsEarned
(1) BirthDate(100) FinalGrade

Report Editor

OptionDefinition
File to CompareLists which CALPADS Extracts can be selected for comparison and data verification.

It is assumed that the files generated from Infinite Campus are generated in the state format and saved for easy uploading (the next option).

Upload Infinite Campus FileAllows the uploading of one of the above extract files to the editor. When selected, a side panel displays where the saved file can be uploaded.

Files are saved per user.
Upload CALPADS ODS FileAllows the uploading of one of the CALPADS files listed above from the CALPADS ODS system. These should also be in the state format and have the same options selected as the Infinite Campus file.

For example, when generating the CALPADS SPRG file, users can select specific programs to include. If the Infinite Campus file does not include the same programs selected in the ODS file, there will be no synchronized records, meaning data in Infinite Campus matches the data in ODS (the goal).  

Files are saved per user.
Add Comparison to QueueOnce the files are uploaded, use this option to generate the file that lists the data results. Essentially, this is the result of data in Campus and data in ODS. Each record has a result:
  • ADD - Data is not in ODS. Record will be added to ODS.
  • DELETE - Data is not in Campus but is in ODS. Record will be deleted in ODS.
  • UPDATE - Some data is different between IC and ODS. Record will be updated in ODS. 
  • SYNCHRONIZED - Data is the same between ODS and Campus. No changes will occur. The ultimate goal is to have all data be synchronized (match) in both systems.

This option does not change any data in ODS or in Campus. This step is provided to compare the differences between ODS and Campus data prior to updating files that will actually change the data.

Add Data Files to Queue

After spending time comparing the data for the reports, this option queues the changes that are needed and are added to ODS. 

  • ADD - Data that is not in ODS will be added.
  • DELETE - Data that is not in Campus will be removed from ODS.
  • UPDATE - Data that does not match will be updated in ODS. 
Batch Queue List
Because this is a large set of data being compared or uploaded, all uploaded files are processed in the Batch Queue. When the files have been compiled and are ready to compare, the Status displays as Completed and can be selected for viewing.

Filter the list of Batch Queue reports using the Start Date and End Date fields to narrow results. 

Compare Data Using the CA ODS Tool

Step 1. Generate the CALPADS extract in Campus

  1. Navigate to the desired CALPADS Extract, as follows:
  2. Generate the CALPADS extract in the State Format. 
  3. Save that file where is can be easily found and with a file name that explains it is the CALPADS extract from Campus.

Take note of which options were chosen when generating these extracts. The same selections need to be made when generating the files from CALPADS. 

This includes calendars, programs, effective dates, collection/report type, etc.

Step 2. Generate the CALPADS extract in ODS

  1. Follow the steps provided by CALPADS at this location - https://documentation.calpads.org/Extracts/ODSExtract/ - to generate the same CALPADS Extract as was generated in Step 1.
  2. Ensure the same options were selected when generating the extracts in Campus. When different options are selected, the comparison file will not be comparing the same data, and the changes submitted to ODS for updating, adding or deleting will be incorrect. 
  3. Generate the file in the State Format.
  4. Save that file where it can be easily found and with a file name that explains it is the CALPADS extract from ODS.

Step 3. Upload Files on ODS Tool

  1. Select the desired CALPADS Extract to compare by selecting one of the CALPADS Extract options in the Files to Compare field.
  2. Click the Upload Infinite Campus File button. In the side panel, click in the text box to locate and attach the saved file from Step 1 and click the Save button.
  3. Click the Upload CALPADS ODS File button. In the side panel, click in the text box to locate and attach the saved file from Step 2 and click the Save button.
  4. Click Close in the Side Panel.
  5. Click the Add Comparison to Queue button. Due to the large amount of data that is included in extracts, the file is automatically sent to Batch Queue. A row is added to the Batch Queue section for the files that were just uploaded. The status begins as Queued. When it is ready to view, the status changes to Completed.

Files are added to the Batch Queue list in CSV and in HTML formats. The CSV format, when copied into Excel, allows filtering and sorting to group data by Add or Delete. The HTML format cannot be sorted but is easier for reading.

To see all files that are queued, go to the Process Alerts (formerly Process Inbox) by choosing your name from the Index (when in the Classic View) or access the Message Center (when in the New Look). 

To remove a queued report or a completed report, administrators can use the Delete or Delete All options available in the Batch Queue Admin tool.

If the Files to Compare dropdown list has no extracts available and reads NO DATA FOUND, contact Campus Support to request that the Student Performance Cache database be updated to the latest version. A separate database is used to store the data snapshot records (the same database that uses the Student Performance Cache). If the Campus application version cannot find a compatible version of that storage database, the list of comparable files is blank. 

Step 4. Compare Student, Staff or Course Data

Once the comparison has completed in the queue, open the file to view the results. The HTML format is easy to read and scroll through. The CSV format can be sorted to look at all records that will be added or deleted, for example, or to see which records don't exist in ODS or in Campus.

Open Completed Comparison File

Districts need to work with those responsible in the district to verify that the data the comparison report returns is accurate. Determine whether a record that has an Update Status of Add really should be added, or a record that has an Update Status of Delete really should be deleted. Also carefully review the available programs in the district for accuracy. 

Records in Infinite Campus can be modified as needed. For example, if after comparing data and it is discovered student enrollments were ended inaccurately, make the changes in Campus and repeat the previous steps to compare the newly modified enrollment records.

Sample Files

An HTML file looks like this:

Comparison File - HTML Format

A CSV file looks like this: 

Comparison File - CSV Format

The above images show ADD and DELETE statuses. When the file is uploaded to ODS, the records will either be ADDED to ODS because they do not exist there but do exist in Campus, or they will be DELETED from ODS because the records exists there but does not exist in Campus.

When multiple records exist for both synchronized values and update/delete/add values, the update/delete/add values are removed. 

For UPDATE records, the difference in data has a row for the information in Campus (labeled with IC) and a row for the information ODS (labeled with ODS). The difference could be any value in the extracts that don't match exactly - e.g., a birth date, a gender value, a grade level. In the image below, data differences exist for the Birth Date field, School Start Date and Enrollment Status. 

Update Status - Data Differences Display

For SYNCHRONIZED records, data is not modified when the files are uploaded to ODS. These records still display on the Comparison report. 

Synchronized Status - Records Match

Step 5. Queue Files for Uploading to ODS

After comparing the data and making any necessary changes to data in Campus, the information can be uploaded to ODS. 

Be aware that this step changes data in ODS. 

Click the Add Data Files to Queue button. Three files are added to the Batch Queue List - one file each for Add, Delete and Update.

Add Data Files to Queue

Once completed (e.g., state changes from Queued to Completed), open the file to see what was added, deleted or updated in ODS. That file looks like this: 

Uploaded File to ODS