Electronic Capacity Model Tool (ECMT) - User Guide

Content

Introduction

To present the TTR process and possible capacity views of the capacity model, an offline application (based on Access and Excel) was created. The first serves the need for the graphical user interface, like forms, and the second serves the graphical presentation on the space-time diagram.

The aim of this document is to show the implemented functions and provide the necessary knowledge regarding the rules of the tool that should be respected. As an attachment, you can also find here always the latest version of the database, ECM file or the code itself in a .txt file.

Notice: the PC where the application was created and also the screenshots were taken has Hungarian language settings, that is why the titles on the pictures might look funny for some of the readers. However, the description should be adequate enough to follow the steps.

Database

The tool has no access control support, but there is an indication of the different roles between RU and IM. After opening the database, a default window is always the login screen. The user can decide to:

  • Log in as RU
  • Log in as IM
  • Close the application

Data model

Database tables, queries and reports are not hidden, the user can see them on the left bar of the tool. Before going into details, let's check briefly the current data model of the tool (for the detailed picture please select the database tools option on the top menu, then select connections).

  • Infrastructure data: locations are stored in a separated table and they are linked to predefined lines. Below you will see the available attributes. Some of them are not used in the ECM, just stored here as an information, e.g. if the line is electrified.
    • INFRA_LINE: stores the lines with some basic information. Before deleting any record in INFRA_LINE, all the operation points should be deleted in INFRA_OP that are on the particular line.
      • ID: give automatically by the database
      • Line number
      • Is electrified
      • Top speed
      • Gauge
    • INFRA_OP: stores the operation points belonging to a line. Before deleting any record in INFRA_OP, all the TCRs, TT elements and capacity elements should be deleted first that are using the particular operation point.
      • Name (serves also as the primary key): it should be unique. In case of a junction, when the point is part of more lines, the line number could be added to the name of the station to keep it unique.
      • Sort order of the operation point on the line to help the tool presenting the geography in a correct order
      • Distance information between the subsequent locations
    • Administration form is available also to manage the lines and operation points in an easy way as you can see it below.

  • TCR: one table and a form are available for TCR (Temporary Capacity Restriction) management.
    • ID: given by the database
    • Description: name of the TCR. It is shown in the tooltip also on the chart
    • Colour of the TCR that should be used for the presentation (colours are predefined in the database, but new codes can be added directly to the Colour table with its RGB values)
    • Harmonization status: status to simulate the work in progress. Currently, it's only a stored information, there is no restriction connected to this field
    • TTP (timetable period): where the TCR is published. Only a stored information, there is no restriction connected to this field
    • From/To OP: the user can select the operation points from a list. The two points must be on the same line, but it's not necessary to select neighbouring stations.
    • From/To time: time should be entered in a "hh:mm" format. The tool can support max. 24 hours presentation that is why midnight crossing is not supported. In that case, the user is recommended to split the objects.
    • From/To date: validity period of the TCR. There is no restriction between these fields and the timetable period. On the chart (see that later), the date filter is checking these fields.
    • Circulation days: the user can indicate on which days the TCR is valid (Mon-Sun)
  • Capacity management for capacity bands
    • CAPACITY_HEADER: before deleting any record in this table, the capacity elements should be deleted first that are part of the particular capacity.
      • ID: given by the database
      • Colour of the band that should be used for the presentations
      • Harmonization status: status to simulate the work in progress. Currently, it's only a stored information, there is no restriction connected to this field
      • TTP: where the capacity is published. Only a stored information, there is no restriction connected to this field
      • From/To date: validity period of the capacity band. There is no restriction between these fields and the timetable period. On the chart (see that later), the date filter is checking these fields.
      • Circulation days: the user can indicate on which days the capacity band is valid (Mon-Sun)
    • CAPACITY_ELEMENTS
      • Operation points where the capacity is published (not necessary to put there all operation point from the line, but never miss an intermediate operation point)
      • From/To time: times should be entered in "hh:mm" format. The tool can support max. 24 hours presentation that is why midnight crossing is not supported. In that case, the user is recommended to split the objects.
  • Timetable management for catalogues and paths. Structure of the two objects is the same.
    • TT_HEADER/CT_HEADER:
      • ID: given by the database
      • Business name: name of the path or catalogue. It is shown in the tooltip on the chart. It can be used also to connect more path or catalogues, like today's PaP ID is used.
      • Colour of the line that should be used for the presentation
      • Line type that should be used for the presentation (solid, dash, dot)
      • RU/IM Harmonization: status to simulate the work in progress. Currently, it's only a stored information, there is no restriction connected to this field
      • TTP check-boxes (path only): editable only for the RU to indicate the rolling planning request for the 36 months.
      • From/To date: validity period of the path or catalogue. There is no restriction between these fields and the timetable period. On the chart (see that later), the date filter is checking these fields.
      • Circulation days: the user can indicate on which days the path and catalogue is valid (Mon-Sun)
    • TT_ELEMENT/CT_ELEMENT
      • Operation points where the path or catalogue is requested/offered (not necessary to put there all operation point from the line, but never miss an intermediate operation point)
      • Arr/Dep time: time should be entered in "hh:mm" format. Please enter always both times, even if it is a run through. In such case, please enter the same time for arrival and departure.
      • From/To time (path only): time should be entered in "hh:mm" format. For TTP2 and TTP3 the IM can offer only bandwidth as an offer. The tool can support max. 24 hours presentation that is why midnight crossing is not supported. In that case, the user is recommended to split the objects.

