SSP Person Directory
- Jason Elwood
- Daniel McCallum
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 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>;