One of the frequent enterprise software uses I’ve heard of for RAD Studio with Delphi is in the banking or financial industries. It seems like these markets focus on Windows app development and the more you look the more you find Delphi is whizzing away behind the scenes. Delphi’s powerful data handling and rock-solid reliability and robustness means it enjoys a very positive reputation with the people who understand esoteric things like derivatives, futures, inter-bank transfers, and why governments can’t just print more currency when they’re a little short on funds. There are a lot of rules and regulations to banking and non-compliance can attract eye-watering fines and, if you’re really unlucky, a long unplanned holiday in federal jail. It’s a serious business.
The enterprise article writer today has asked to remain anonymous to preserve their ties with their famously fickle investment banking peers. We have the author’s contact details and will make sure if they win the challenge they’ll get notified accordingly.
Table of Contents
What is the background to your enterprise software?
Investment banks are subject to constantly changing regulations. These regulations are complex and require careful legal interpretation. But business operations need to prepare to support these regulations even when they haven’t yet received clarity from legal departments.
When a regulatory body issues a new regulation (say Dodd Frank – The Dodd-Frank Wall Street Reform and Consumer Protection Act or EMIR – The European market infrastructure regulation), the bank requires to perform an outreach to all of their existing clients and “re-paper” them (i.e. the bank needs to inform them of the new regulatory requirements, and the customer needs to sign some new agreements). The bank needs to ensure that these agreements are signed by specific dates, else the bank cannot continue to trade with them (which means losses of millions in revenue).
What were the requirements of the enterprise software?
Our list of requirements were:
- Identify the list of clients to out-reach.
- Prioritize the list of clients to out-reach based on certain criteria:
- Number of years they have been a customer,
- Current value of business in the last 12 months,
- Based on what products they trade with us (sometimes regulation is only applicable when trading certain products)
- Have completed an active trade in the last 6 months – (but note for some products, they might do a high-value trade once in 2 years)
- Enable a tracking system – Each client must go through a lifecycle – Shortlist, prioritized, out-reach-done, in-review, signed.
- Ensure our client out-reach/ touchpoint is kept to a minimum. We don’t want to out-reach to the same customer multiple times – i.e. once per product / once per branch etc.
- The client has an option to either sign an agreement with us, or register themselves with a government body – if so, we need to get their registration status from a third-party website.
What were the challenges of your enterprise software?
We identified the following potential problems:
- Identifying clients is a problem – as the same client can be registered in multiple client masters across the organization, and across multiple geographies.
- There are duplicate client records.
- Some clients have complex legal hierarchies – so we would rather work at the “parent” level rather than at the “child” level.
- Some clients (called Agents) act “on-behalf” of other clients – so the actual “legal entity” that needs to complete the formalities might not be the agent.
- But some Agents have the authority to sign on behalf of the client they are acting for – so this needs to be tracked correctly.
- Data quality of what products the customers trade is bad, as there are different platform for different products, and product names across applications are not consistent.
- Getting trading information from trading systems is complicated due high volumes (but what we really needed is just aggregate information of total trades done in a period of time)
- We need to retrieve registration information from a 3rd party-website and update our tracker – to ensure we don’t out-reach to them if they have already completed the formalities.
You researched the existing process, how did you do that?
We had to hire 20-30 temporary data analysts to request extracts from existing enterprise business applications. They would consolidate these various data sources into a single location – usually into a bunch of excel files with very complex macros and then load it into a MS Access database. They would then manually work through the client lists, fix data quality issues and track the progress of the out-reach using shared excel files which was a nightmare to keep in sych and there were frequent data losses due to corrupt files. Excel was the only option as implementing the regulatory changes in existing enterprise applications would take 3-6 months, and that task could not even start till the regulations had stabilized.
What were the criteria of the desired enterprise software solution?
We laid out the following points.
- Ability to source data from multiple sources – various client masters, from product master, from trading systems, from 3rd party external websites.
- Get all the data and land it in a database.
- Fix data quality issues.
- Check if they have already completed the agreements on a 3rd-party website, if so – out-reach is not required (but still needs to be tracked).
- Send automated-prioritized list to analysts, who might re-prioritize manually.
- Track the clients’ out-reach status.
- Dashboard required for senior stakeholders to provide updates and help remove blockers.
What were your design considerations?
The solution should be “agile” in the sense, regulations are usually not well defined, and subject to interpretation – so fields to be captured / data quality rules / prioritization rules can change very often – we need the ability to turn-around changes in less than 1-2 days. Security – as this involves bring together client data and revenue data – access restrictions is very important.
Why did you decide to use RAD Studio with Delphi to create your enterprise software?
We decided to use Delphi to build an application, which had the following functionality.
- Excel-like user interface – as that would require minimal user-training.
- Dataset / Worksheet paradigm – all data is captured as datasets, rather than forms.
- Faster time to market – adds support for new regulations fast.
- Central database – enterprise level database (instead of Excel or MS Access).
- Audit / versioning / Workflow – know who modified what and when.
- Easy to deploy – the user just needs one executable file.
- Large data volumes – ability to handle large data volumes without decreasing on-screen performance.
- Assign tasks between users.
- Security – row / dataset level.
- Reporting – integration with visualization tools.
- ETL – for data ingestion and business logic.
How did you implement the ‘Excel like’ user interface requirement?
As I was limited by what we were licensed to use, therefore I used a standard grid component to provide the excel-style interface, but I would really have preferred to use TNextGrid (Bergsoft.net), as it would have given a lot more functionality.
The user would work on one row at a time. Each row usually represented one customer. Data was entered in individual cells. Depending on the data type – user could enter numbers, strings or dates. In some cases, we could provide dropdowns so user can select from a given set of options, and even validate values against lookup tables.
What do you mean by dataset/worksheet paradigm?
It was a conscious decision to capture / work with data as datasets rather than as forms. Creating a form-style interface would have slowed us down. So for all practical purposes, a dataset corresponded to a table in the database, and if we needed a new data capture field, we just needed to:
- Add a new column to the underlying table,
- And configure some metadata that defined the data entry rules.
You mentioned faster time to market, how did that happen?
Adding supporting for new regulations is now easy, all it requires is to create a new table in the database, configure the rules of how data is captured in each field, assign users to the new table, and finally do an initial population of the table – either by loading initial data from an excel file or using an ETL job.
Can you tell us more about the central database?
The current solution was based on each data analyst working on their own local excel file, which would then be sent to a single person who would load it into a MS Access database. This was fraught with numerous problems as you can imagine.
The Delphi application was designed to use Oracle as its central data repository. For every table created, we added some additional system columns – which was used for capturing audit history, and row locking.
What were the special considerations for workflow, auditing, and data integrity?
Concurrency was managed using row locking – the moment a user started editing a row, it would be locked by the user, and the workflow status for that row would be “In progress”. Once the user completed all edits to the row – they would send it for “Review”. The reviewer could either “Approve” the row (which would unlock it), or “Reject” the row (which would revert the data to its previous state).
Additionally – we had a separate log table – where every edit done by any user on each cell was logged. This information was used for audit and also to revert a row back to its previous state.
I understand you found the Delphi app was easy to deploy compared to other solutions?
All the users need to use the application was the executable file, and the Oracle Client libraries. The OCI libraries could be requested by the user from our inhouse app-store. And the executable file could be downloaded from the team’s SharePoint. Therefore, sending out new updates to the executable was super-easy. We used FireDac for database connectivity with Oracle.
What sort of data volumes were you dealing with?
Excel supports a million rows, but working on large sheets was not performant, and a frequent cause of data corruption. Now that the data was in a Oracle database, data volumes did not matter. From a user-interface perspective, we needed to show the user only a few hundred rows on screen, as normally they just worked on one row at a time.
When a customer record was loaded into a dataset, all of them would be in “Unassigned” state. When a user starts editing it, it gets auto assigned to themselves. If the record needs work from another user, they could assign it to another team member.
Was security and reliability an important factor in your Delphi enterprise app experience?
Security was critical – this was initially implemented at a dataset level, as one team usually worked on one regulation, and all of them would have the same level of access. But we captured additional attributes for the dataset, which would allow row-level security at the time of reporting.
What enterprise software reporting tools did you use?
For basic reporting, we used the TChart components to show total rows in progress, completed etc. We were able to produce live dashboards – allowing us to track the status of work, and the status of client-out reach. With use of database views and visualization tools like Tableau, senior management were impressed with the level of transparency we could provide (compared to the previously opaque excel based reporting). As users and reports were being generated from the same table – reports were real-time.
Finally, along with user-edits, we had ETL tools like Informatica that sourced data from external website, or other applications and populated some of the columns in the dataset. These other columns provided the necessary context for the data analysts to review the customer data and make decisions. The ETL tool would also run regular jobs that used the data in the database, ran some business logic, created reports, and even sent data downstream.
Was the project a success?
This end-user application was widely successful. Our end-users were happy, as they no longer needed to struggle with Excel, and were able to focus mainly on the actual business requirement.
Our management were happy, as they got speed, security, and transparency – and they were able to rely on the data when reporting to external regulators. We eventually used this application to support client out-reach for five regulations. As each regulation stabilized, the required functionality was migrated to our enterprise applications for business-as-usual operations.
For a time, we even packaged the Delphi application and had it made available through the internal app store.
This article was submitted as part of our Enterprise Article Showcase. If you have a success story to tell about a project which makes use of RAD Studio with Delphi or C++ Builder, or any of our other great enterprise products please get in touch, you could be featured too and win a prize! Read all about it here: Enterprise Article Showcase