Installing the Hierarchical Travel Matrix¶

For dynamic scheduling it is usually recommended that a Hierarchical Travel Matrix (HTM) be used. Details of which countries are included in which HTM region are included in a later appendix. Further details on these HTM regions, including their final size and the disc space required for installation, can be found in the Architecture and Sizing Guide, which also details the matrix ID which should be set when using each respective HTM.

There are two ways to install a HTM into a database: automatically using the IFS PSO Installer, or, manually using database scripts. It is recommended that the automatic option is used in all cases, to avoid the risk of introducing errors.

Prerequisites¶

HTMs can be installed on either SQL Server, SQL Azure or Oracle databases. The installation process differs slightly for each database provider, however the first few steps are common:

• Download the data for the matrix to be installed. The data is comprised of the actual HTM data (locations, travels etc.) and installation scripts for each database provider (organised in folders with the database provider names).
• The HTM data is packaged in a compressed file, which needs to be unzipped once it is downloaded to the server hosting the target HTM database.
• You now should have a series of .csv files in a folder which will be used to load the HTM data to a database using the scripts in the corresponding folder (SQL, Oracle or SQLAZURE). Below are the instructions for each database provider.
• Once installation is complete, refer to 'Post-HTM-installation checks' below to check whether the HTM installation was successful.

Automated HTM Installation¶

The IFS PSO Installer can be used to create a new HTM database. If you wish to use the IFS PSO Installer for a HTM database install, you will need your licence key at hand. In the following sub-sections we provide instructions for installs against a local SQL Server or Oracle database, and for installations into a SQL Azure database.

Microsoft SQL Server Installation¶

After starting the IFS PSO Installer, you will be presented with the start page where you can select your licence key file and then select "HTM Install" from the Install Mode option - see the snapshot above. After clicking "Start" you will move to the Licence Details page where you may review the specifics of your licence key. In particular, you can see which HTM matrix IDs the licence is valid for - see below snapshot. No actions needs to be taken on this page so you may click "Next" to continue.

The HTM Options page allows you to select the type of HTM install that you would like to perform. The different options are as follows:

• HTM Folder: this is the root folder for the HTM containing both the 'scripts' and 'data' folders.
• Deployment Mode: either "Local Server" or "Azure". For SQL Server or Oracle installs you will select "Local Server", whereas for SQL Azure you will typically select "Azure", however it is possible to install an HTM to SQL Azure using the "Local Server" option (see the note at the end of this section for further details).

For a SQL Server HTM install, set the Deployment Mode to "Local Server" and then choose the relevant option for Install Mode. The next page you come to is the Databases page, where you will enter the connection string for your SQL Server database. Note that you will need to have a database already created and a user with sufficient permissions to create tables, etc., against the database that you can use for the HTM install. Once you have entered the connection string for your target database you may test that it is correct using the Test button. With a valid connection string, click "Next" to continue.

The Actions page will then summarise the steps that the IFS PSO Installer needs to take to perform the HTM install. To proceed with the install click "Start". You will be informed when a task is running (it will be marked as "Executing") and for the data insertion step there will be an estimated time of completion to provide a rough indication of the time remaining. Once all actions are marked as "Done" the install is complete. If at any point an error occurs information will be displayed on the IFS PSO Installer.

Microsoft SQL Azure HTM Installation¶

The installation process for SQL Azure is identical to that for SQL Server in the preceding section up to the HTM Options page in the IFS PSO Installer, the difference being that for a SQL Azure install you will set the Deployment Mode option to "Azure" (instead of "Local Server"), then click "Next". You will then come to the Subscriptions page where you should select the Azure subscription that you wish to install the HTM database under, then click "Next" to continue.

The Database Servers page allows you to select the database server that you will use for the HTM install. You will need to specify an appropriate database server name and select the Data Centre location where you would like the database server to be created - this should be as close as possible to the site.

On the Databases page, you may then set the database name to be used for the HTM install - by default this is "IFS Scheduling".

