The first step the script is doing is creating a temp table that will store the staff information. This is so that the information can be loaded once and reused throughout the script without having to hard code the same data throughout different parts of the script.
For Nebraska, the information that is required for someone to log in is what is in the temp table.
· StaffStateId - This is the state id of the staff member.
· PositionTitle - This is the role that the user will be logging in to in the dashboard and is the same as the Staff Category.
The second section of the script is inserting the actual staff members. A staff member can be entered here multiple times for each of the LEAs or Schools that they need access to. The below example shows how the final insert statement would look like when it is populated. There will be an individual insert statement for each staff member.
The example code below is a template and has been parameterized so that can be used to quickly enter in staff members. Just copy and paste into the SQL script.
The next section of the script Inserts the staff information from the #Staff temp table. This only loads the required information to log in to the dashboard and what the ETL packages are requiring. This part will only insert staff members that are not already in the domain.StaffInformation table. If the staff member does not exist, it generates a StaffUSI. This is because the StaffUSI is created in the ODS, but since the staff member does not exist in the ODS, we have to supply one for the dashboard. It will only exist in the context of the current Dashboard build. When the staff member is added to the ODS, then it will use that StaffUSI going forward.
The way that it is generating a StaffUSI is with the First Name and Last Name. The function convert(INT, convert(VARBINARY(max), s.FirstName + ' ' + s.LastSurname)) converts the First Name and Last Name together, into binary. Once it is in binary, then it is converting the binary into an integer, which is what the table is requiring.
Note: If there are staff members with the same name, then there will be a clash of StaffUSIs. It is suggested that more text is added to the first name or last name to indicate unique values.
The next section of the script inserts the staff StaffUSI, EducationOrganizationid, Staff Category and Position Title into the StaffEducationOrgInformation table. This will provide the claimset for the specific staff members. Since we previously added the StaffUSI and ensured all staff members existed in the StaffInformation table, then we are just joining and returning the added staff members and those that exist. We are then inserting that staff members that do not exist in the StaffEducationOrgInformation table.
The next section adds staff members into the StaffInformationExtension table. This is the table that works with the SSO to store the Staff State Id. The script only adds those staff members that do not exist in the StaffInformationExtension table.
The last part of the script is cleaning up the #Staff table. Because this is a temporary table, we do not want it taking up space or caching the information longer than it should. So we drop/delete it from the session.