Dashboard Staff Insert Script

Purpose
The following article discusses the script created to insert staff that need to log into the dashboard. The following scenarios would be why this script is used:
· Staff has not been setup in the SIS
· Staff are state staff persons and need access to the LEA
· Staff should be LEA System Administrators, but their assignment/employment is something else


Implementation
Script Location
The script is currently in the Nebraska repository and can be found in the following location:
Repository: Ed-Fi-Apps
Location: Database\PostLoad\Dashboard
Name: 0012-InsertDashboardStaff.sql


#Staff Temp Table
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.
· FirstName - First Name of the staff member
· LastSurName - Last Name of the staff member
· EmailAddress - The email address that is stored in the SIS or ADFS
· EducationOrganizationId - The Local Education Organization Id or School Id that the staff member needs to log in to
· Staff Category - This is the role that the user will be logging in to in the dashboard
· PositionTitle - This is the role that the user will be logging in to in the dashboard and is the same as the Staff Category.


Staff temp table
CREATE TABLE #Staff (
[StaffStateId] [nvarchar](30)
,[FirstName] [nvarchar](75) NOT NULL
,[LastSurname] [nvarchar](75) NOT NULL
,[EmailAddress] [nvarchar](128) NOT NULL
,[EducationOrganizationId] [int] NOT NULL
,[StaffCategory] [nvarchar](100) NOT NULL
,[PositionTitle] [nvarchar](100) NOT NULL
)


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.


Staff Example
Insert into #Staff Values (2136458,'Sam','Simmons','ssimmons@isd.org',85457452,'LEA System Administrator','LEA System Administrator')


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.


Staff Template
Insert into #Staff Values (<Staff State Id,nvarchar(30),StaffStateId>, <First Name, nvarchar(75),FirstName>, <Last Name, nvarchar(75),LastName>, <E-mail,nvarchar(128),EmailAddress>,<Education Organization,int,EducationOrganizationId>,<Staff Category,nvarchar(100),StaffCategory>, <Position Title, nvarchar(100), PositionTitle>)

Note: To use templates in SQL Management studio, type ctrl+shit+m. Alternately you can navigate to the menu and selet Query->Specify values for Template Parameters


Staff Information Table
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.


Staff Information
INSERT INTO [domain].[StaffInformation] (
[StaffUSI]
,[FullName]
,[FirstName]
,[LastSurname]
,[EmailAddress]
,[HighestLevelOfEducationCompleted]
)
SELECT convert(INT, convert(VARBINARY(max), s.FirstName + ' ' + s.LastSurname)) AS StaffUSI
,--converts the full name into an integer
s.FirstName + ' ' + s.LastSurname AS FullName
,s.FirstName
,s.LastSurName
,s.EmailAddress
,'No Degree' AS [HighestLevelOfEducationCompleted]
FROM #Staff s
LEFT JOIN domain.StaffInformation si ON s.FirstName + ' ' + s.LastSurname = si.FullName
WHERE StaffUSI IS NULL
GROUP BY s.FirstName
,s.LastSurName
,s.EmailAddress

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.


Staff Education Org Information Table
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.

StaffEducationOrgInformation
INSERT INTO [domain].[StaffEducationOrgInformation] (
[StaffUSI]
,[EducationOrganizationId]
,[StaffCategory]
,[PositionTitle]
)
SELECT si.StaffUSI
,s.EducationOrganizationId
,s.StaffCategory
,s.PositionTitle
FROM #Staff s
JOIN domain.StaffInformation si ON s.FirstName + ' ' + s.LastSurname = si.FullName
LEFT JOIN [domain].[StaffEducationOrgInformation] se ON si.StaffUSI = se.StaffUSI
WHERE se.StaffUSI IS NULL


Staff Information Extension 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.


StaffInformationExtension
INSERT INTO [extension].[StaffInformationExtension] (
[StaffUSI]
,[StaffStateID]
)
SELECT si.StaffUSI
,s.StaffStateId
FROM #Staff s
JOIN domain.StaffInformation si ON s.FirstName + ' ' + s.LastSurname = si.FullName
LEFT JOIN [extension].[StaffInformationExtension] sie ON s.StaffStateId = sie.StaffStateID
WHERE sie.StaffStateID IS NULL


Drop Table #Staff
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.


Drop #Staff
DROP TABLE #Staff

Creation date: 12/30/2015 1:01 PM ()      Updated: 12/30/2015 1:13 PM ()