EdReflect FTP Process – EdReflect Support

Purpose: To explain how the updated EdReflect FTP files and processes have been updated since the EdReflect refresh on August 10th, 2020.

History: Previously, clients were given credentials to an org specific folder on BloomBoard’s FTP server. This folder contained CSV files with the totality of data for that org, and these files were updated with a complete export of data daily.

Legacy Data: Before we switched to the new version of EdReflect, clients were asked to collect the exports of their data before the switch to the refreshed version of EdReflect.

Updates

New ID's

After making the switch to the refreshed EdReflect, we have updated the id’s associated with our data models. For this reason, we are providing join tables from the old model id’s to the new model id’s. These join tables can be found within the export/all folder on the ftp server for an org.

The join tables are:

artifact_ids.csv
artifact_content_ids.csv
goal_ids.csv
indicator_ids.csv
observation_group_ids.csv
observation_template_ids.csv
organization_ids.csv
rubric_ids.csv
slo_assessment_ids.csv
slo_ids.csv
slo_template_ids.csv
step_ids.csv
snapshot_ids.csv
trending_ids.csv
user_ids.csv
user_group_ids.csv

Exports as daily changes

The files in an org’s ftp folder will no longer contain a complete export updated daily. Instead, we will provide a list of all things that have changed on a given day, and provide this export on a daily basis. While we know what data models were changed, we don’t always know exactly what changed, but the current state of any models that were modified that day. For this reason, we have created new CSV files that track just which models have been changed so that any rows keyed by those model id’s can be dropped, and the new state can then be uploaded into our clients’ database systems. This is required. If an item in a list has been deleted, we don’t know which item was deleted, we only know which items in the list remain. Therefore, all items in that list should be deleted from a client’s database, and then all the current items should be uploaded.

tl;dr: Orgs will need to delete some rows from many of their tables before upserting the rest of the data, on a daily basis.

Deprecated Exports

The following exports are no longer generated:

Aggregated_EOY_Reports_Summary.csv
Observation_Survey.csv
Observation_Template_Scheduling_Groups.csv

Other notes

Some exports have had fields removed. Other exports have had fields added, some both. The added / dropped fields are detailed in the FTP Files table.
The field school_year provides a calendar year, while the school_year_id field provides the number of years since 2009.
In the artifact_ids.csv and artifact_content_ids.csv mapping tables, our old artifact and artifact_content models have been combined, so a legacy artifact_id and a legacy artifact_content_id can point to the same new_id for an artifact.
Legacy id fields for groups were inconsistent, so no mapping of old id to new id was performed.

Timing

Each day, information is collected from midnight GMT to 11:59 GMT. Then, the FTP job is run at 8 am GMT.

FTP Files

Below is a list of all files in an org's ftp export folder, any dropped fields, any added fields, and notes about the file.

File Name	Dropped Fields	New Fields	Notes
Aggregated_Observation_Ratings.csv		school_year	Drop all rows keyed by observation_group_id's in Observation_Modified.csv
Aggregated_Slo_Assessments.csv	slo_assessment_type_name
Aggregated_Snapshot_Ratings.csv		observation_school_year, observation_group_id	Drop all rows keyed by observation_group_id's in Observation_Modified.csv
Goal_Posts.csv	uploaded_content_id, catalog_item_id, post_created_date		Drop all rows keyed by goal_id's in Goals_Modified.csv
Goals_Deleted.csv
Goals_Indicators.csv
Goals_Modified.csv			Reference CSV for knowing which rows to drop in relevant CSV files.
Goals_Questions.csv			Drop all rows keyed by goal_id's in Goals_Modified.csv
Goals.csv
Indicator_Proficiency_Levels.csv			Drop all rows keyed by rubric_id's in Rubrics.csv
Indicators.csv			Drop all rows keyed by rubric_id's in Rubrics.csv
Observation_Answers.csv			Drop all rows keyed by observation_group_id's in Observation_Modified.csv
Observation_Comments.csv	resource_name, resource_id, comment_id, comment_date_time, commenter_note		Drop all rows keyed by observation_group_id's in Observation_Modified.csv
Observation_Feedback.csv	feedback_date, next_steps_date, observer_notes_date		Drop all rows keyed by observation_group_id's in Observation_Modified.csv
Observation_Forms.csv			Drop all rows keyed by observation_group_id's in Observation_Modified.csv
Observation_History.csv
Observation_Modified.csv			Reference CSV for knowing which rows to drop in relevant CSV files.
Observation_Participants.csv			Drop all rows keyed by observation_group_id's in Observation_Modified.csv
Observation_Snapshots.csv			Drop all rows keyed by observation_group_id's in Observation_Modified.csv
Observation_Step_Scripting_Notes.csv	scripting_note_updated_date, scripting_note_created_date

Here is an example based on the user_affiliations table:

This article is available as a PDF download below to share with your organization's IT or Data team to assist with setting up FTP usage.

ftp_EdReflect_FTP.pdf
30 KB Download

Articles in this section