Applicable version: v2.5.0

Description

The Directory Search feature added in SSP v2.5.0 included a materialized view to operationalize the likely large amount of data that can exist within SSP and external data. The materialized view was able to sustain performance while adding the new feature, expanded Student Search criteria and increased activity within SSP. The materialized view solution became the basis for both the Caseload list and Student Search results. All student records listed in the Caseload or Student Search results are based on querying the view. The view is a combination of operational SSP and external data that updated by several processes described below. The entire view is refreshed by processes 1-3 while updates are made based on several actions described in process 4. At the time of development, the performance sustained SSP records in the thousands and external person records nearing one hundred thousand.

Person Directory Processes

Refresh Process	Refresh Steps
The refresh process is the same regardless of the trigger to execute the refresh. Below are a list of the triggers that update the views/tables.	Refresh the Secondary Person Directory Disable operational triggers Point the Caseload list and Student Search to the Secondary Person Directory Refresh the Primary Person Directory Enable operational triggers Point the Caseload list and Student Search to the Primary Person Directory
Refresh Triggers	Trigger Description
1) Application startup	When the application starts, the Primary and Secondary Person Directories are updated from all data sources per the following steps:
2) External person sync	Per the task_external_person_sync_trigger configuration, the external person sync process executes. As soon as it completes, the directory refresh steps execute per the following steps:
3) Directory person refresh trigger	Per the task_directory_person_refresh_trigger, the refresh process can be executed per a schedule. By default, the trigger is disabled
4) Operational triggers	Updates to the person model, submission of Early Alerts and responses to Early Alerts all update the view transactionally

SQL Server Database Object Definitions

Description	Type	Object
Directory Definition	View	v_directory_person
Primary Person Directory	Table (materialized view)	mv_directory_person
Secondary Person Directory	Table (materialized view)	mv_directory_person_blue
Procedure to refresh the Primary Person Directory	Stored Procedure	REFRESH_MV_DIRECTORY_PERSON
Procedure to refresh the Secondary Person Directory	Stored Procedure	REFRESH_MV_DIRECTORY_PERSON_BLUE
Procedure to update the Primary Person Directory per transaction	Stored Procedure	update_directory_person_from_view_where_person_id
Procedure to update the Primary Person Directory per transaction	Stored Procedure	update_directory_person_from_view_where_school_id

PostgreSQL Database Object Definitions

Description	Type	Object
Directory Definition	View	v_directory_person
Primary Person Directory	Table (materialized view)	mv_directory_person
Secondary Person Directory	Table (materialized view)	mv_directory_person_blue
Procedure to refresh the Primary Person Directory	Function	refresh_mv_directory_person
Procedure to refresh the Secondary Person Directory	Function	refresh_mv_directory_person_blue
Trigger to update the Primary Person Directory per transaction	Trigger Function	update_directory_person_from_view_where_person_id
Trigger to update the Primary Person Directory per transaction	Trigger Function	update_directory_person_from_view_where_school_id
Trigger to update the Primary Person Directory per transaction	Trigger Function	update_directory_person_from_view_early_alert_id

Considerations

There are situations based on the timing of events whereby the Caseload list and Student Search may temporarily not be the most current. Based on the default task schedule configurations and known usage, the possibility of stale data is low and during low traffic periods. The following scenarios may result in stale data.

The time between the load of the external data and the completion of the refresh triggered by the external person sync.
The time during the use of the Secondary Person Directory that doesn't include the result of a triggered event (program status change, Early Alert submission and response)

Outside of the scenarios described above, the system solution to updating stale data is to enable the task_directory_person_refresh_trigger and set for a time to execute the refresh process as described above. This should only need to be done one time as the external person sync process should trigger a refresh of the view. Alternatively, the materialized views can be updated directly. There is a risk that the Caseload and Search will not work while the database processes are executing. The following queries could be used with the caution.

PG: select REFRESH_MV_DIRECTORY_PERSON()
SQL: exec REFRESH_MV_DIRECTORY_PERSON
PG: select REFRESH_MV_DIRECTORY_PERSON_BLUE()
SQL: exec REFRESH_MV_DIRECTORY_PERSON_BLUE

Also note that for SQLServer the "operational" SSP database user (db_username from $SSP_CONFIGDIR/ssp-config.properties) must be allowed to execute stored procedures. In most deployments this does not require special configuration, but in the event your security policies are such that that user must be explicitly granted execute permissions on specific stored procs, here are the statements which you would likely need to run. (Use 'dbo' for <schema> unless db_schema from $SSP_CONFIGDIR/ssp-config.properties is set to something else.

GRANT EXEC on <schema>.REFRESH_MV_DIRECTORY_PERSON to <ssp-operational-user>;
GRANT EXEC on <schema>.REFRESH_MV_DIRECTORY_PERSON_BLUE to <ssp-operational-user>;
GRANT EXEC on <schema>.update_directory_person_from_view_where_school_id to <ssp-operational-user>;
GRANT EXEC on <schema>.update_directory_person_from_view_where_person_id to <ssp-operational-user>;

Browser not supported