Complete Refresh is Evil! Make your SIS integration go at lightspeed

Document created by kelley.macewen on May 24, 2017•Last modified by kelley.macewen on Feb 15, 2018

Version 4Show Document

Like • Show 7 Likes7
Comment • 8
View in full screen mode

This document is taken from a presentation Mark O'Neil delivered at the 2017 Teaching & Learning Conference in Milan, Italy. You can download the presentation and script files at the end of this document.

Complete Refresh vs. Store/Delete

Complete Refresh	Explicit Store and Delete
You pass all your data – every single record – into a black box. Learn chews on data and takes actions based on data differences. Complete Refresh takes a file and manages the deletes from a set of data based on differences in the data provided and what is stored in Learn. This has substantial overhead as the data must be compared and determinations regarding differences (update), whether data is new (store), or whether the data is in Learn, but not in the data file (delete). Complete Refresh is often easier from an SIS extraction as you pull all the relevant data and create the feed file.	You pass in only the data to act on– decreasing data scale You pass in explicit data sets – and Learn only adds/updates records Using Store or Delete gives you explicit control over granular sets of data. This is substantially faster as only the data present in the file is acted upon. Store and Delete files are more difficult to generate as the SIS needs to process the data pulls to create files containing only the differences between pulls to generate the feed files. However, the efficiency gained in processing often makes Store and Delete the more worthwhile method.

Complete Refresh

Explicit Store and Delete

You pass all your data – every single record – into a black box.
Learn chews on data and takes actions based on data differences.

Complete Refresh takes a file and manages the deletes from a set of data based on differences in the data provided and what is stored in Learn. This has substantial overhead as the data must be compared and determinations regarding differences (update), whether data is new (store), or whether the data is in Learn, but not in the data file (delete).

Complete Refresh is often easier from an SIS extraction as you pull all the relevant data and create the feed file.

You pass in only the data to act on– decreasing data scale
You pass in explicit data sets – and Learn only adds/updates records

Using Store or Delete gives you explicit control over granular sets of data. This is substantially faster as only the data present in the file is acted upon.

Store and Delete files are more difficult to generate as the SIS needs to process the data pulls to create files containing only the differences between pulls to generate the feed files. However, the efficiency gained in processing often makes Store and Delete the more worthwhile method.

Example

Given:

Old.txt: A Complete Refresh feed from yesterday

And New.txt: A Complete Refresh feed from today

Process

We need to compare new to old complete refresh feeds to find differences
- sort the files and diff
We want our DISABLE document to contain only records that are not in new
Our STORE document to contain only documents that are CHANGED or ADDED in New
- CHANGED means the record may be in old and thus in both old and new so is a special case
  - We take the disabled doc and These will be in DISABLED due to initial diff – need to remove
- ADDED means the record is only in New
  - This is discovered via another diff between old and new

This may look like a lot of costly steps against a large set of refresh data, but I guarantee it and the Snapshot DELETE/STORE will be significantly faster than a complete refresh.

Create Sorted files:

$ sort New.txt > sortedNew.txt
$ sort Old.txt > sortedOld.txt

Create DISABLE.txt file and write the proper header:

$echo 'EXTERNAL_PERSON_KEY|…|LASTNAME|EMAIL|INSTITUTION_ROLE' > DISABLE.txt

Generate the DISABLEd users (those not in the new file):

$diff sortedNew.txt sortedOld.txt | grep ‘> ' | sed ’s/> //g’ >> DISABLE.txt

Generate the Updates from DISABLEd users file

$ ./sedRemoveUpdates.sh

DISABLE.txt contents before sedRemoveUpdates:

DISABLE.txt contents after sedRemoveUpdates:

Running sedRemoveUpdates removes the updates (which which were matched in the diff). Where sedRemoveUpdates.sh is:

#!/bin/bash

while read line; do

matchMe=$(cut -d'|' -f1 <<< $line)

echo $matchMe

searchResult=$(grep $matchMe < sortedNew.txt)

if [ -z "$searchResult" ]; then

#echo "not found"

else

sed -i.bak "/$matchMe/d" ./DISABLE.txt

done < DISABLE.txt

exit 0

now DISABLED.txt contains only those EXTERNAL_PERSON_KEYS that are not in the sortedNew.txt file.

We can then use a similar process reversing the Diff to find Updated users to create a smaller Store file that reflects only additions and updated lines in the sortedNew.txt by copying only new and updated entries from a slightly altered sortedNew file to a STORE.txt file.

Copy sortedNew to storeNew:

$cp sortedNew.txt storeNew.txt

Create STORE.txt file and write the proper header:

add the missing data from old to new by appending DISABLED to sortedOld and resorting:

$cat DISABLED.txt >> storeNew.txt

$sort storeNew.txt > sortedStoreNew.txt

Diff the old file to the new data and append to STORE.txt:

$diff sortedStoreNew.txt sortedOld.txt | grep '< '|sed 's/< //g' >> STORE.txt

STORE.txt:

DISABLE.txt:

Archive Old.txt and Save New.txt as Old.txt for the next update.

Additional thoughts

Faster by orders of magnitude
Easy to insert into Snapshot SIS Framework flow
Less prone to “where’d my data go?” scenarios
Timestamp and archive your files, and prep for the next iteration
- archive_dir = “./archives”
- now = `date +%Y-%m-%d.%H:%M:%S`
- mv old.txt $archive_dir+”archive” + $now
- mv new.txt old.txt

Downloads

Scripts required to provide SIS deltas
'Complete Refresh is Evil!' Presentation

1 person found this helpful

Attachments

CompleteRefreshIsEvil.pptx447.4 KB
refreshisevil.zip6.5 KB

Outcomes

8 Comments