The next option is to select the database performance tier. See the Architecture and Sizing Guide for guidance on which tier to use. The tier can also be configured after installation via the Azure Portal.

The final option is to "Scale Database During HTM Install" which concerns the performance tier of the database to be used during the HTM install, which may differ from the performance tier required for day-to-day operations of the HTM database. We would typically recommended that the database is scaled to the mid-to-high performance level (e.g. an S7) as this will better handle the large volume of data that is inserted during the HTM install. Once the install has completed the database will be scaled back down to the value selected previously. The snapshot above shows an example of the Databases page.

The next page in the IFS PSO Installer is for Firewall configuration for the SQL Azure database server. Here you can add in permitted IP address ranges by either editing the default "all addresses open" firewall rule and/or adding in extra firewall rules. The snapshot below shows an example of the Actions page - notice that the penultimate action is to update the performance tier of the database to an S1.

The final step for a SQL Azure HTM install is to review the list on the Actions page and then set the install in motion by clicking the "Start" button.

Oracle HTM Installation¶

Installing an HTM into an Oracle database follows the same basic steps as for Microsoft SQL Server, differing at the Databases page in the IFS PSO Installer - please review the steps prior to that in the Microsoft SQL Server section above. For an Oracle install you will select "Oracle" as the provider type on the Databases page, after which you are required to enter a valid connection string as well as the login username and password for a DBA level user - see above snapshot. (The latter is required to grant certain permissions during the install process - if needed, specific details can be found by examining the steps in the manual installation section). Both the user id and the DBA user must be in upper case. Once the required details have been entered, clicking "Next" takes you to the Actions page where there is a summary of the steps for the HTM install - see snapshot below. To start the install, click "Start".

Manual HTM Installation¶

Microsoft SQL Server Installation¶

1. For V1 HTMs, put the extracted CSV files in the root folder of the HTM i.e. the folder containing the subfolders "SQL", "SQLAzure" and "Oracle"

   Warning

   This folder should not be located within a OneDrive folder as this can cause the install to fail.

   Note

   For V2 and V3 HTMs there is no need to move any files from their default location.

2. Create a database and set the Recovery Model in Options to Simple. Ensure that the drive where you are going to create the database has enough free space. Ensure that you have dbo rights in the new database.

