RBC Financial Group Automates Bank Statement Reconstruct Activities

Organization RBC Financial Group.

The Challenge Automate a formerly manual process of reconstructing customer bank statements.

The Strategy Use WebFOCUS, RBC Financial Group's enterprise standard reporting solution, to tap into an enterprise data warehouse of transactional records to instantly reconstruct consolidated bank statements over any selected time period.

The Challenge A tremendous boost in productivity for operations center personnel, with annual savings of $1 million.

Information Builders Solution WebFOCUS.

You never thought it would happen to you. The government is auditing part of your personal tax return history. They'll need to see your bank statements from 1998. No problem. You have those stored in a shoebox in the closet. Or did you put them in the attic? Uh oh…

There are many reasons why people need to reconstruct historical transactions from their personal bank accounts. If statements are lost or misplaced, they assume a quick phone call to the bank is all that is required to replace them. True enough, but few people stop to consider what a huge volume of data these transaction histories represent, or what is involved in reconstructing a specific statement from this mountain of information.

Consider RBC Financial Group, a leading North American financial services organization based in Toronto. With more than 12 million customers and 1,200-plus branches, RBC Financial Group's customer transaction records dating back six years equal a terabyte of data. Until recently, personnel at RBC Financial Group's operations service centers had to laboriously reconstruct personal transaction statements by combining information from legacy information systems with microfiche records.

That all changed when RBC Financial Group released Bankbook Reconstruct, a WebFOCUS application that completely automates the process of constructing and printing bank statements. RBC Financial Group personnel use the system to instantly reconstruct consolidated bank statements in English or French, containing up to six years of transaction history for any one of their 12 million personal banking clients. The new system is yielding a dramatic boost in productivity for the operations service centers, resulting in significant productivity gains with bottom-line savings.

Ever the Innovators

Chartered in 1869 as the Merchants' Bank of Halifax and renamed Royal Bank of Canada in 1901, RBC enjoys a longstanding reputation as an innovative company. In 1961 it was the first bank in Canada to install a computer and in January 1995 it became the first Canadian bank to offer information through the Web. Currently, an estimated 10,000 RBC Financial Group employees use Information Bulders' WebFOCUS enterprise business intelligence technology in their jobs.

"The genesis of our Bankbook Reconstruct project came from a request by our operations service centers," recalls Andy Hanna, manager of report management and distribution at RBC Financial Group. "For years, customer service representatives responded to requests for reconstructed statements by accessing archival data on our mainframe systems and microfiche records from a third-party company. They had to manually concatenate this information, then translate it into the appropriate language, English or French."

The turnaround time for a reconstructed statement covering a year's transactions could take up to three weeks. These cumbersome tasks were not only an inefficient use of RBC Financial Group resources, but were expensive for customers, who were charged an hourly rate for the service. "Our operations people wanted to know if we could pull the legacy data and microfiche records together into one source for reporting," says Hanna. "I knew this was possible. But after researching the problem, I determined it would be time-consuming and difficult to accomplish."

Hanna's background at RBC Financial Group includes 10 years on the computer services side and another 10 years on the business side. His diverse experience came to fruition in a resourceful solution to the problem. "We had an enterprise data warehouse of transactional data that managers were using to analyze business opportunities and spot trends," Hanna explains. "I realized that since all the data was there, we could use it to reconstruct statements as well."

Creating a New Reporting Portfolio

According to Dale Lindsay, technology liaison for the Record Capture and Information Management for Personal and Business Technologies department, within two weeks Hanna had developed a WebFOCUS prototype to prove that the concept was viable. Then, working part-time, Hanna and another developer, Jayem Nolan, spent about six months developing the complete Bankbook Reconstruct application.

Because WebFOCUS includes the powerful FOCUS language, they were able to retrieve the data, create the reports, and construct the presentation layer all from one product. "Nolan and I split the task," says Hanna. "He developed the code while I created the business rules. We used WebFOCUS as the engine to get the data. We had plenty of experience with Information Builders' technology, and we knew that the WebFOCUS environment was easy to learn and to use."

The tricky part was to restructure and verify the data so it could be used to create personal transaction histories. Since the enterprise data warehouse was modeled for historical trend analysis, there were lots of tables associated with each client. "We had to make sure we were sending accurate data back to the clients, so much of the development time was spent verifying that the data was accurate and ensuring that the reporting routines produced consistent results," Lindsay says.

Prior to the Bankbook Reconstruct project, the enterprise data warehouse was not used as an operational system, but only for trending and ad hoc reporting. "Once we decided to use the enterprise data warehouse as our source of data, we wanted to ensure complete accuracy of the data," Hanna adds. "My group spent months performing an exhaustive data integrity check as part of the Bankbook Reconstruct project. We actually spent more time on that than on the development of the application. We are now confident that the statements given to clients are complete and accurate."

From a technical standpoint, Hanna says, the primary challenge came with joining the tables efficiently to obtain good query performance. "The joining features in WebFOCUS were particularly helpful for combining tables from many sources," he adds. "Once we devised the right joins and table hierarchies, it was relatively easy to do. When we had a question, we called Information Builders' tech support hotline. They were very helpful."

Seeing a Return on Developer Investments

RBC Financial Group's Bankbook Reconstruct application quickly exceeded managers' expectations. "The response time for creating a report that accesses three years of transactions is about 20 seconds," says Hanna. "Previously, that task would have taken weeks and cost the customer quite a bit of money."

The new system requires employees to simply fill out a few fields – such as account number and to/from dates – to easily obtain that same information. This enables the bank to charge customers a flat fee of just a few dollars for the service. "We can take up to six years of data and compile it into one report," Lindsay says. "Instead of manually concatenating information from microfiche, WebFOCUS pulls it all together and gives you a running total. It handles all of the calculations automatically, allowing us to improve our turnaround time for most reports from about 10 days to less than three minutes."

This not only saves time and money for bank customers, but also delivers huge savings for RBC Financial Group. According to John Heal, national manager of operations, automating Bankbook Reconstruct activities has enabled the bank to improve productivity in the service centers. "Also, we no longer need to pay a third party to create and manipulate the microfiche," says Heal. "These cost savings go directly to our bottom-line results."

A number of other departments are also using the application, including the Credit Management and Recovery and Fraud Detection departments. "We plan to expand the WebFOCUS application to include current account statements, Visa statements, and other customer information," Hanna says. "Now that we can easily access and analyze the data, there are many different possibilities for using it."

Extending the Returns to Tellers and Customers

Bankbook Reconstruct is currently being used by up to 150 RBC Financial Group personnel in the operations service centers. The next logical step is to roll out the application to tellers in the branches so customers can obtain reconstructed statements on the spot. "It takes almost no training to get a teller up to speed using the system, since it involves simple data input through a browser screen – stuff most computer users know instinctively," adds Hanna. "It's that easy."

Phase three will involve rolling out the service on the Web so RBC Financial Group's 12 million customers can reconstruct their own bank statements with a few mouse clicks, generating reports to PDF files or Excel spreadsheets. "We are very close to achieving this capability already," adds Hanna. "We are simply ironing out the security issues and figuring out what kind of business we might generate from putting this online."

With the help of Information Builders, RBC Financial Group is perpetuating its reputation as an innovator. At the Information Builders Summit 2002 users conference, the company received the Highest ROI Award for the Bankbook Reconstruct application, reporting an overwhelming 1,218 percent return on its WebFOCUS investment. "The savings we obtain from this new system benefit our customers as well as our institution," Hanna concludes. "It is a win-win situation for everybody."