This piece was originally written some years ago, and never published. It is a report and analysis of the largest project I had worked on as a developer / DBA up to that point in my career. I have published it here for any of you who are interested.
Between July 2008 and February 2009, a team of four achieved the impossible.
Large Bank wanted to implement a Rewards and Loyalty Programme. The project was estimated to take two years to complete, just for the middleware and back-end. The front-end would be developed side-by-side, by a specialist call-centre company to interface with the new system. A separate direct marketing software system was developed by a third-party to interface with our system.
Requirements included:
- a full end-to-end solution including full integration with the existing Enterprise Service Bus (ESB) and all associated systems (credit card, current accounts, customer information view, debit order system);
- a batch processing engine to import from and export to the COBOL-based flat files for debit orders;
- multi-language support at the database level;
- a natural language interpreter for reading bank statements in order to identify vendor transactions where exact matching wasn’t possible;
- interfacing with a new direct marketing engine developed by a customer management solutions company.
Shortly into the project, timelines were dramatically shortened. The delivery date was brought to early 2009. As a result, the call-centre company was unable to complete their deliverable in that timeline, so our company took on the front-end development as well.
Technologies Used
The company elected to use Microsoft technology, due to its lower total cost of ownership (TCO) and inherent knowledge in the team. SQL Server, C#, Workflow Foundation, WCF and IIS. I personally developed the database and batch processing engine, and was responsible for the data types across the system. I assisted with other aspects of the middle tier, including debit order interface and vendor-specific analysis. We ran into dramatic performance issues with the natural language processing, so my colleagues assisted me with that.
The team lead elected to use Microsoft Workflow Foundation for much of the middle-tier business rules. In retrospect this was a fundamental error. Its goal was to reduce the complexity of maintaining the system in the future, since it uses an abstract conceptual method of business process flow, drag-and-drop, and so on. However, it was arduous to work in. We lost many hours of productivity thanks to Workflow Foundation.
In some cases, portions of the Workflow Foundation code were replaced with simple C# DLLs, which could achieve the same results in a much quicker development time, and be maintainable with proper commenting and modular design.
The debit-order interface was written with a combination of database queries and bit-shifting C# code to generate the EBCDIC and compressed integer fields for the debit order flat file. We very proudly avoided writing any COBOL code, keeping all the code in the .NET stack.
Database Design
The database design was, to that point, the greatest challenge in my career. The Large Bank staff came up with an original design that was based on their intimate familiarity with mainframe data management. I was tasked with rethinking how the data was to be stored and accessed, while educating the Large Bank project managers about my decisions.
We used Microsoft SQL Server 2005. While 2008 was due for release around the time of the project’s launch, and we did have access to the beta version, I was not prepared to base a solution on untested technology. This decision bore out correctly, and the database as of the time of this writing, is still performing admirably, handling millions of transactions per month. Using SQL Server 2008 from the start would have permitted the use of the new DATE and TIME fields, which may have improved certain processes.
One of the design decisions with the database was a novel implementation of multi-language support. Owing to South Africa’s 11 official languages, the system had to support Afrikaans and English from launch, with capacity to upgrade to additional languages easily in later phases.
Traditionally, all text values in a system are stored in resource files, and depending on the language key, retrieved accordingly. My database design had to be different, because the language key was stored per customer, not per end-user.
Since the database contained this language key at the customer level, all text fields in the T-SQL queries themselves would already have to contain the appropriate language for display on the front-end. This was one of my most satisfying achievements.
When a customer record was queried, the text fields from the back-end, middle-tier and front-end would all match automatically. The result was that customer support scripts would be presented in the customer’s home language as soon as the customer was identified, to improve their customer support experience.
Algorithm Challenges
The primary goal of a rewards and loyalty programme, such that it exists for the customer, is accrual of reward points so that they can be redeemed by the customer at certain vendors.
One of my more challenging algorithms to solve was to redeem accruals in the order in which they were earned. Accruals had an expiration date, and redeeming these would have to use the earliest ones first.
The challenge came in with customers that spent fewer accrual points than they had in a rewards transaction, where we had to keep track of their spend, audit the numbers appropriately, and keep a running total without hammering the database.
My original database design assumed that each Large Bank customer would eventually have a Rewards and Loyalty number. With approximately ten million customers, each potentially earning accruals on every transaction, we had an estimate of thirty transactions per month. It would have been ludicrous to sum the millions of rows to get the latest accrual balance every time the customer wanted to redeem his or her rewards, hence the running total.
The C# code I wrote for this was simple in theory, but complex in implementation. Each accrual had a Starting Value (the original value when earned), a Current Value (the value after a redemption was applied), and an Expiration Date. Additionally, each customer had an overall Accrual Balance record, which contained the running total.
The running total would be queried first, to check whether the customer had enough rewards available.
Then the accruals would be queried in date order (taking the expiration date into account), with the earliest ones first. The code iterated through the available accruals and set each one to zero as it used them, or if those had expired.
Each change to the Accrual record changed the running total, and increased another counter for Redemptions, until the Redemption counter matched the requested rewards count.
In the case of a customer having, for instance, a single accrual record of 200 reward points and then redeemed 100 points, the Accrual record’s Starting Value would remain 200, the Current Value would be set to 100, the Running Total would be set to 100, the Redemption counter would go to 100, and the transaction would be approved and the vendor notified.
Auditing
Every single change to the database that was not an INSERT required an audit record to be stored in the Audit Database.
The Audit Database was a simple design, comprising key rows to identify the source table in the main database, as well as an XML field containing the original row that was modified (whether by DELETE or UPDATE).
The biggest design decision here was whether to use triggers on the main database, or exclusive DELETE and UPDATE stored procedures which wrote to the Audit database for every change.
For security reasons, all access to the database was restricted to stored procedures anyway, so this decision was easy. No triggers were used, and every DELETE and UPDATE required one of the included stored procedures. Each of these wrote the original row values to the Audit database, before writing the change to the main database, and was wrapped in a T-SQL transaction.
Access to each database was locked down to specific users, and in the production environment, none of us was able to make any changes to the database without Large Bank DBAs checking the code and running it on our behalf.
Lessons Learnt
With the right people, working under extreme conditions, almost anything is possible. However, this is detrimental to their physical and mental health, and should not be encouraged over a long period.
Two of the team were replaced during the project, due to the pressure of the job. Two others left the company shortly after the project ended due to stress. I was one of them.
Large Bank, for the first time in their long history, had a single view of their customer data in this programme. The silo-effect of separate systems and internal politics for each division in the bank was broken down by this project. Having a better view of how the customer was using the different products helped them better identify and evaluate risk, and develop associated rewards accordingly.
I have become much more aware of how different data sources can work together to form a single view of a customer, and this has helped me well in my career.