ASB Bank, a wholly owned subsidiary of Commonwealth Bank of Australia (CBA), has a long history of innovative products, services, and operational procedures. When its parent organization decided to apply for an advanced level of accreditation for the international Basel II risk management accord, ASB needed to quickly assemble a packet of its own risk management information, processes, and policies to provide to CBA, as well as create a process for submitting periodic updates. To meet these requirements, ASB built a Basel II risk management data mart on Microsoft® SQL Server™ 2005 Enterprise Edition. Using the new features and technologies in SQL Server 2005, the bank was able to meet the requirements in less than nine months, ensure the integrity of business processes and data, reduce operational losses, lower funding costs, enhance its existing risk framework, and reduce hands-on management.
With more than 150 years of experience serving New Zealanders’ banking needs, ASB Bank, a wholly owned subsidiary of Commonwealth Bank of Australia (CBA), is strongly committed to staying “one step ahead” in its products, services, and operational procedures. In 2004, central bank governors and the heads of bank supervisory authorities in the Group of Ten (G10) countries endorsed a new framework—known as the Basel II accord—for banking supervision and to establish the level of capital banks should hold. This framework establishes more risk-sensitive minimum capital requirements to encourage banks to identify potential risks and improve their ability to manage them.
CBA decided to apply for the advanced level of Basel II accreditation, and, as a subsidiary of CBA, ASB needed to comply with the same regulations. The accreditation procedure required that ASB provide an application packet of all of its risk management information, processes, and policies for CBA to submit to the Australian Prudential Regulatory Authority (APRA) by September 2005. ASB also needed to create a process for submitting periodic updates.
To gather the required application informa-tion and provide the monthly feeds to CBA, ASB built a new Basel II risk management data mart that collects data from approxi-mately 60 data feeds—mainframe systems and Microsoft® SQL Server™ 2000 data-bases—each day. ASB decided to build the new data mart on Microsoft SQL Server 2005 Enterprise Edition to take advantage of the following features and technologies:
- SQL Server Integration Services (SSIS). This technology enables ASB database administrators to more easily import packages from multiple sources and manage multiple configurations more easily. “The redeveloped SQL Server Integration Services gives us a lot more functionality out-of-the-box, so it requires less custom development,” says Peter Newey, Manager of SQL Server Solutions, ASB Bank.
- Notification Services. ASB is using Notification Services to automate the load processes into the Basel II data mart. It also plans to use this functionality in the future to automatically notify users of conditions such as load errors or delivery of completed reports.
- SQL Server Analysis Services. Proactive caching in SQL Server 2005 Analysis Services brings data cube information closer to real time and enables ASB to define how often the data cube should be refreshed to reflect changes to the source data. Newey explains: “We’re getting information from disparate sources, so we want to be able to build data marts that produce usable information, and potentially reprocess portions of that information anytime during the day.”
- Report Builder. This feature enables ASB general business users to create a variety of internal reports without IT intervention. “One of the reasons we looked at SQL Server 2005 was because it significantly empowers users with Report Builder to create their own reports,” says Newey.
||SQL Server 2005 Integration Services gives us the transpar-ency that the Basel II accord requires, and enabled us to quickly demonstrate and display clear and transparent processes for managing data and credit information.
Manager of SQL Server Solutions, ASB Bank
When ASB first set up the Basel II data mart, it routed the data feeds through its existing SQL Server 2000 data warehouse into the new SQL Server 2005 Basel II data mart. As of October 2005, the ASB Basel II data mart was 3 gigabytes (GB) and growing by about 2 GB per month. Ultimately, the bank database administrators plan to migrate the SQL Server 2000 data warehouse to SQL Server 2005.
Data is loaded into the SQL Server 2005 data mart each day, where it is consolidated, and verified. Periodically, the system produces extracts—including customer, log, and credit information—that are automatically transmitted to CBA. “Analysis and reporting are mission-critical in the financial services industry, so we need much more than standard data warehousing functions,” says Newey. “With the new features and tools in Microsoft SQL Server 2005, we are able to dynamically analyze and report critical information in a short time frame.”
By building its Basel II risk management data mart on SQL Server 2005 Enterprise Edition, ASB Bank was able to:
- Meet Basel II accord framework in less than nine months. “SQL Server 2005 Integration Services gives us the transparency that the Basel II accord requires, and enabled us to quickly demonstrate and display clear and transparent processes for managing data and credit information,” says Newey. “We felt that we had a lot more opportunity to manage our load and transformation processes and present transparency with SSIS.”
- Ensure the integrity of business processes and data and deliver required information to CBA in a secure, timely manner each month. The ASB Basel II data mart takes advantage of the significant security improvements in SQL Server 2005, including password policy enforcement specifically for the database server; support for native encryption; and the ability to group database objects and attach ownership of those objects to a schema, rather than granting users ownership of individual objects.
- Make more profitable use of capital through reduced operational losses, better risk-based pricing, and lower funding costs from reduction in capital requirements.
- Enhance the existing risk framework to enable improved monitoring and measurement of risk.
- Reduce hands-on management. “The tools in Microsoft SQL Server 2005 reduce management and development time, helping us to deliver critical business information faster,” says Newey.
- Establish a blueprint for future data marts and reporting. ASB is using the Basel II data mart project as a blueprint for all of its future data mart loads and operational reporting. Says Newey, “Microsoft SQL Server 2005 has given us an enterprise-level database system that delivers extremely reliable data and analysis for industrywide compliance as well as for our business moving forward.”
Microsoft Windows Server System
Microsoft Windows Server System™ is a line of integrated and manageable server software designed to reduce the complexity and cost of IT. Windows Server System enables you to spend less time and budget on managing your systems so that you can focus your resources on other priorities for you and your business.
For more information about Windows Server System, go to:
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is comprehensive, integrated data management and analysis software that enables organizations to reliably manage mission-critical information and confidently run today’s increasingly complex business applications. By providing high availability, security enhancements, and embedded reporting and data analysis tools, SQL Server 2005 helps companies gain greater insight from their business information and achieve faster results for a competitive advantage. And, because it’s part of Windows Server System, SQL Server 2005 is designed to integrate seamlessly with your other server infrastructure investments.
For more information about SQL Server 2005, go to:
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:
For more information about ASB Bank products and services, visit the Web site at:
© 2005 Microsoft Corporation. All rights reserved.
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft, the Windows logo, and Windows Server System are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are property of their respective owners.