Bank of Montreal (BMO) - IntelliMatch

Case Overview

The Enterprise Reconciliation (CAD and USD Cash Reconciliation) Services and The retail risk department of Bank of Montreal (BMO) team reconciled branches suspense accounts daily. Based on certain business rules they then need to notify the Branch Manager of outstanding items. Prior to the Notification application this process was done manually (i.e. populating the word document and fax to the Branch Manager). The process is now automated using the information that reside on IntelliMATCH database. The team is tasked with developing more than 50 types of reports for branch managers, bank executives and credit centres across the country.

The process, which required the team to consolidate data from several disparate sources, was time consuming and often required the team to manually input data into Excel spreadsheets for analysis. By the time the reports were completed, the data was dated and affected the team’s ability to consistently make timely business decisions.

Result: The final solution handles 30 gigabytes of data each month and has allowed the Enterprise Reconciliation Services and the retail risk management team to speed the report generation process and improve the bank’s ability to gain insight from risk data.

Customer Overview

Bank of Montreal (BMO) - Capital Markets is a leading, full-service North American-based financial services provider offering equity and debt underwriting, corporate lending and project financing, merger and acquisitions advisory services, securitization, treasury management, market risk management, debt and equity research and institutional sales and trading.

Business Challenge

Bank of Montreal (BMO) found its managers and analysts were spending too much time developing data reports, and assigned a special team to find a way to streamline the report generation process.

A team is tasked with developing data-based IntelliMatch notification reports that help BMO executives, the Enterprise Reconciliation Services and credit centres identify issues before they become problems. This team provides data that can help BMO remain competitive against other major retail banks in the Canadian market.

The team is tasked with finding a way to easily develop key risk reports, including two in-depth monthly reports for executives. The first report helps the bank measure credit approval ratios. The second report provides data on credit delinquents. The challenge with these and other reports is ensuring that they are developed quickly enough to reflect the current status of the business.

The challenge is that it’s a very laborious process to distribute reports by faxes from RightFAX Server and pull the data together from iMatch database necessary to complete the reports. The data they need is stored in several sources across the company. For large reports it typically takes them about five business days to complete the process, which means information can become dated by the time the executives receive the data. For new data dimensions, these can be pushed out to 14 days.

Solution

The internal batch program running on the IntelliMatch Notification App Server will pick up the data from the IntelliMatch and Branch Profile databases and process it. In coming data are retrieved from the IntelliMatch database, generate and send reports to Branch Manager Location and Executives.



Conet-Tech chose Microsoft SQL Server 2005 for its 64-bit support, OLAP functionality and ability to handle large environments with low-maintenance requirements. Running on an HP ProLiant DL585 Opteron platform with 32 Gigabytes of RAM, the database would support the retail risk management team’s mission critical report generation process.

Among a host of benefits, SQL Server 2005 delivers improved data analysis and integration capabilities through features like SQL Server Integration Services (SSIS) and Online Analytical Processing (OLAP.) SSIS is designed to make it easier for organizations to automate the transition of data into the database from disparate sources. OLAP is a well-known analytics technology that speeds the report building and generation process, but is typically only available as an add-on database feature. We were also pleased that OLAP functionality was built into the database at no additional cost.



 

Results

The main goal of this project was the redesign of Branch Profile database, batch process and to speed the report distribution by Fax via RightFAX server and the report generation process. In early testing, SQL Server Integration Services has proven that it will significantly reduce the amount of manual intervention needed by the risk management team. Instead of manually collecting data from disparate sources and consolidating it in Excel spreadsheets, the team can now count on SQL Server to collect the data in a fraction of the time.

The new solution will eliminate manual intervention due to existing bugs, efficiently track and log process failures and will re-automate the scheduled notification processes to run 5 times per day at 10h, 12h, 14h, 17h and 20, automatically generate faxes to Branch Manager and email notification to Personal Branch Area Manager and Vice President when needed (the email notification is done only once a day at 17h).

The technologies

- Visual Studio .NET and Microsoft .NET Framework, VB.Net
- Windows Server and SQL Server Enterprise Manager, Report Builder, SSRS, ETL, SSIS
- RightFax Utility Software 8.7+, Reports Distribution by Fax , Crystal Reports 2008, VSTS, WCF, UML, Visio, SMTP Server