DATA WAREHOUSING IN FINANCIAL SERVICES Iyas Al Qasem, Conchango Iyas joined Conchango in 1995 to jointly establish and head up the Business Intelligence and Customer Relationship Management practice. Having developed, project managed and rolled out BI and CRM implementations in some of the UK's largest businesses, he is a regular speaker on issues in this arena, and focuses on deriving and maximising business benefits from these notoriously complex and risky projects. WHY SHOULD YOU CARE? If you are not looking at data warehousing and business intelligence at the moment, here are three reasons why you (and this means you, not just your IT department) should be: Regulatory requirements. The advancing plethora of regulatory requirements and recommendations, from Basel II to the Cruickshank report, Sarbanes Oxley to the International Accounting Standards, all depend either explicitly or implicitly on the provision of accurate, reliable and timely information. Customer promiscuity. The ease of replication of financial products, as well as the entry of non-financial companies into their provision (witness credit cards and savings accounts from the supermarkets) is giving the consumer a wide choice of financial service provider. The ability to monitor customer trends on aggregate, as well as identifying individual customers about to defect, is becoming more fundamental for continued profitability and growth. Operational efficiency. Multi-channel interaction with your corporate and personal customers has become the norm. What is not yet the norm is the provision of quality and cost-effective service consistently across these channels. Effective improvement, be it in underwriter, broker, branch or web profitability, is predicated on the availability of accurate information on each. Similarly, the increasing sophistication in fraud within the financial services sector demands an equally sophisticated capability to identify, prevent and combat it. Despite a very volatile success rate for data warehouses, they are gaining rapid adoption across a variety of companies, and most rapidly within the financial services sector. This is explained primarily by the ability of a data warehouse to provide a robust and automated platform for the support of all the above, and is being accelerated by the identification of specific business initiatives that it can support. In fact, attempting to support the needs of the above three categories without a data warehouse strategy in place is likely to severely undermine (or in some instances, fully preclude) their successful implementation. HOW DOES A DATA WAREHOUSE HELP? The primary objective of a data warehouse is to support an organisation's strategic and tactical business initiative through provision of the accurate, reliable and timely information required in order to take the most appropriate action. The kinds of areas that it can support are shown in Figure 1 - Typical Data Warehouse Analysis Areas. Definitions of data warehouses abound, almost all of which take a technical bias and ignore the reason behind the existence of a data warehouse. My definition is that "A data warehouse is a database that contains data which may include a company's products, operations, customers and market. The data is stored in a fashion that enables easy access to information to support an organisation's strategic and tactical information needs." In order to achieve this, the data warehouse exhibits a combination of a number of differentiators from other systems that organisations may have in-house. These include the following: Multiple sources. The information that you will need to make strategic or tactical decisions rarely resides in one system. The data warehouse takes its data from all the systems that are required to support these decisions. Data cleansing. As the data warehouse seeks to represent the single version of the truth, a key component is the ability to cleanse the data that comes from these multiple systems. This data-dirtiness may have arisen, for example, due to people keying in data differently on screens, a lack of attention to data that is not operationally important, or through intentional mistyping of information to avoid identification (the 'Mickey Mouse' customer syndrome). Financial Analysis and Compliance Marketing Sales and Trading Revenue Reporting Cost Analysis P&L Reporting Margin Analysis Taxes Budget Variance Analysis Loan Analysis Trade Floor Reporting Portfolio Analysis FAS 133 Analysis Compliance Alerting Marketing Segmentation Web Traffic Analysis Customer Analysis Market Basket Analysis Campaign Analysis Customer Loyalty Analysis Cross Sell Analysis Up Sell Analysis Product Profitability Analysis Product Management Channel Efficiency FAS 133 Analysis Research Distribution Client Loyalty Analysis Portfolio Analysis Position Reporting Hedge Slippage Alerts Transaction Volume Analysis Market Data Analysis Financial / P&L Reporting Trade Floor Reporting Refinance Risk Analysis Operations Advisory Services Fraud Profiling and Risk Analysis Call Center Management Channel Management Customer Statements Trade Confirmations Trade Settlement Alerts Wire Transfer Alerts Swap Mark to Markets Financial Reporting Trade Floor Reporting Commission Reporting Branch Office Scorecards Portfolio Analysis Asset Management Customer Statements Customer Alerts What-if Analysis Research Distribution Portfolio Risk Analysis Product Introduction Customer Profitability Analysis Tax Notification Confirmation Alerts Credit Risk Management Hedge Slippage Alerts Fraud Risk Management Exposure Reporting Exceptions Risk Management Value At Risk Analysis Customer Risk Scoring Underwriting Scorecards Index Performance Alerts Prepayment Risk Claims Analysis Figure 1 Typical Data Warehouse Analysis Areas Data consolidation. Even once all the data is clean, more work is needed in order to get to the required information. Consolidation is concerned primarily with mapping the data from the different systems so that a consistent view is achieved. For example, a customer may have a different customer number associated with him for each of his savings account, his current account, his response to a mailer and the complaints he registered. The consolidation phase will aim to ensure that this customer is identified consistently across all his interactions with the organisation. This will also apply to employees, products, services, branches, brokers, underwriters and almost any entity with which the organisation interacts in more than one way. Data manipulation. Data in transactional systems is designed to answer very granular questions. For instance, a typical ATM query is "Are there sufficient funds in account X to allow this £50 withdrawal?" The questions that an organisation needs answered at a more strategic level are more akin to "How many of my 30% most profitable customers are exhibiting behaviours that would suggest they are about to close their accounts?" Likewise, more complex queries are required to support the formalised business and financial intelligence reporting needed to assess overall on-going capital requirements against risk profiles within the second pillar of Basel II (the supervisory review process). These examples require that the data is transformed according to predefined formulae in order to answer these questions. Data storage. Most operational systems require very little historical data to be retained. In fact, retaining too much can actually slow down their performance. The data warehouse, by contrast, has a requirement to retain significant amounts of history. In some cases, regulatory bodies explicitly stipulate this. Information presentation. For fullest effect, the resultant information can be presented in a myriad of ways. Standard reports will always be required, especially those of a regulatory variety, but even here the media for presentation can vary (printout? Internet? E-mail?). However, most value can be derived by basing the presentation on the actions that the information should provoke. For instance, a graphical interface in an insurer's call centre can very effectively show an operator what the insurer covers in his customer's household (insured items portrayed as pictures in green), as well as identifying which potential opportunities exist (uninsured items pictured in red with targets attached). A fund manager may want notification to his mobile - phone when the value across his entire portfolio has dipped by more than 10%, while a retail bank area manager may want weekly updates on his ten worst performing branch managers. Alternatively, data could be fed on directly into another system, for example to automate and run a marketing campaign that targets high net worth individuals who use a company's savings service, but not its sharedealing one. WHAT SHOULD I DO NOW? If any of the above needs resonate within your organisation, the first step is to identify whether there is a data warehouse initiative under way within the company. Data warehouses come in a variety of shapes and colours, many of which are equally valid, provided that they have been architected from day one with the goal of answering a business need. To this aim, check that there is intimate business involvement and ownership of this project. Almost without exception, if this is missing, the project is doomed unless someone identifies and lobbies who should get involved from a sponsorship and ownership perspective. That IT needs to be heavily involved from a deployment and management perspective goes without saying. If there is no such initiative under way, identify how the three fundamental issues above (regulatory requirements, customer promiscuity and operational efficiency) will be supported from an informational perspective. If there is no convincing answer, then identify whether your organisation would benefit from a data warehouse. LLOYDS OF LONDON - EVOLVING FOR SURVIVAL. Bob Heath, Bluerock Consulting Lloyd's of London, the venerable London institution, has had a troubled decade. It nearly went out of business in the early nineties following massive claims arising from asbestosrelated illnesses and natural disasters such as Hurricane Andrew and the Piper Alpha tragedy. Then when things started to look up at the start of the new Millennium, the Twin Towers outrage rocked the whole of the worldwide insurance market, and Lloyd's of London in particular. The legacy of this can still be seen in the financial press today, witness the recent hike in provisioning set aside by Hiscox to cover losses arising from September 11th [Source: Hiscox press release dated 21 August 2003]. However, Lloyd's is continuing to provide much sought after products and services in a specialist insurance market. Lloyd's as a whole wrote insurance polices with total premiums of £14.4Bn in 2003 [Source: Lloyd's Members Services Unit February 2003]. This makes Lloyd's the world's second largest commercial insurer and sixth largest reinsurer. The 71 syndicates actively writing specialist insurance policies do business in over 120 countries. As a centre of insurance expertise the London Market, and Lloyd's of London in particular, continues to punch above its weight. The Lloyd's Market consists of a diverse range of organisations, from large corporate syndicates financed through corporate investments and share subscriptions, to traditional syndicates, many of which are still funded largely by capital provided by Names (private investors who contribute capital on a limited or unlimited liability basis). However, Names are providing a dwindling percentage of Market capacity as they are replaced by more efficient forms of funding - now just 13% compared to the 87% provided by corporate members. [Source: Lloyd's Members Services Unit February 2003]. This in turn is impacting the way in which the Market operates. For example, Lloyd's which has traditionally accounted on a 3 year basis is moving towards annual accounting and the adoption of GAAP. Syndicates are the ultimate entrepreneurial businesses, with risk being accepted under the control of a lead underwriter who then decides how much risk to pass on, or reinsure to others. Lloyd's syndicates write most of their business on behalf of large organisations, governments, projects, etc. Service companies have provided an entry into retail insurance business, e.g. motor insurance, as a means of smoothing out returns due to the countercyclical nature of the business. The fragmented nature of the Market has generally been felt to be detrimental to the development and deployment of new technologies within the Market. No one syndicate has been of sufficient size to really interest the big technology players, and obtaining consensus in such an entrepreneurial market has been hard to achieve. Indeed, the many initiatives lead centrally by the Lloyd's Corporation have had mixed success. This is analogous to some stock markets where the committees that make the decisions all tend to act in their own vested interests. That said, self interest and business trends have forced the pace of change in a number of areas in recent years. Two interesting examples are discussed below. The London Market is no stranger to the outsourcing of back office functions, although these have largely been undertaken by centralised functions under the umbrella of member-owned corporations, delivering services on a mutual, non-profit making basis. As such, these outsourced arrangements have failed to deliver the same benefits that would be expected from a commercial organisation driven by the motive to turn a profit for its capital providers. Now the old back office service functions previously undertaken by the Corporation on its Members behalf have been combined with those of a similar organisation, the International Underwriting Association of London (IUA), and placed into a joint venture (JV) with Xchanging, a company specialising in business process outsourcing which owns 50% of the JV, to form Ins-sure. This JV has the objective of improving the level of service to its members, whilst helping to modernise the existing technology infrastructure and returning a reasonable profit to each of the JV partners. A Corporation sponsored project has developed an online platform for putting customers in touch with underwriters. Previously known as Project Blue Mountain, Kinnect now delivers an online marketplace which supports brokers and corporates in identifying potential suppliers of specialist insurance. This online initiative is not intended to replace, or disintermediate, the normal route to market, and thus imperil the traditional face-to-face ethos of Lloyd's; but rather is viewed as a tool to be used in identifying potential trading partners. A number of rivals have taken a similar tack by attempting to create insurance exchanges. However, these have failed to gain significant momentum, possibly in part due to vested interests, particularly in the broker community, but also because of the nature of a business where face-to-face interactions are deemed key. There is much discussion about the future of Lloyd's, indeed the fundamental viability of the London Market as a whole is regularly questioned. However, London has a pool of specialist insurance expertise that is unsurpassed anywhere in the world. There is also a recognition that the way the market operates cannot stay the same simply for the sake of 300 years of tradition, it must move forward as the whole financial services world contines to change at a phenomenal pace. So, whilst London will continue to be challenged by low tax, low cost markets such as Bermuda, it occupies a niche that can probably continue to be successfully exploited - but only as long as the Market continues to evolve.