Database SizeΒΆ
The size of the archive database will depend principally on 4 factors:
- The size of the datasets in the Scheduling and Resource Planning databases.
- The number of days that audit rows are retained for.
- The settings for the frequency of taking snapshots and the expiry periods of these snapshots.
- Whether the 'Generate Reporting Data' option is selected or not.
The table below gives an example of the space required when archiving a typical day of scheduling data. This is based on customer data which featured heavy use of appointment booking. The modelling database was not used.
| Measure | Value |
|---|---|
| Total activities in dataset | 10,000 |
| Total resources in dataset | 200 |
| Size of initial load input file | 30Mb |
| Total input files sent in one day | 10,000 |
| Total size of input history files for full day | 50Mb |
| Size of archive database after processing one full day | 200Mb |
| Size required by each dataset snapshot | 25Mb |
Based on this data, we can expect that with the default audit retention setting of 30 days retention, the size of the audit table would grow to around 6Gb, and then be maintained at this level. If in addition we assume that snapshots are taken twice per day, and that these have an expiry of 30 days, then we can expect the snapshot table to grow to around 1.5Gb, and be maintained at this level.
This means that for a system processing the dataset used in this example, and with the settings described above, the likely final size of the archive database would be around 7-8Gb.
Note
These values depend heavily on the settings used for auditing data and taking snapshots. For example, with the same data, if the 'AuditRetentionDays' parameter was set to 7 (so audit data was deleted after 1 week), then the audit table would only be expected to grow to 1.4Gb. If snapshots were also only kept for 7 days, then the snapshot table would only be expected to grow to around 350Mb. This would give a total database size of less than 2Gb.
Modelling datasets should generally take up less space than scheduling datasets, particularly in the audit table since changes are usually much less frequent. The table below gives a typical example of the space required for snapshots:
| Measure | Value |
|---|---|
| Total resources in dataset | 500 |
| Total weeks of planned shifts | 12 |
| Size of ARP export file | 30Mb |
| Size required by each modelling dataset snapshot | 15Mb |
If modelling snapshots are taken once per day and kept for 30 days then the space required by the modelling dataset is likely to be around 500Mb.
Note
While these examples can be used as a basis for determining the expected size of the Archive Database with any given set of data, it should be noted that there is unlikely to be an exact correspondence. In particular, an important factor in determining the space required for scheduling datasets is the amount of output changes being generated by the DSE. This can vary significantly depending on the data being scheduled, and is heavily affected by factors such as the number of scheduling exceptions being reported.
Note
The examples above do not include reporting data. The Archive Database is expected to be larger due to the 'fact' tables, in which records will be inserted with each snapshot. No reporting table data is expected to be deleted or cleared out by the Schedule Administration Service, as this data is meant to be used for reporting over months, even years. See example below for the added size to the Archive Database when using the 'Generate Reporting Data' option.