jkelley_blackboard May 29, 2017 9:10 AM
Brilliant. Thanks!
Like • Show 0 Likes0
Actions
jkelley_blackboard Aug 4, 2017 7:37 PM
I wrote a Python 2.7 version of this that might help in other operating environments. I'm willing to share if you help me debug it. ;-)
1 person found this helpful
Like • Show 0 Likes0
Actions
mdeeprose Aug 9, 2017 4:05 AM
I'm going through the example files and powerpoint.

In the file convertRefreshToSnapshot.sh at the beginnnig it says

sed -i "" '1d' new.txt
sed -i "" '1d' old.txt

what does this do and why is it necessary?

thanks!
Like • Show 0 Likes0
Actions
moneil Aug 9, 2017 10:06 AM
This removes the first line - '1d' - from the file so that the header is not included in the sorting and data comparisons.

Cheers,
-m
Like • Show 0 Likes0
Actions
- mdeeprose @ moneil on Aug 10, 2017 1:28 AM
  Thanks Mark!
  
  I'm going to try implementing this technique in the next few days.
  
  Complete refresh for some of our feeds is taking 10x longer than it did with the snapshot tool.
  Like • Show 0 Likes0
  Actions
  - mdeeprose @ mdeeprose on Aug 24, 2017 4:13 AM
    sed -i "" '1d' new.txt didn't work for me on RHEL7 bash.
    
    I used sed -i '1d' instead.
    
    For making the "old" file I started by copying the full file after it had been processed, and adding a date on it.
    cp fullfile.txt fullfile.txt.`date +%Y%m%d`
    
    and then deleting files older than 15 days:
    find fullfile.2* -mtime +15 | xargs -i@ rm -f @
    
    so then doing the compare I did:
    sed '1d' fullfile.txt.`date -d "yesterday 13:00" '+%Y%m%d'` > old-no-header.txt
    
    then sort then compare. Doing "yesterday 13:00" was a tip from stackoverflow about ensuring changing time zones (e.g. summer time, winter time) doesn't mean the wrong file is picked up.
    
    not using sed -i but putting it into a new file meant I kept a pure copy of the original file for any audit purposes.
    
    The scripts were really useful. Thank you!
    Like • Show 1 Like1
    Actions

mbuchanon Sep 17, 2018 5:29 PM

For those that are on Windows and want to do something similar in PowerShell You can tinker with something like this:

clear
$olddata = @"
EXTERNAL_PERSON_KEY|USER_ID|SYSTEM_ROLE|FIRSTNAME|LASTNAME|EMAIL|INSTITUTION_ROLE
2006|ddishez001|none|Dan|Dishez|ddishez@sununiversity.edu|Student
2000|jsmith001|none|John|Smith1|jsmith@sununiversity.edu|Student
2007|rrondelle001|none|Ronda|Rondelle|rrondella@sununiversity.edu|Student
2002|ttsai001|none|Tevis|Tsai|ttsai@sununiversity.edu|Student
2003|lgonzales001|none|Lois|Gonzales|lgonzales@sununiversity.edu|Student
2004|mmacneil001|none|Megan|MacNeil|mmacneil@sununiversity.edu|Student
2001|kthomas001|none|Kyle|Thomas2|kthomas@sununiversity.edu|Student
2005|mmacneil002|none|Mark|MacNeil|mmacneil1@sununiversity.edu|Student
"@
$newdata = @"
EXTERNAL_PERSON_KEY|USER_ID|SYSTEM_ROLE|FIRSTNAME|LASTNAME|EMAIL|INSTITUTION_ROLE
2006|ddishez001|none|Daniel|Dishez|ddishez@sununiversity.edu|Student
2005|mmacneil002|none|Mark|MacNeil|mmacneil1@sununiversity.edu|Student
2007|rrondelle001|none|Ronda|Rondelle|rrondella@sununiversity.edu|Student
2003|lgonzales001|none|Lois|Gonzales|lgonzales@sununiversity.edu|Student
2004|mmacneil001|none|Megan|MacNeil|mmacneil@sununiversity.edu|Student
2000|jsmith001|none|John|Smith1|jsmith@sununiversity.edu|Student
2020|bbaggins|none|bilbo|baggins|bbaggins@sununiversity.edu|Student
"@
[array]$olddata = $olddata | convertfrom-csv -Delimiter "|" | sort external_person_key
[array]$newdata = $newdata | convertfrom-csv -Delimiter "|" | sort external_person_key

[array]$diff = Compare-Object $olddata $newdata -Property external_person_key,user_id,system_role,firstname,lastname,email,INSTITUTION_ROLE

[array]$updates    = ($diff | group -Property external_person_key | ? {$_.count -gt 1}).Group 
[array]$newdiff    = $diff | where-object {$updates -notcontains  $_}

[array]$updates = $updates | ? {$_.SideIndicator -eq "=>" }   | select * -ExcludeProperty SideIndicator
[array]$drops   = $newdiff | ? {$_.SideIndicator -eq "<=" }   | select * -ExcludeProperty SideIndicator
[array]$adds    = $newdiff | ? {$_.SideIndicator -eq "=>" }   | select * -ExcludeProperty SideIndicator

$store = $updates + $adds 
$disable = $drops  
$store   |convertto-csv -Delimiter "|" -notype |% {$_ -replace '"'}  | out-file -Encoding utf8 -force "\path\to\store.txt"
$disable |convertto-csv -Delimiter "|" -notype |% {$_ -replace '"'}  | out-file -Encoding utf8 -force "\path\to\disable.txt"

Actions

dg23077 @ mbuchanon on Aug 20, 2018 5:11 PM
I use compare-object for all my diffs and it works quickly and very well.
Like • Show 1 Like1
Actions