Reference data tables are a powerful feature of Duco. With reference data tables a user can set up:

• Simple equivalence between values and codes. E.g., "Buy" and "B" are the same, or account code "ABC123" is equivalent to "987987"
• Set up a table with multiple columns
• Share the same table among processes
• Permissions to allow users to simply use a table, update its values, or add new columns.

Setting up a reference data table

To access the screen to manage reference data tables:

• Click More from the top of the screen.
• Choose Reference data

 

 

To create a new reference data table

• Click on Create table
• Enter a name and, optionally, a description for your table.
• Click on Create table

You will now need to set up the table structure. You can do it manually column by column, but the easiest and quickest way is to upload some sample data.

As an example we'll use this table.

You will notice that Duco detected the data type for each column and the file format based on the data from the sample.

Now the reference data table is in place. To use it, you need to first associate it with a matching process. You can do so from the Processes screen.

Now that the reference data table is associated with a process, you can navigate to that process and use the table.

For example

• Click on Processes
• Click on your process (in this case "FO vs BO")

 

A reference table is very flexible and can be used for various tasks, but typically it is used...

1. in the Rules and Rule sets screen in a text equivalence rule
2. in the Matched fields screen in a translation rule

The following section describes these common uses.

 

Setting up a text equivalence rule

Once a reference table is associated with the process you can set up a text equivalence rule.

• Click on Processes -> Rules and Rule sets
• Add a rule associated with a field (in this case "Counterparty ID") 
• Click on Text equivalence
• Choose the table you want to use (in this case "Parties")
• Choose the columns you want to use (in this case "FO ID" and "BO ID")

If you run the process, Duco will apply the text equivalence rule.

 

A simple reference data table for text equivalence

Consider the following set of results

In this case a front office system (FO) uses the values "Buy" and "Sell" to indicate whether a trade is a buy or a sell, whereas the back office system (BO) uses the values "B" and "S".

To handle this situation you can set up a simple reference data table and use a text equivalence rule associated with the "BuySell" field.

• Go to the "Rules and Rule sets" screen
• Click on "Add rule" in association with the "BuySell" field
• Select "Text equivalence rule", enter a name for your table (for example "Buy Sell") and click on "Save"

When you create a new table via the "Rules and Rule set" screen, Duco will show a link that takes you directly to the "Reference data" screen.

Update the data type in Columns screen if needed.

Press Insert row to add values to the table.

Add values and press OK.

You can add rows manually or upload a CSV file to populate the table. Zipped files can be uploaded here, but may only contain a single file and cannot be password protected.

 

Permissions

Access to reference data tables are controlled by roles in a similar way to processes. Multiple roles can be assigned to a given User or Group, allowing them the desired level of access to the table.

To access the screen to manage reference data tables:

• Click on More at the top of the screen
• Choose Reference data
  

This will return a list of all the tables that you have visibility to. 

• Select Settings next to the table you want to grant access to.
• Then choose Permissions from the menu on the left.

 

From here you can grant new Users and/or Groups access to the table. Different levels of access can be granted, depending on your requirements.

The below table outlines the different access roles for a given reference table:

 

 

Updating a Reference Table

When uploading data to an existing reference data table the following methods are supported:

• Add to existing rows - This will add any records in the upload as new rows in the reference data table
• Overwrite existing rows - This will delete all the existing rows and replace them with the rows in the upload file
• Update based on tracking columns - This allows you to mark one or more columns as tracking columns*, enabling the Reference Data table to operate in an ‘update’ mode where files can be uploaded to both add new data and amend existing rows.

*Note: Only columns that have a type of ‘text’ can be tracking columns.

 

Uploading data

To upload a file to an existing table, open the target table and select Upload data. This will then present you with the options to either add to the existing rows, or to overwrite them completely. Note that any file you upload at this stage needs to have the same file type and structure as that used when originally creating the table.

 

Uploading an Update File 

In order to upload a file that will amend the existing data, you first need to identify Tracking columns. These will be the columns that you anticipate uploading updated data for in the future. Only then will the upload option Update based on tracking columns appear. Enabling this feature puts the reference table into 'update mode'.

To do this, check the Tracking Key checkbox against the column/s that you want to use. Remember, these must contain text values.

 

Be aware that setting a column as a tracking column is only be possible when there is no data in the reference data table. As such, when you create a table using a sample file, where the table will be automatically loaded with records, you will need to subsequently delete the records in order to enable the Tracking Key.

When in ‘update mode’ and there is data in the table, the following restriction will apply:

• • Structural changes on the reference data table will be disabled (e.g. column reordering, adding a new column, removing a column)
  • You will not be able to deselect a column from being a tracking column or enable a new column as a tracking column

When a Reference Data Table is in ‘update mode’ it will be possible to load files in either of the following two ways:

• • Update - Any records in the upload file that have the same values in the tracking columns as existing rows in the table will cause the rows in the table to be updated. All other records in the upload file will create new rows in the table. 
  • Overwrite - All rows in the table will be deleted and replaced by the records from the upload

When uploading a reference data file through the UI the user will be able to choose whether the upload will be an “update” or “overwrite”

 

 

When setting up SFTP Submission routing to a Reference Data table that is in ‘update mode’, by default it will operate as an Overwrite with an option to process as an ‘Update’ (similar to the Append option available today)

 

When in ‘update’ mode the system will validate if the file being uploaded has more than one record with the same tracking column values. If so, the latest of the records in the upload file with the same tracking column values will be processed with the earlier records with the same tracking values being ignored.

Any records that are ignored during either manual upload or SFTP upload will be shown via a warning in the Reference Data Table UI stating that X number of records were ignored.

 

When in ‘update’ mode and a user is inserting a row manually through the UI, note the following behaviour:

• • Once a new row is created and upon clicking OK to enter a value into a column of the new row, if a row with the same tracking key values already exists an error message will be displayed. As the system creates the record and updates each column value independently this means that users will need to enter the unique tracking values first
  • If the user adds a row and does not enter any column values and then also tries to add a second row, the system will prevent the second row being created and show an error. This is because the system will treat the columns values on the incomplete blank row as null values and a new incomplete row will also have null values, thus the system will prevented the second row being created as it will be a duplicate  

Sharing a reference table with multiple processes

A single reference table can be connected to multiple processes. To make a connection, go to the reference table and select Settings and then Processes from the menu on the right of the screen.

 

On the next screen you will then be able to search for the processes you wish to connect with.

 

 

As many processes can be linked to the table as is required.

 

Once a connection is made, the reference table will then be available to the process for NRL, Rules, Filters, etc.

 

How to migrate an existing reference data table to operate in update mode

In order to set up an existing reference data table to operate in 'Update mode' you will need to clear all the transactions first the easiest way to do this is to:

• • Submit an empty file with no records using the 'overwrite' option. This can be done by either selecting Overwrite existing rows when uploading data from the reference data table UI or by unchecking the 'Append' option in the Submission routing settings and submitting via SFTP
  • Note - If your format expects a header then the file should contain only the header row and no record rows. 
• Once the table is empty, you will be able to go into the reference data settings and set the necessary column(s) to be a tracking column
• In the Submissions routings settings relating to this reference data table you will need to change the settings to operates in Update mode rather than overwrite by enabling the ‘Update & Append’ button
• Submit a reference data file that contains a full population of records to create a baseline for subsequent updates:
  • As the table will be in 'update mode' and there are no existing rows all rows will be inserted
• At any point going forward submit your delta upload files and existing rows will be updated and new rows appended/created