I am a firm believer of setting goals. Be clear and realistic on what you want to achieve. A few years ago I was blessed with an opportunity to be part of a project with strong culture of setting goals and collaboration.
A top investment management firm was looking towards automating the distribution of their clients’ tax certificates. A few teams formed part of the project team. We were a team of four consultants, and our focus was on data source design, report design and report distribution methods based on client requirements.
The investment firm is extremely client-focused. For example, there was a person who complained that he did not get his son’s certificate the same day as his own. This person’s certificates were held back to ensure simultaneous distribution, despite the extra time taken by the investment firm.
There were many different rules that affected the outcome of a certificate. For example, a certificate could cover many pages, or had an incomplete address. The reports had to be very robust and dynamic.
We collaborated with the client’s business experts to develop understanding of the existing process. With this knowledge, we could design solutions and do resource planning with confidence. It was important to avoid workarounds and over-complications, because the reports were going to be reviewed each year. Clear communication with developers to find or add solutions to the reporting software kept schedule disruption to a minimum.
The first objective was to build a solution to stage the data for reporting. The data source would be an ORACLE database, where the previous solution used Microsoft SQL Server. Millions of transactions are stored in the live system every month, therefore due to time and performance constraints it was not feasible to generate tax certificates directly from the live system. The staging solution consisted of an automated daily import process, copying large amounts of data from the ORACLE database to a Microsoft SQL Server database. We added data verification checks, such as before and after aggregation checks. If any batch was out by even one decimal, the records would be removed and the batch investigated.
Once the data was staged successfully, TSQL queries were engineered to provide processed data for the reports. These queries were extremely complex. They were excellent opportunities to challenge all aspects of database and query design. Optimization was a key concern, as thousands of tax certificates had to be generated within hours. To achieve acceptable performance, queries were reviewed several times during the course of development. Indexes were added to tables to further reduce query times. We manage to reduce from around 6 seconds/report to less than 1 second/report.
The following testing techniques were applied to ensure a robust solution:
Staging of data:
- Expected aggregates of numerical data were tested against aggregates of imported data.
- Expected number of records was matched against actual number of records imported.
- Random samples were given to fresh eyes from different departments for spot checks.
- Consultants identified extreme cases to test, such as long names, large values, many-paged reports etc.
- Consultants were challenged to find mistakes.
- Generation test runs of all tax certificates on server.
- Distribution test run of all certificates.
- Monitoring of mail server performance.
- Expected number of tax certificates generated was matched against actual number of tax certificates generated
- Expected number of mails to send matched against actual number of mails sent
The project was deemed very successful. Elements that lead to the success of the project:
- A few minutes were spent each morning setting and assigning goals for the day (daily stand up).
- Individual accountability.
- Effective collaboration between individuals and departments.
- Effective cross team collaboration.
- Defined project owner and project sponsor.
- Success of the project and client satisfaction was the most important to each individual.