Achievement Rates Cached Data
View contentsThe achievement rates are also referred to as QAR for shorthand on this page.
The achievement rates reports used cached data due to the complexity involved in deriving the data. This data should be regularly refreshed to ensure it is up to date. In most cases this should be done by setting the stored procedure UPDATEALLTRAINEEPPRSTATS to run on a schedule in SQL Server (please contact your IT administrator for information about scheduling the procedure). It is recommended that the cache update is run out of office hours, as it may impact server performance while in progress and can potentially take several hours on very large databases.
TRAINEEPPR Tables
The cache update populates a series of tables with a name beginning with TRAINEEPPR. Details of the tables are as follows:
| Table | Data Stored |
|---|---|
| TRAINEEPPR | Apprenticeship QAR data using the latest ruleset, for showing anticipated future results |
| TRAINEEPPR_REC | Apprenticeship QAR data using the latest ruleset, for reconciling with published results |
| TRAINEEPPR_1819 | Apprenticeship QAR data using the 2018/19 ruleset, for showing anticipated future results |
| TRAINEEPPR_REC_1718 | Apprenticeship QAR data using the 2018/19 ruleset, for reconciling with published results |
| TRAINEEPPR_1718 | Apprenticeship QAR data using the 2017/18 ruleset, for showing anticipated future results |
| TRAINEEPPR_REC_1718 | Apprenticeship QAR data using the 2017/18 ruleset, for reconciling with published results |
| TRAINEEPPR_1617 | Apprenticeship QAR data using the 2016/17 ruleset, for showing anticipated future results |
| TRAINEEPPR_REC_1617 | Apprenticeship QAR data using the 2016/17 ruleset, for reconciling with published results |
| TRAINEEPPR_1516 | Apprenticeship QAR data using the 2015/16 ruleset, for showing anticipated future results |
| TRAINEEPPR_REC_1516 | Apprenticeship QAR data using the 2015/16 ruleset, for reconciling with published results |
| TRAINEEPPR_1415 | Apprenticeship QAR data using the 2014/15 ruleset |
| TRAINEEPPR_ET | Education & Training QAR data using the latest ruleset, for showing anticipated future results |
| TRAINEEPPR_ET_REC | Education & Training QAR data using the latest ruleset, for reconciling with published results |
| TRAINEEPPR_ET_1819 | Education & Training QAR data using the 2018/19 ruleset, for showing anticipated future results |
| TRAINEEPPR_ET_REC_1819 | Education & Training QAR data using the 2018/19 ruleset, for reconciling with published results |
| TRAINEEPPR_ET_1718 | Education & Training QAR data using the 2017/18 ruleset, for showing anticipated future results |
| TRAINEEPPR_ET_REC_1718 | Education & Training QAR data using the 2017/18 ruleset, for reconciling with published results |
| TRAINEEPPR_ET_1617 | Education & Training QAR data using the 2016/17 ruleset, for showing anticipated future results |
| TRAINEEPPR_ET_REC_1617 | Education & Training QAR data using the 2016/17 ruleset, for reconciling with published results |
| TRAINEEPPR_ET_1516 | Education & Training QAR data using the 2015/16 ruleset, for showing anticipated future results |
| TRAINEEPPR_ET_REC_1516 | Education & Training QAR data using the 2015/16 ruleset, for reconciling with published results |
| TRAINEEPPR_ET_1415 | Education & Training QAR data using the 2014/15 ruleset |
| TRAINEEPPR_TS | Traineeship QAR data using the latest ruleset, for showing anticipated future results |
| TRAINEEPPR_TS_REC | Traineeship QAR data using the latest ruleset, for reconciling with published results |
| TRAINEEPPR_WPL | No longer used - previously stored WPL QAR data |
Information on the difference between anticipated future results and reconciliation data can be found here.
The tables without a year suffix (e.g. TRAINEEPPR, TRAINEEPPR_REC, etc) always use the latest available ruleset. When a ruleset for a new contract year is implemented, the previous ruleset is then moved to a new set of tables. E.g. When the 2020/21 ruleset is implemented, the 2019/20 ruleset will be moved from TRAINEEPPR to TRAINEEPPR_1920, TRAINEEPPR_REC to TRAINEEPPR_REC_1920, etc. This means any custom reports which use the base TRAINEEPPR tables will continue to use the latest rules without requiring changes to the tables.
Configuring the Cache Update
Config options are available to restrict which tables are populated in the QAR cache update and how many past years are included. For large databases it is strongly recommended to review these configs and decide which tables need to be updated as it can hugely improve the time it takes to run the cache.
For example, if you only require the apprenticeship QAR data and not the ET QAR data, the configs can be set so the ET tables are not updated. You also may not require previous rulesets to be updated, in which case only the base TRAINEEPPR tables should be updated.
To access the configs in Maytas:
- Click the M5 button at the top-left and select Options.
- Select Reports Library on the left.
- The relevant configs can be found in the Achievement Rates section on the right.
The configs are as follows:
| Config | Description |
|---|---|
| Calculate the [ruleset year] Apprenticeship QAR | Enables the cache to update for the apprenticeship QAR for the specified ruleset |
| Calculate the [ruleset year] Education & Training QAR | Enables the cache to update for the ET QAR for the specified ruleset |
| Limit old QAR data in the [ruleset year] apprenticeship cache | When enabled, data from more than 5 years before the relevant ruleset year is ignored in the apprenticeship cache update |
| Limit old QAR data in the [ruleset year] ET cache | When enabled, data from more than 5 years before the relevant ruleset year is ignored in the ET cache update |
When the Limit old QAR data configs are enabled, data from more than 5 years before the relevant ruleset year is ignored in the cache update. For example, for the 2016/17 ruleset, data is ignored if the planned end date and actual end date are both before 01/08/2012 and the actual end date is not null. For 2015/16, the same logic applies but for 01/08/2011, etc. The cut-off dates for each ruleset are as follows:
- 2019/20 – 01/08/2015
- 2018/19 – 01/08/2014
- 2017/18 – 01/08/2013
- 2016/17 – 01/08/2012
- 2015/16 – 01/08/2011
- 2014/15 – 01/08/2010
With these configs enabled, the cache update will in most cases be significantly faster, particularly if your database has a lot of data from 5+ years ago. With the configs disabled, data for all years is included in the cache update.
Cache Log
The QAR cache update has a log table which is updated every time the cache is run. The table is called QAR_CACHE_LOG. When the cache update completes successfully, a row is added to the table with a status of Completed, along with the start date/time, end date/time and duration. If the cache update encounters an issue, the status will be Failed and the error message will be recorded in the Details column. If the error was for a particular learner (e.g. due to invalid data), the trainee ID and POT are also recorded (and the aim number for ET aims).
A report is also available to view the log - it can be found in the Quality folder of the reports library and is called QAR Cache Update Log.