Menu/forms

The tool has a simple menu with several forms to help the data entry. Instead of the direct work in the database tables, the user can enter those values on forms. RU and IM users have similar options but customized to their needs.

  • RU menu

  • New request: opens the commercial needs form for entering path request
  • Request query: gives an overview of the entered path requests in a simple table view
  • Request report: a printable version of the request query. Each request has a page with the entered information
  • Capacity query: gives an overview of the published capacity bands in a simple table view
  • Capacity report: a printable version of the capacity query. Each capacity has a page with the published information
  • Request catalogue: RU user can mark published catalogue paths as requested
  • TCR query: gives an overview of the published TCRs in a simple table view
  • TCR report: a printable version of the TCR query. Each TCR has a page with the published information
  • IM menu

  • Answer requests: opens the commercial needs form for answering the path requests. Only the common and IM fields are editable, IM cannot change TTP setting or requested arr/dep time.
  • Overview of requests: gives an overview of the entered path requests in a simple table view
  • Infra data admin: opens the form for managing the infrastructure data in the system as it was described already in a former point
  • New TCR: opens the TCR form on a blank page to publish new TCR
  • TCR query: gives an overview of the published TCRs in a simple table view
  • TCR report: a printable version of the TCR query. Each TCR has a page with the published information
  • New capacity band: opens the capacity form on a blank page to publish a new capacity
  • Capacity query: gives an overview of the published capacity bands in a simple table view
  • Capacity report: a printable version of the capacity query. Each capacity has a page with the published information
  • New catalogue path: IM user can define new catalogue path
  • Catalogue path query: gives an overview of the published catalogue paths in a simple table view

ECM generator

A macro-enabled Excel file is provided for generating the space-time diagram, to present the ECM. It has a synchronization connection to the database (for further details please check the following main chapter). Due to the database connection, the red worksheets of the Excel can be updated directly from the database. Please note that the name of those worksheets cannot be changed.

  • Infrastructure
  • Capacity
  • TCRs
  • Path
  • Catalogues

Drawing of the different objects can be customized, meaning not every worksheet is necessary always in the file, however, the infrastructure sheet must be presented always, regardless which object the user would like to present in the model.

The first worksheet called "Customization" where the user can customize the size and scales of the space-time diagram also called as "Chart". The program draws always one chart at a time for one given timetable period and selected validity period. However, below each other, the program will draw a separated chart for each available line of the infrastructure.

Back to the customization sheet, here you can see what kind of options are available for the users.

  • Height/width: these are the values of the chart in pixels.
  • Start/end time on chart: it's a number format, only for the presentation above the chart. It should be in line with the start/end time calculation field
  • Start/end time calculation: the program draws the objects on the chart according to the ratio of the given time values and these limits. Only those objects will be drawn that are in between the given times.
  • Line per x minute: the user can decide with which frequency the program should draw a thin line. The thick lines are drawn for every hour.
  • From/to date: each capacity product has a validity period. With this field, the program can filter only the valid capacity products.
  • Running days filter: each capacity product has circulation days information. With this table, the program can filter only those capacity products, that are valid at least at one of the selected circulation day.

The user can start the generation of the chart with the different drawing option buttons. Everything can be drawn together or the user can select a drawing piece by piece.

