Leading Canadian Bank to Mitigate Global Risk Exposure Using SQL Server™ 2005

RBC Financial Group is Canada’s largest bank as measured by market capitalization and assets, and one of North America’s leading diversified financial services companies. It provides personal and commercial banking, wealth management services, insurance, corporate and investments banking, and transaction processing services on a global basis. To succeed in a volatile financial market RBC must balance acceptable trading risks against those deemed unreasonable. The Group Risk Management (GRM) division at RBC is tasked with maintaining that balance. Each day it gathers worldwide trading transaction activity into an enterprise data warehouse for risk analysis. Reports are then sent to key executives who ensure RBC stays within risk parameters. Data volumes have been steadily increasing, and reports were taking too long to generate. This was affecting the bank’s ability to monitor market risks in a timely manner. As well, analysts were unable to drill down into the data and find patterns. RBC enlisted the expertise of Microsoft® Gold Certified Partner T4G Ltd. to upgrade its Microsoft® SQL Server™ 2000 database to SQL Server 2005, taking advantage of its enhanced Analysis Services capabilities. It is also customizing it for a 64-bit environment using the Visual Studio® 2005 development system. Subsequent tests show the Microsoft-based platform will help RBC load data faster and, as a result, cut report query times in half. The platform will also help generate more detailed and flexible reports, leading to better and more timely decision-making.

Situation

The financial industry is a leader at driving value from technology, and RBC Financial Group is no different. It was the first Canadian bank to install a computer when it powered up an IBM 1401 in 1961. Seven years later, RBC again revolutionized the Canadian banking industry by making the first banking transaction – a deposit – using a computer. Today the bank’s 14 million personal, business and public sector clients throughout North America and some 30 countries around the world make heavy use of everything from ABMs to online and mobile banking services.

RBC’s Market Risk Management function is also dependant on information technology. To fulfill its mandate of independent risk oversight, the Risk Management function must ensure on a daily basis that the risk positions the trading rooms have taken are in keeping with the overall business unit trading limits.

To fulfill this mandate the bank gathers daily trading and transaction information from around the world into its systems at its Toronto headquarters. This data is then put into a variety of different reports, one of which is produced weekly and submitted to the highest executive level.

“Risk reporting is critical for us. We regularly create models around worst-case scenarios for particular trading book positions. For example, looking at what will happen if inflation goes up or if the credit environment deteriorates rapidly. This enables senior management to have a clear and independent view of the market risk run within the trading operation,” says Richard Hastings, senior project manager, RBC.

Given that the bank’s trading rooms are handling large volumes of very high value transactions, understanding the level of risk at all times is critical to RBC’s senior management.

To help give risk analysts and business users access to the data it collects, RBC employs Microsoft® SQL Server™ 2000 and Analysis Services 2000 as a business-facing layer. The software helps transform raw transactional data into reports that business managers can easily understand and use to quickly determine what risk RBC might face in the wake of trading activity. The final reports are then posted on an intranet site based on Microsoft® Office SharePoint® Portal Server 2003.

Timely access to these reports is crucial. But turnaround time was becoming a cause for concern at RBC.

“The demands being placed on the system were growing but the ability for the system to scale was limited. It’s just like that famous phrase – ‘time is money’. Speed is very important to users of the risk reports. Given the daily cycle and pace of a trading environment, the quicker it gets out, the quicker the need for people to dig into it. This is important for our business,” says Mandeep Singh, senior technical systems analyst, RBC.

RBC also wanted a tool that would help it give users a view into data that best suits their needs. The system lacked the means to make data available to them – data views the bank needed most. This limited the system’s effectiveness.

“We wanted to turn the application into a reporting and decision-making system,” says Bo Wu, operational manager, GRM, RBC. “But to do that we needed a new reporting system that would help us do more data mining and have the flexibility to quickly adapt to our needs.”

Solution

Impressed by the capability of SQL Server™ 2000, RBC chose to migrate to SQL Server 2005 to improve the performance of its risk reporting system. To help it ensure a smooth migration and fast results, RBC enlisted the aid of T4G Ltd., a Toronto-based Microsoft Gold Certified Partner and a specialist in data management solutions.

“RBC really wanted to enhance the front end on its existing SQL Server system. Our goal is to provide them with an intuitive and cutting-edge, front-end tool that will provide users with quick access to reports and extensive drill-down capability. SQL Server 2005 will enable us to realize this goal,” says Paul Barter, vice-president, strategy and marketing, T4G.

RBC was particularly interested in Analysis Services 2005, which provides organizations with integrated, Web-enabled OLAP analysis. Using Analysis Services, companies can discover data patterns and trends and make better business decisions as a result.

