User cannot login to dashboard

Purpose: If you are trying to answer the following questions, then this article will help give you answers.

• Does the user exist in the Dashboard?
• Does the staff member have a State Id Assigned?
• Does the staff member have the right dashboard claims?
• Does the staff member have the correct assignment in the ODS?
• Which staff members have an end date less than today?
• Does the staff member have a State Id in the ODS?

Objective: The following sections cover different scenarios of why a user may not be able to log into to the dashboard application. The example code below is a template and has been parameterized so that it can be used to quickly enter in staff members. Just copy and paste into the SQL script.

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

Dashboard


StaffInformation
Check to see if the user exists in the Staff information table. The code example below is using the Full name as criteria. It is using the like function so that all or part of the name can be entered in.
If you know the StaffUSI then you can change this to use the StaffUSI. By running the below query, if the user exists, then you can use the StaffUSI that is returned in the following queries.

SELECT [StaffUSI] ,[FullName] ,[FirstName] ,[LastSurname] ,[EmailAddress] ,[Gender] ,[HispanicLatinoEthnicity] ,[Race] ,[HighestLevelOfEducationCompleted] FROM [NeDOE_Dashboard_NeDOEStaging].[domain].[StaffInformation] WHERE FullName LIKE '%<User Full Name, nvarchar(75), FullName>%'

StaffInformationExtension
A requirement for a user to log in and work with the SSO integration is that the staff member has a staff state id. The following query checks to see if the staffusi exists in the extension.StaffInformationExtension table.

SELECT si.[StaffUSI] ,si.FullName ,[StaffStateID] FROM [NeDOE_Dashboard_NeDOEStaging].[extension].[StaffInformationExtension] sie LEFT JOIN domain.StaffInformation si ON si.StaffUSI = sie.StaffUSI WHERE si.StaffUSI = < Staff USI,INT,StaffUSI >

User Claims
For a user to be logged in and see their correct data, they must be assigned to the correct claim. The way that this is assigned is through the PositionTitle. The following query returns the users Position Title and Staff Category to make sure they have the correct claims. The query also shows the EducationOrganization that the user is assigned to.

If the user does not show up at all and you have confirmed that the user exists in the ODS, then the End Date in the Assignment may be filled in. Check the next query to confirm.

SELECT s.[StaffUSI] ,s.FirstName ,s.LastSurname ,[EducationOrganizationId] ,d.Description AS StaffClassificationDescriptor ,sct.Description AS StaffClassificationType ,[BeginDate] ,[PositionTitle] ,[EndDate] ,s.[Id] ,s.[LastModifiedDate] ,s.[CreateDate] FROM [edfi].[StaffEducationOrganizationAssignmentAssociation] seoa JOIN edfi.Staff s ON s.StaffUSI = seoa.StaffUSI LEFT JOIN edfi.StaffClassificationDescriptor sd ON seoa.StaffClassificationDescriptorId = sd.StaffClassificationDescriptorId LEFT JOIN edfi.Descriptor d ON sd.StaffClassificationDescriptorId = d.DescriptorId LEFT JOIN edfi.StaffClassificationType sct ON sct.StaffClassificationTypeId = sd.StaffClassificationTypeId WHERE s.StaffUSI = < Staff USI,INT,StaffUSI >

ODS

Staff Assignment
For a user to get the appropriate claims, they must have a record or an assignment to an educationorganization in the edfi.StaffEducationOrganizationAssignmentAssociation table. The below query will check if the user is in the table.

One thing to remember is that if the EndDate is populated and is earlier than today, then the record for the user will not be populated in the Dashboard.

SELECT s.[StaffUSI] ,s.FirstName ,s.LastSurname ,[EducationOrganizationId] ,d.Description AS StaffClassificationDescriptor ,sct.Description AS StaffClassificationType ,[BeginDate] ,[PositionTitle] ,[EndDate] ,s.[Id] ,s.[LastModifiedDate] ,s.[CreateDate] FROM [edfi].[StaffEducationOrganizationAssignmentAssociation] seoa JOIN edfi.Staff s ON s.StaffUSI = seoa.StaffUSI LEFT JOIN edfi.StaffClassificationDescriptor sd ON seoa.StaffClassificationDescriptorId = sd.StaffClassificationDescriptorId LEFT JOIN edfi.Descriptor d ON sd.StaffClassificationDescriptorId = d.DescriptorId LEFT JOIN edfi.StaffClassificationType sct ON sct.StaffClassificationTypeId = sd.StaffClassificationTypeId WHERE s.StaffUSI = < Staff USI,INT,StaffUSI >

Staff State Id
For a user's state id to show in the Dashboard and allow them to log in. It must be added as an Identification code to the staff member. The query below checks the edfi.StaffIdentificationCode table where the Identificaiton System Type is State. These get pushed to the extension.StaffInformationExtension table.

SELECT s.[StaffUSI] ,FirstName ,LastSurname ,si.[StaffIdentificationSystemTypeId] ,sist.Description ,[IdentificationCode] ,si.[CreateDate] FROM [edfi].[StaffIdentificationCode] si LEFT JOIN edfi.Staff s ON s.StaffUSI = si.StaffUSI JOIN edfi.StaffIdentificationSystemType sist ON sist.StaffIdentificationSystemTypeId = si.StaffIdentificationSystemTypeId WHERE sist.Description = 'State' AND s.StaffUSI=< Staff USI,INT,StaffUSI >

Staff Assignment End Date
The following query checks the edfi.StaffEducationOrganizationAssignmentAssociation table and returns all staff members that have an end date less than today. If a user is in this list, then the assignment record is not promoted to the dashboard.

SELECT s.[StaffUSI] ,s.FirstName ,s.LastSurname ,[EducationOrganizationId] ,d.Description AS StaffClassificationDescriptor ,sct.Description AS StaffClassificationType ,[BeginDate] ,[PositionTitle] ,[EndDate] ,s.[Id] ,s.[LastModifiedDate] ,s.[CreateDate] FROM [edfi].[StaffEducationOrganizationAssignmentAssociation] seoa JOIN edfi.Staff s ON s.StaffUSI = seoa.StaffUSI LEFT JOIN edfi.StaffClassificationDescriptor sd ON seoa.StaffClassificationDescriptorId = sd.StaffClassificationDescriptorId LEFT JOIN edfi.Descriptor d ON sd.StaffClassificationDescriptorId = d.DescriptorId LEFT JOIN edfi.StaffClassificationType sct ON sct.StaffClassificationTypeId = sd.StaffClassificationTypeId WHERE EndDate < GetDate()