Note: before clicking the draw all or other options, please be sure that the workbook does not have any worksheet with the name "Chart", otherwise error message will be shown.

The connection of the database to the chart

The Excel and the Access database has five connections between each other to keep the synchronization:

  • Capacity bands
  • TCR overview
  • Paths
  • Catalogue paths
  • Infrastructure

Each of them as a database query behind is called from the Excel with the refresh button, once the connection is established. To see the existing connections, please go to Data and select the queries and connections option.

Then, you will see the existing connections on the right side of the workbook.

Import data via new connection

Using an existing connection (see next chapter) is ideal, when the user works with the database and chart on his/her workstation. In that case, refreshing the data via the already established connection is easy and quite useful. However, when the application has to be set-up for a first a time or right after sharing it via e-mail, it might be better to remove the old sheets and connections and add new connections.

Add a new connection

Go the Data window of Excel and select the first option on the left side. The second option will be the import from the database. Among the database options, please select the Microsoft Access Database.

Browse your database

Using the windows explorer, please browse and then select your database and click on the import button.

Select the queries

In this step, please select which information you would like to import from the database. For that, please select the proper query from the list from the top. The names of the queries are self-explanatory (Commercial needs overview = paths).

Select one of the queries. And then click on the import (first among the buttons at the right bottom corner).

Repeat

Repeat the above steps until you have all the sheets from the Database in your Excel. Based on the experiences, the infrastructure sheet is the most stable, maybe that’s not necessary to reload all the time, but the rest of the sheets can be reloaded anytime. Please note, it’s not necessary to load all the queries to the Excel, only those that you like to present on the chart, e.g. catalogues (Infrastructure is a must sheet).

Refresh data via existing connection

Let us assume that the database has been updated by a user, e.g. new lines or new paths were added. In this case, the chart view would need an update. Excel user has the option to update all connection or only one of them.

Update all connection

Go again to the same Data window of the Excel and click refresh all.

Update one connection

First, go the worksheet that should be updated. Click in the data area (select any of the cells of the data).

Go to the same Data window of the Excel, but now select only refresh (not all). The right picture shows the result that application starts loading the new data.

Update connection settings

It's possible to send the application among the users, however, it results that the connection between the two files will become broken (due to invalid local references).

Recommendation: if the purpose is to simulate the steps of the work without the graphical presentation, then it's enough to send only the database file; the Excel is only needed when the view should be generated.

Fortunately, there is a way to manage the connections. We will need the queries and connections view again, on the right side of the workbook, as it was shown before. If you don't have it, please check the first pictures of this main chapter. The following steps will show how to modify the data connection from one database (old original) to another (freshly downloaded).

Right-click on one of the connection (as the route to the database will be updated, it doesn't matter which connection we change now, after that, all of them will be automatically changed) and select edit.

As a result, Excel will open a new dialogue, showing a preview of the current connection.

Somewhere on the right side of the top menu please select the settings of the data source.

As a result, Excel will open a new dialogue showing the current route to the database.

Select the source modification option on the left bottom of the screen.

As a result, Excel will open a new dialogue where it’s possible to browse for a new route.

Click browse and select the new database (e.g. the freshly downloaded).

Look for the new database and when you’re done, open it.

After that, you will need to confirm the change in the previous dialogue. Click OK to continue.

As you can see, the dialogue will show already the new route. To finish the action, press close.

To see the result of the action, on the left side of the top menu, please use the update preview option.

As a result, Excel will show you now the preview from the new database (e.g. a new line as now).

Then simply just select the close this window with the “x” on the top right corner. It will ask you afterwards a question, whether you like to keep the changes you made. Please select yes, I would like to keep them.

After that, you will be returned to the workbook and you can see that all the connections will start loading immediately to update to the new database.

Update ECM code

The drawing algorithm of the ECMT is also stored in a separated file and it's possible to update, in case of any bug fix. The following guide will show you how to update the code in your Excel via a few steps. Before starting with the steps, please download the attached .txt file from this documentation.

Open Visual Basic editor

Go to the Developer tab of your Excel (if it's not visible, you can enable that via the settings and customization of ribbon) and select the Visual Basic icon on the top left corner. A new dialogue will be opened, where among the available modules, please select "TTR".

Replace code

On the right side, in the window, you can see the current code. The only thing you should do is to select all, delete, and then, paste the new code that you downloaded with the .txt file. Then save the Excel.

 

 

 

File
Details
State
Current
Area
Release
Company Type
Keywords