...
The Directory Search feature added in SSP v2.5.0 included a materialized view to operationalizeThe Caseload list and 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 a materialized view created during the development of SSP 2.5.0. 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
...
| Info |
|---|
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.
|
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.
| No Format |
|---|
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>; |