RBC Financial Group
Leading Canadian Bank to Mitigate Global Risk Exposure Using SQL Server™ 2005
|
|
Company Overview
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.
Business Challenge
RBC’s Market Risk Management function is also dependant on information technology. To keep 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.
While SQL Server 2005 takes advantage of the parallelism and performance of the 64-bit Intel Itanium 2 processor, the Itanium 2 architecture also provides a unique level of system fault-tolerance and reliability which is crucial in key applications such as this
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 bit Intel Itanium 2 processor.
“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.
Business 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 5pm and ends at 8am 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 Intel Itanium 2 processor 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.
The scalability of the Itanium 64-bit architecture permits a more effective use of large in-memory data sets, greatly accelerating the batch processing for the risk system.
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 the context of the portfolio, allows us to bid for business closer to our acceptable risk/return level,” says Hastings
Stable, long-term platform
Taking advantage of the Microsoft-based platform enables RBC to tap into Microsoft’s extensive partner community. It is also currently considering adding a business intelligence layer to sit on top of SQL Server 2005. One candidate currently under consideration is Microsoft Gold Certified Partner Panorama Software Ltd., which provides SQL Server users with comprehensive analysis and reporting capabilities.
Once the 2005 version is fully implemented, RBC and T4G will also begin looking at SQL Server Integration Services.
“This will help us consolidate several data cubes into one, and give users direct access to them. That would be the ultimate enhancement, and one that RBC users are really looking for,” says Barter.
Microsoft Windows Server System
Microsoft® Windows Server System is a comprehensive, integrated, and interoperable server infrastructure that helps reduce the complexity and costs of building, deploying, connecting, and operating agile business solutions. Windows Server System helps customers create new value for their business through the strategic use of their IT assets. With the Windows Server operating system as its foundation, Windows Server System delivers dependable infrastructure for data management and analysis; enterprise integration; customer, partner, and employee portals; business process automation; communications and collaboration; and core IT operations including security, deployment, and systems management.
For more information about Windows Server System, go to: http://www.microsoft.com/windowsserversystem
For More Information
For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234 in the United States or (905) 568-9641 in Canada. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to: www.microsoft.com
For more information about T4G Ltd. products and services, call 1-800-399-5370 or visit the Web site at: www.t4g.com
For more information about RBC Financial Group products and services, call 1-800-769-2599 or visit the Web site at: www.rbc.com
For more information Intel products and services, call 1-800-628-8686 or visit the Web site at: www.intel.com