3. Open a command prompt and 'cd' to the SQL sub-folder of the HTM data folder, e.g. cd \HTM\GB\scripts\SQL.
4. If you already have data in the database, use the drop indices and drop tables scripts to remove the existing data and tables (called "Hierarchical Travel Matrix Drop Indices.sql" and "Hierarchical Travel Matrix Drop Tables.sql".
5. Open the create tables script ("Hierarchical Travel Matrix Create Tables.sql"), ensure that the correct database is selected, and run it.

6. Run the batch file, "Hierarchical Travel Matrix Bulk Copy.bat", using four parameters: database name, server name, SQL server user name, password. e.g. "Hierarchical Travel Matrix Bulk Copy.bat" TravelGB localhost IFS_PSO mypassword. If using integrated security against a local database, it is only necessary to specify the database name.

   Note

   This process may take some time (possibly hours).

7. Once complete, run the primary indices script ("Hierarchical Travel Matrix Primary Indices.sql").

8. Run the create indices script (called "Hierarchical Travel Matrix Create Indices.sql"), which again may take some time.
9. When the script has completed, you can shrink the database.

Microsoft SQL Azure HTM Installation¶

SQL Azure HTMs should always be installed automatically using the installer. Manual installation is not supported.

Oracle HTM Installation¶

1. For V1 HTMs, put the extracted CSV files in the root folder of the HTM i.e. the folder containing the subfolders "SQL", "SQLAzure" and "Oracle".

   Warning

   This folder should not be located within a OneDrive folder as this can cause the install to fail.

   Note

   For V2 and V3 HTMs there is no need to move any files from their default location.

2. Choose an Oracle user name for accessing the HTM database e.g.'IFSHTM'. Create this HTM user in Oracle. The HTM user must have rights to create tables (including external tables), views, procedures, sequences and triggers. The HTM user requires the CONNECT and RESOURCE roles and does not require the DBA role. As the HTM is large (several GBs), consider creating a separate tablespace for the HTM user. Ensure that the TEMP tablespace is large enough for the HTM indexes to be created.

3. The default path to look for scripts must be modified to the folder (called Oracle) that contains the Oracle scripts provided with the HTM, e.g. C:\FrenchHTM\V1\Oracle. This setting can be found under Tools / Preferences / Database / Worksheet.

   

   Open a SQL Worksheet using a connection with the Oracle "SYS" user, using role "SYSDBA".

4. Execute the CreateDirectory.sql script. This script takes two arguments, the Oracle HTM user name (using the correct case) and the path (on the Oracle server) where the .csv files are located. Note that the path is assumed to be on the Oracle server: if you have the CSV files locally, then you must share the folder and specify a UNC path to it. If the path contains spaces then it must be quoted (using single quotes). Ensure that the script has run without errors.

   Note

   The CreateDirectory script performs the following commands.

   • DROP DIRECTORY "HIER_DIR"; (actually an equivalent PL/SQL command that does not error if the directory does not exist)
   • CREATE DIRECTORY "HIER_DIR" AS 'C:\HTM\AU';
   • GRANT READ, WRITE ON DIRECTORY "HIER_DIR" TO "IFSHTM";

   Warning

   The most common errors are:

   • running the script as a user without DBA rights
   • specifying the Oracle HTM user name with an incorrect case

5. Create a Connection for the Oracle HTM user created earlier (e.g. IFSHTM) and Open a SQL Worksheet using that connection.

6. Execute the create script ("Hierarchical Travel Matrix Create.sql") as the Oracle HTM user by typing the command @'Hierarchical Travel Matrix Create' in the worksheet and selecting 'Run Script'.
7. This script will produce errors! These are an artefact of the script supporting multiple HTM formats. The script will successfully process the database objects appropriate to the database version, and flag errors for database objects not appropriate to the database version. However, if the whole process completes in seconds, then there is probably some other problem: at least some of the CSV imports should succeed.
8. The script can take many hours to complete depending on the size of the HTM.
9. If installing a V1 HTM, run the script LoadMatrix_Bounds.sql. The parameter it asks for should be "Matrix_Bounds.csv". This step is not required for newer version HTMs.
10. Refer to 'Post-HTM-installation checks' below to check whether the HTM installation was successful.

Adding HD Tiles¶

Post-HTM-installation checks¶

Once the scripts have completed, there are a few checks that are recommended to verify that the installation was successful.

• Check that the scripts executed without any errors.

• Verify that the new HTM is working correctly by adjusting a test environment DSE to connect as the new HTM user. Check that the HTM is connected correctly and that the tiles appear as information in the event logs.

  Warning

  When pointing the DSE to a Sql Azure HTM database, make sure you use the full database name as the value for the data source field in the connection string of the DSE config, e.g.:

  data source=tcp:"server_name".database.windows.net,1433;

• Verify that the new HTM is working correctly with the DSE by sending the corresponding verification file (which is supplied with the HTM installation files).

Migrating to a new HTM¶

Updating the HTM that you are using to a newer version essentially involves installing the new HTM and then switching over the applications to point at the new HTM. Step-by-step instructions for this process are as follows:

• Install new HTM on new database.
• Test connection and performance using a test environment before releasing the new HTM onto your production system.
• Switch production system to new HTM during a suitable maintenance window (e.g. during off peak time). HTM connection details are stored in the System database and can be edited via the Administration workspace in the workbench. As such, HTM connections can be reconfigured without the need to reinstall the applications.
• Validate the new HTM in the production system including operation and performance. Should any issues be identified, switch back to the previous HTM.
• Once operation of the new HTM is stable and satisfactory, decommission the old HTM database. We recommend a timeframe of at least two weeks for this overlap.