“Analysis Services 2005 comes with a much smarter and much more enhanced set of algorithms. This will help us create better aggregates or data views, which is something we really wanted to leverage,” says Singh.

Analysis Services 2005 also ships with the Unified Dimensional Modeling (UDM) feature. UDM defines business entities, business logic, calculations and other metrics. It acts as a central repository that stores the most up-to-date versions of reports, spreadsheets and other analytical applications. It can be mapped to a host of heterogeneous back-end data sources, providing users with a complete and integrated picture of their business regardless of where their data resides.

“Using UDM, it’s a lot more logical for us now to interpret business results into data cube rules. This will help us eliminate a lot of the difficulty we had interpreting our business rules, and help get the data we need, faster. We’re really looking forward to taking advantage of it,” says Singh.

RBC is also considering implementing SQL Server 2005 Reporting Services. Reporting Services is an enterprise managed reporting environment, one that is embedded and managed via Web services. Reports can be personalized and delivered in a variety of formats, including spreadsheets. New with SQL Server 2005 is a reporting tool, Report Builder, which helps build easy-to-read reports faster.

To help ensure timely and effective customization, RBC developers are taking advantage of the Visual Studio® 2005 development system. Visual Studio is helping them compile the C++ code necessary to host SQL Server 2005 on a 64-bit Itanium server.

“Our developers liked the fact that the whole Visual Studio development area has been upgraded. It’s much more comfortable to develop in. Whether you want to look at optics or properties, everything is at your fingertips now. And when you’re more comfortable, you get projects done faster,” says Singh.

The ability to tightly integrate SQL Server 2005 and Visual Studio 2005 also proved attractive to RBC. Whereas developers had to work in separate areas when working with features such as Analysis Services 2005, they can now stay within a single IDE. Developers no longer need to be specialists in several different tools.

Benefits

The migration to the new Microsoft-based enterprise platform is well underway, and is expected to be complete by early November 2005. By taking advantage of SQL Server 2005 Analysis Services, RBC in its prototyping is already witnessing faster report turnaround times. Analysis Services 2005 is also helping make reports smarter – by automatically suggesting optimal aggregates. Overall, the upgraded, integrated Microsoft-based platform is addressing the business-critical risk management needs of RBC.

Faster access to data

Each day RBC launches a batch process task that typically begins at 5 p.m. and ends at 8 a.m. the following morning. This process captures trading data – a complex array of trading positions and market data from around the world, and packages it via SQL Server 2005 and Analysis Services in a way that business users can clearly understand.

An estimated 2GB of data is pushed through Analysis Services each day and, on average, 5.5GB of data is processed through SQL Server 2005 system on a daily basis. Despite these volumes, the new toolset is already helping RBC reduce processing time.

“Through a process of hardware and software upgrades, we’ve knocked 10 hours off our batch process time in the last six months. We are looking to the implementation of SQL Server 2005 to help us further reduce that batch time. By reducing the batch time, we are able to meet the need of our business in sites outside of Toronto, such as New York, London and Sydney. This enables our staff, in all locations, to more thoroughly investigate the numbers that we get in a timely manner,” says Hastings.

Risk reports, depending on their size, typically take an average of 40 minutes to query. SQL Server 2005 tests have shown that this time can be cut in half. Running SQL Server 2005 on a 64-bit Itanium server has also helped to improve the software’s performance. To ensure proper customization while making the switch from 32-bit to 64-bit, RBC developers used Visual Studio 2005 to help them compile code in C++ and integrate SQL Server.

“Using SQL Server 2005 and Visual Studio 2005 together means everything we need as a development and database team is available to us right across the board. It has made everything intuitive and comfortable for us,” says Singh.

More effective reporting

Managing risk is a critical task for RBC. Receiving accurate, up-to-date risk reports is considered a top priority. “The users want this data right away – almost as soon as they launch a request,” says Singh.

By taking advantage or SQL Server 2005 Analysis Services, RBC will give users the flexibility they require. Business users will be able to access the cubes – the data models that enable users to view data in multiple dimensions – and help them better understand their risk levels. They will also have access to a larger and more detailed set of aggregates to run their data against.

“The data generated by SQL Server 2005 will be more efficient to our business users now. They’ll also be able to do more digging and get the results they need,” says Wu.

Enhanced competitiveness

RBC has access to terabytes of current and historical trading data. It needs to continuously transform that into meaningful and timely information. Together SQL Server 2005 and Visual Studio 2005 are helping RBC generate more detailed reports, faster.

“This is a very important initiative for us. Improved risk management gives us an edge over our competition, and knowing more precisely the level of risk of the next trade in."

For more information about RBC Financial Group products and services, call 1-800-769-2599 or visit the website at: www.rbc.com

Back to Case Studies