This article is designed for administrators who understand your Mosaic installation, you may need to share this article with your local support team.
The database job populate reports populates the Mosaic datamart tables which are used for reporting. The job runs the stored procedure p_populate_report_tables.
The populate reports job is a daily job schedule to run out of hours. The start time varies from customer to customer depending on how long the job takes. Typically, the job will start between 7pm and 10pm and will take 2 - 4 hours although there are exceptions.
Because the job needs to be run for reporting purposes it is key to the process of copying the Mosaic database (usually called mosprod) to the reporting instances of Mosaic (usually called mosrep).
Therefore, the jobs below should be run in the following order:
Run the Populate reports table job.
Backup the mosprod database.
Restore the mosrep database from the latest backup of mosprod.
It is recommended that the three jobs above are chained, i.e. Job 1 triggers Job 2 on completion which then triggers job 3 on completion. This will ensure all jobs complete in order and the whole process is successful. For some customers you may find that the jobs above are not chained and are scheduled individually with sufficient time between each of the job start times.
Troubleshooting
The Populate Reports Job writes logs to the database table report_logs for each of it steps. The final step on completion is called Finished Populate_Package and will be time stamped. The second to last step will record the time taken for the job to complete in seconds.
To query the database log run the following SQL:
For Oracle
alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS'; select * from report_logs order by time_stamp desc;
For SQL Server
select * from report_logs order by time_stamp desc;
Below are some examples of problems you may encounter with the Populate Reports Job.
Symptom | Checks | Resolution |
Job started but did not complete. | Compare the logs on the mosprod database with the mosrep database. If the job completed on mosprod but not on mosrep then this indicates that the database mosrep was copied before the job completed. | Manually run the job to copy mosrep from mosprod again. |
Reports data is out of date. | NB. Due to the nature of the reporting refresh process it is normal for logs to have yesterdays date in the mosrep database. | Manually run the job and correct the job schedule. |
The job completed but reports are incorrect. | Check the log for errors. Individual steps could have failed as a result of errors. | Report any errors to the reporting development team |
The job took a long time to complete. | Check the logs for steps which took an usually long time. If the form steps are taking a long time check the switch value SYS Form Answers Refresh, see SQL Below SELECT * FROM REPORT_SWITCH_MAPPINGS where SWITCH_NAME='SYS Form Answers Refresh' If the value is set to Full Refresh change it to Monitor Changes which will run much quicker. |
|