Role MUFG Securities

Introduction

I was a contract SQL Developer at MUFG Securities in London, from August 2023 to June 2024.

Now I have edited some of the details here because I signed a NDA - Non-disclosure agreement, so I need to refrain from divulging any sensitive data (I've marked any such changes with **)

Rook and Pledged-limit collateral projects

I started the role with the **Rook and Pledged-limit vs collateral projects.

Trading counterparties of the bank had to put up Margin payments to cover their trading positions. Whenever trades went against them, they received margin calls.

However sometimes they disputed these margin calls.

These disputes were stored in the database along with one of three "escalation levels" L1, L2, L3, determined by the size of the position, the nature of the counterparty, and other factors.

The project was to write the code in sql to determine these escalation levels according to provided business rules.

This was done using a SSIS package created by BIML to load the data from supplied flat files, Control-M jobs to load them daily, and Control-M jobs to run the stored procedures to load them from Staging to MigReporting and through to the FOMI SQL Server databases.

There was an Excel file that used an Excel add-in called SQL Spreads to read and write data between the sheets of the spreadsheet, and the data in the FOMI SQL Server database. This allowed end users to read and update the status of the disputes by clicking a button.

In addition, once the user was happy with the final data, they would click another button in the spreadsheet, to run another macro, that ran a VB script to write a "trigger" file into a path in the file system (we didn't have access to this path in Prod). This trigger file would be picked up by a "FileWatcher" Control-M job, which when so triggered would run another stored procedure to copy the changes back into MigReporting.

Configuration of SQL Server synonyms to point to live vs test objects.

The purpose of this was to allow users to manually change escalation levels and other data in response to business requirements.

BIML

The SSIS package was not created manually. Instead it was created in an automated fashion using a Visual Studio 2019 add-in called BIML.

To use BIML, you first of all created the destination table in SQL database "CascadeStaging".

Next you run a script to insert data corresponding to the table's columns (read from the system tables) into metadata tables owned by BIML containing the column definitions of this destination table.

Then with the SSIS project open in Visual Studio 2019 you right-click the project's .GoBIML file and select "Go BIML" from the right-click menu. This instructs the BIML add-in to create the dtsx package file, using also additional configurations in template files.

Then, after running a further step, you can double-click the new .dtsx file in Visual Studio to open it.

Right-click the background and select "Variables" to display the list of variables in the .dtsx file, and set them to the required values (for example sourcefilepath). When run in production, the Control-M scheduler sets the value of these, by providing parameters mapped to them.

Next I wrote a series of stored procedures to load this data through to the Bronze, Silver, and Gold layers according to the "Medallion" data architecture (a new industry standard). Table Jarvis.BronzeAutomationTool was all text columns, a copy of the Staging table, the only difference being that the Bronze layer kept an archive of data for earlier COBIDs.

Next load into SilverBookingAccount, a table containing data grouped by BookingAccountCode (for example "**Alresford" or "**Ascot"), with values like Commitment and CommitmentInUSD that were common to each BookingAccountCode, and other data that will be needed to subsequently calculate StartCash. Conversion into numerical and date datatypes.

Next the load into SilverTrade, which had the same data as BronzeAutomationTool but with the text converted into numerical, dates etc, and also joined with SilverBookingAccount to use some of the aggregated values in the calculation of some of the columns, most notable "StartCash".

Note that the source file contained only local-currency values. The USD values were calculated on the fly by multiplying by a exchange rate in the global.exchangerate table.

Next the "Gold" layer tables: GoldFactTrade and GoldDimBookingAccount were loaded from the silver tables. It was a 1-1 data copy, but at one point within the development I had added code to populate Type 2 slowly changing dimensions keys and surrogate keys. To do this I repurposed code from <website google mssqltips 2883> to use the SQL MERGE statement to assign new surrogate keys when the values changed - this code is around three layers deep, is elegant, but is perhaps a little difficult to understand.

SVN - Apache Subversion

The new versions of the code were committed to the SVN source repository for safe-keeping.

In the case of the SSIS and SSRS packages, it was done as follows:

First you create an empty directory on your local machine.

In Windows Explorer right-click and select "SVN Checkout" (it is necessary to have installed "Tortoise SVN" software locally, to do this.

Provide a URL to the existing "branch" in the SVN repository on the network, like:

https://visualsvn.mfil.local:8443/svn/TBO-BIT/MigReporting/Branches/Development/Neil.Miller

Then right-click again and select "SVN Update". This will cause all of the files at that URL to be copied locally. And shown locally in colour green if they have not been subsequently changed.

You can then edit the files locally (using Visual Studio etc), after which they will turn red.

When you are happy with the new versions, right-click the parent folder again and select "SVN Commit", whereupon it will open a window listing all of the files that it detects have changed (are coloured red), with a checkbox for each one. Tick the checkbox and click OK, and it will "commit" these up to SVN, returning when complete a revision number (like 35424) uniquely defining the upload. They then become available to other users to download and edit as required.

In the case of the stored procedures and tables created in the database, although I have script files to create these, it is not these files that are committed to SVN. Instead you install the Redgate "SQL Source Control" add-on to SQL Management Studio . Then in Management Studio you configure each database in the "Setup" pane of the Redgate window that appears on the right hand side when you select a database, to point to a SVN URL location like:

https://visualsvn.mfil.local:8443/svn/TBO-BIT/MigReporting/Branches/Development/Neil.Miller/Database/FOMI/DBObjects

Then you click the "Commit" tab in the Redgate window, where it will list those SQL objects (like stored procs, views, tables) that have changed, with a checkbox each, where you can click "Submit" to commit these items to SVN. They will be stored in SVN as a create .sql script in a subdirectory of the above ..Neil.Miller/Database/FOMI/DBObjects path (and can indeed be downloaded to the local directory in the same way as above).

Control-M

Control-M is the Scheduling system that the company uses.

It is configured entirely with XML files, some of which are auto-generated by editing Excel spreadsheets and running Powershell scripts to extract the XML from them.

Example XML files were HD_Staging.xml, MG_Cyclic.xml, and FO_Model.xml. The MG stood for MigReporting I think, and FO for FOMI.

The XML files contained the jobs to run, command line paths, stored procedure names, database connection strings, with heavy use of variables and other parameters.

In order to test changes to the files, it is necessary first to commit them to SVN, initially to the Neil.Miller dev branch, for example:

https://visualsvn.mfil.local:8443/svn/TBO-BIT/MigReporting/Branches/Development/Neil.Miller/ControlM/FO_Model/Export

TeamCity

Once committed to SVN, the files would automatically become available at the corresponding path in the TeamCity web-based compiler system. Go into that and select the files and click to compile them (this took several minutes because all the files were compiled every time).

This would generate a "build number" like 232.2344 or something like that.

Next go into the "DA" (formerly SDA - Serena Deployment) deployment web application, to deploy the compiled code of this version number to the Control-M servers that will run it.

Next go into the Control-M web application itself. Click on the "Planning" tab, and navigate down the tree, and find the job(s) that have been updated. Click to "Order" these jobs, with specified parameters or not. This creates a running instance of the job that can be seen in the "Monitoring" tab. Here the progress of the job, including any errors, and including all variable values supplied either in the job or at a parent level, can be seen. Verify that the job completes successfully, and check the database etc to see whether data has been loaded or files moved or copied.

PTM reports

I wrote code to analyse the text of the large 4500-line Jarvis.PublishTrade stored proc, to identify all columns and underlying table and view names in it. Using an iterative method going up the tree. And used this to generate a list of these columns for **Richard to provide data for.

The PTM reports were in SSRS. There were around 11 sections, each of which was run in turn when you run the overarching PTM report. It is run on the SSRS web page, though can also be run in Visual Studio.

The change I made for the Automation Tool was not to the report themselves, but rather the three datasets (PTMTrade.rsd I think, and SSFF.rsd and SSFFByBookingEntity.rsd) that contains the SQL query that is run.

These reports when run, were saved as .PDF files for the business.

Releases

Once Control-M or database code is ready for release to Production, it is necessary to commit it to SVN to a release path, such as:

https://visualsvn.mfil.local:8443/svn/TBO-BIT/MigReporting/Branches/release/2.5.4/ControlM/FO_Model/Export

where "2.5.4" is the release version (increments every release cycle every two weeks or so).

And update a Confluence (wiki) page with details of the released files, with test evidence (screenshots) of the code working saved into a spreadsheet. This is all reviewed by the release team, in a weekly meeting you need to attend, to discuss the release. Once that is complete, a different team in the company deploys the release from the SVN path above, onto Prod - us developers only had read access to Prod.

SSFF

The final project I worked on was the integration of the SSFF data from the Automation Tool (an alternate source of the data), into new tables in the FOMI database (on-premise SQL Server), and using this data instead of existing SSFF data from existing table Jarvis.Trade (while still using non-SSFF data from Jarvis.Trade)

The Automation Tool provided a weekly or daily .csv file with integer column COBID (close-of-business-id) in the form yyyymmdd giving the date of the data. It was around 3000 lines of data.

This file was initially loaded in by a SSIS package.

The data consisted of lists of transactions or trading positions grouped by BookingAccountCode (otherwise known as a "facility" or "transaction type") (which had values like "**Bishop" or "**Knight"). Each position had a "CashOut" amount, meaning the amount of cash drawn for it, and also a "Commitment" value common to the whole BookingAccountCode, being the amount of money available to the counterparty to withdraw.

Steve feedback

My manager **Steve says he has been very happy with my work - it was good quality.

He said what was good was that I got on with it without needing to be prompted - he could "leave me to it" and progress things despite "big problems". And that gave progress so as to give stakeholders confidence that all was going to plan.

He said the only downside was that in the feedback that I gave, I tended to be too detailed.

I replied that this was probably because while I am good with detail, I felt that I was less good at the big picture resulting from that detail. And that I was concerned that I might have misunderstood the big picture and was concerned about causing an error as a result of that misunderstanding. So I would provide them with the detail, and let them construct a big picture from it - and in that way it would not be my fault if there was a problem with my communication.

I asked whether he was happy with my speed of working. He didn't answer, so I suggested that it perhaps isn't too easy to tell - which he agreed with.

Another point he said, was that I tended to focus in initially on a small part of the project, for example Section 8 of the PTM report. He said it is better to initially produce an extract of the whole PTM report (and the **Foxtrot) first. i.e. focus on the big picture first. Send regular "reconciliations" (comparisons of data old vs new) to the business regularly. My reply was that that would require an assumption that the stored procedure of each section works in the way that it appears to do on the surface, and that such an assumption would open me to the risk that a detail unseen in the report would change the macro picture.

Me: I think I should take a risk on this in the future. Because most of the time stored procs do do what they appear to do on the surface.

He said that the handover doc I had written was the best one he had seen.

I said that he took an interest in developing his staff, and that his was a major motivation for me going to the Fabric events and doing the Microsoft exam.

I said that his approach of being more hands-off was consistent with his approach of trying to stretch people professionally though.