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.
Comments
0 comments
Article is closed for comments.