Introduction to DTCC Functionality

Hi and welcome to the PilotFish eiConsole. Today we’ll be taking a quick look at the DTCC-related functionality available in the eiConsole.

POV to CSV Route

To do this, I’ll open up this POV to CSV minimal route. In this route, we’ll be receiving data via a directory Listener, converting it to XML using our file specification editor and the flat file transformation module and then routing that data to a single Target Transformation.

Target Transformation Process

The Target Transformation will map these fields from the DTCC file onto an XML representation of CSV, convert that data to CSV, and ultimately use a directory Transport to drop that in a downstream directory so that the Target System can pick it up for further processing.

DTCC File Format and Parsing into XML

DTCC files look a bit like what I’ve shown you on the screen right here – it’s an EDI-type format where the first 2 to 4 bytes of each line specify the structure of the file that we need to parse.

The first thing we’ll need to do is parse this into an XML representation so it can be processed through the remainder of the eiConsole. If we click on the Source Transformation, we can see that this is done with the delimited and fixed-width file transformation module.

File Specification Editor

We can open up the file specification editor to see exactly how we define a DTCC EDI format for the file specification editor and engine to process. The tree on the left-hand side of the file specification editor describes the structure of the file that we need to parse and our job when describing a new format to the file specification editor is to build this tree.

Tree Structure in File Specification

You’ll see that we have 3 components in this tree, record types – each of these represents a record type in this case, a DTCC file. Fields are represented by each of these blue nodes – in this case, represented by a name, start position, end position and length.

And in the case of a complex flat file, a record control. What this record control is doing for us is helping us take a look at those first 2 to 4 characters of each line to determine which of these record types it represents.

Using the Data Mapper in the eiConsole

Once we’ve parsed out the file and we’ve turned it into XML, now what do we do with it? Well, in this particular case, we want to turn it into CSV but we could do any number of things with it. To turn it into CSV, we’re going to use the data mapper, which you can see has been added as a Target Transformation.

Here in the data mapper, you’ll see that we have our Source format on the left – in this case, it’s our XML representation of a DTCC file. We have our Target format on the right – in this case, it’s a CSV format. And we have our mapping in the middle.

Now this mapping is pretty straightforward. We’re going to take our XML representation and for each contract element, we’re going to create a new row in our CSV. For each field within that contract, we’re going to create a new column. We map these simply by dragging & dropping from the Source to the Target. Once we’ve done that we can test this to make sure it works as expected.

Testing the Route

Let’s take a peek at how this rolls back. Let’s leave the data mapper, go back to the Test Mode and take a peek at how this looks. Now in testing mode, I’m again going to start with my Source Transformation but this time I’m going to allow this to run all the way through the data mapping. I’ll again select my same sample file and you can see that we’ll have our DTCC EDI format as the initial data, as before the data is parsed into an XML representation. But now we can take a look at the output of the XSLT stage. The output of our data mapper now shows that we’ve processed one contract – contract number one – and we’ve pulled out its CUSIP.  We found the appropriate process date, etc. fields that were not present in the input are simply left as empty and will ultimately be rendered as empty columns in our CSV.

Testing the Routes Final CSV Transformation

Looking once again at the overall route we can see that the final step is a simple CSV transformation module. At this point, the route knows nothing about the fact that this was originally a DTCC format, it’s just taking the XML output of the data mapper, which is formatted in such a way that each XCS record becomes a row and field underneath it as a column and converts that into a standard CSV and drops it into a file. Running this through Testing Mode once more. Again, we’ll start with our sample data, everything else is the same, but we let it run through to the end and we can see that ultimately we’ve generated CSV with the appropriate column headers.

Versatility of PilotFish’s eiConsole

Of course, this is just an example. Real-life examples become much more complex with many more fields. You don’t need to go to a CSV, you can go to a database, to another XML format, to JSON, etc. It’s also worth noting that these transformations from DTCC to an XML format can also work in reverse. So, the eiConsole provides a tremendous amount of utility in being able to both parse and generate any DTCC EDI format without having to do any coding specific to the details of that spreadsheet that defines the format.

Conclusion

Thank you for watching! Click the link for more information on transactions and processes supported by the eiConsole for DTCC.