In this example, we are working with a CSV file containing Student Assessment data. One of the columns is the Student USI which tells us the Student related to the Student Assessment. However, in order to load this Student Assessment to the ODS, we need the Student Unique Id, not the Student USI.
Consider a CSV file from a third-party system which contains this Student Assessment data. In this case, the third-party system produces data with a StudentUSI and we must perform a lookup so we can get the StudentUniqueId:
adminyear,DistrictNumber,DistrictName,SchoolNumber,SchoolName,StudentUSI,listeningss_adj,speakingss_adj,readingss_adj,writingss_adj,comprehensionss_adj,oralss_adj,literacyss_adj,Overallss_adj 2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,134709,333,349,270,246,289,341,258,283 2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,134537,303,392,100,100,161,348,100,174 2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,230626,363,230,152,202,215,297,177,213 2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,62776,294,262,251,263,264,278,257,263 2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,9888,209,237,269,277,251,223,273,258 2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,8,270,237,296,251,288,254,274,268 2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,185634,270,262,289,242,283,266,266,266 2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,185565,934,948,932,926,933,941,929,933 2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,212298,939,939,928,930,931,939,929,932 2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,26811,938,925,929,916,932,932,923,925
We also have a table called StudentLookups defined with StudentUSI as the primary key and StudentUniqueId as the value. Here is a snippet of some of the records in that table:
A custom script to "look up" the Student Unique Id from the Student USI looks like this:
param ($row)
# This example takes a CSV with StudentUSIs and performs a lookup on a database table to retrieve the corresponding StudentUniqueId
# Assumed variables for this snippet
# Assumed table structure with two string columns [Key] and [Value] with a primary key on [Key] so that the table is dictionary-like.
$connectionString = "--Enter the connection string needed to access your lookup table--"
# Function to perform a single lookup.
function get-replacement($original) {
$connection = new-object System.Data.SqlClient.SqlConnection($connectionString)
try {
$connection.Open();
$command = $connection.CreateCommand()
try {
$command.CommandText = "SELECT [Value] FROM [dbo].[StudentLookups] WHERE [Key] = @Original"
$command.Parameters.AddWithValue("@Original", $original) | Out-Null
$reader = $command.ExecuteReader()
try {
$replacements = @()
while ($reader.Read()) {
$replacements += $reader.GetValue(0)
}
if ($replacements.Length -ne 1) {
throw "Expected exactly one unambiguous match for original id '$($original)' but found $($replacements.Count) matches."
}
return $replacements[0]
}
finally {
$reader.Dispose()
}
}
finally {
$command.Dispose()
}
}
finally {
$connection.Dispose()
}
}
# Your row-by-row processor script will call the get-replacement function in order to add a new column, StudentUniqueId, based on the original column StudentUSI
$row.'StudentUniqueId' = (get-replacement $row.'StudentUSI')
This script is run for each row. When this script is run, the StudentUSI (Key) is used to retrieve the StudentUniqueId (Value) from the StudentLookups table that we have defined. To perform this lookup, we need to configure the connection string and then use the proper query to get the StudentUniqueId. Once this lookup is made, there is some validation to ensure there is exactly one match and then we later add the "StudentUniqueId" column with the looked-up value. The Transform/Load process then performs the mapping and POSTs to the ODS. After this, we move on to the next row, where we repeat the same steps.
Similar to the Quick Start, the user sets up any necessary Bootstrap items, a Data Map for mapping these CSV columns to ODS Student Assessements, and an Agent that is set up to use the script. When setting up the Data Map, we added a dummy column named "StudentUniqueId" to our CSV since Transform/Load will be looking for the looked-up value in that column.
When we run Transform/Load, we will encounter success. The Logs \ Ingestion screen shows the looked-up StudentUniqueId value that was POSTed to the ODS:
