Print this
Collecting all your data in one place seems like a great idea. Just try to make it work.

The Data Warehouse Problem

By Tom Groenfeldt

The premise is simple and the goal alluring: consolidate derivatives positions from all across the globe into a single data warehouse to get a comprehensive view of enterprise-wide risk.

In the last few years dozens of financial institutions have spent hundreds of millions of dollars to move mountains of financial information from around their global operations into gigantic data warehouses. In many cases, the result has been disastrous. In fact, Winston Churchill's description of democracy as the worst system except for all the others might apply just as well to data warehousing for managing risk across the enterprise: theory often founders when confronted with operating realities, the processes are complex and often messy, and the results fall far short of perfection.

While some critics say data warehouses are quite useless for risk management, others believe they have a value but are often oversold by consultants who know that a data warehouse project guarantees lucrative employment for a couple of years. Success in this endeavor involves resolving a number of tough problems related to corporate planning, data management and systems technology.

All together now

A data warehouse takes data from multiple sources and stores them on one or more databases whose complete store of information can be accessed through a single server. Most data warehouses are constructed to help corporate America collect key pieces of data for a variety of purposes. Wal-Mart, for example, a leader in data warehousing, can track sales from each of its stores and determine what has sold and what needs to be replaced—and can even tell what time of day items sell best and how price changes during the day affect sales.

Designing a data warehouse to measure risk is much more complex because it doesn't function with simple static data. To work properly, a data warehouse for risk management should gather all the information about all its transactions from every trading and lending center around the world, and then consolidate each transaction in storage with its entire context—including history, attributes and derived attributes. Then the trades must be marked-to-market from a data feed or computed theoretically to calculate exposures.

Once that's done, a bank can use its warehouse to get a comprehensive view of its existing risk, analyze trends in that risk, and rebalance its portfolio to correct for overexposure to a currency, counterparty, interest rate view or instrument type. If the bank were really ambitious and had the budget for huge bandwidth, it could replicate the data warehouse to its major trading centers so they would also have the full array of information on-site for immediate response to queries.

"Data warehousing is fabulous technology for certain things, but I am not certain that capital markets instruments are one of the things it is best at.”
Charles Wurtz
management consultant,

"The data warehouse is usually the heart of any enterprise-wide risk management,” explains Barbara Smiley, a principal at Meridien Research in Newton, Mass. "Enterprise risk applications call on data from a large number of transaction processing systems. Separate systems for separate products at separate subsidiaries in separate locations feed into one normalized form so calculations such as value-at-risk can be run against a consistent set of data.”

Although gathering all that data into one central location is an expensive, time-consuming project, there aren't many alternatives for those who wish to assess their risk across an enterprise. "There isn't a way to aggregate the data without a functional data warehouse,” insists Steven Silberstein, CEO of FAME, a firm that produces software used for storing, analyzing and publishing time series data. "Firm-wide risk requires that a risk department separate from the trading department has a copy of all the relevant data, a copy of all the analytics that are being used to value derivatives, and a cleansed and archival copy of the market data that drive that analysis. The functions of a data warehouse have to be accomplished somewhere in a firm-wide risk management exercise.”

But drawing a diagram on a white board is one thing and making it work is another. "There is a strong argument for data warehouses,” says Peter Vinella, president of Vinella & Associates, who has created something of a stir speaking out against the use of data warehouses for risk management. "You can use data warehouses to create cash flows, customer reports and things like that. With the object-oriented technology growing popular, there seems to be a way to extract data in a meaningful way.” But he has strong reservations about using data warehouses for risk management, because he believes database companies have sold them as panaceas, overlooking the difficulties of making the concept work.

"It's much better to have a data warehouse for 95 percent of the positions and manage the rest in some sort of pragmatic way.”
Keith Bear
solutions executive for securities and capital markets in London,

"Data warehousing is fabulous technology for certain things, but I am not certain that capital markets instruments are one of the things it is best at,” echoes Charles Wurtz, a management consultant at Veristics, a firm specializing in risk management. "The advantage of a data warehouse is that you can fairly rapidly design, construct and execute sophisticated queries and interrogation against your data assets, and it lets you do trend mapping against your business. It is extremely flexible. The problem is that it can be slow. It works well for trend forecasting, but it is too slow and usually designed with nonreal-time interfaces to operational data.”

Ready, fire, aim

Creating a data warehouse is a huge challenge for any financial institution. But most banks compound the problem by starting off on the wrong foot. The first mistake banks usually make is failing to take the time to figure out what they want in a data warehouse for before they begin to build. "The first thing to do is understand the business problem,” suggests Ade Osinubi, the senior technical architect at AMS. "That should drive the business solutions you put in place. Once a firm understands what data will be stored and who will use them, the technical issues such as the number of databases to use, their locations, timeliness and bandwidth requirements can be addressed.”

That kind of thinking requires an enormous amount of prep work that most firms are eager to cut short. "It's easy to come up with an idea of a data warehouse and populate it,” says John Wartman, a principal at AMS, "but without a specific purpose it becomes a data graveyard.” "That seems obvious, but often one side is doing one thing in an organization and the other side doesn't know what it is doing.”

A data warehouse can be designed, for example, to serve the needs of the finance department as well as risk managers by constructing relatively simple extensions to data tables. But to design those extensions, one must do a business analysis of all the departments that will take information from the data warehouse. "What you have to do is come up with a common data requirement, and the requirements for the finance department aren't necessarily the same as for market risk,” he explains. "You need to look at common denominators and extend beyond that for other applications.”

Most often, however, managers of a data warehouse project will underestimate the organizational problems this kind of corporate self-analysis will uncover. "Risk systems tend to highlight the discrepancies in the business models of the bank,” says Keith Bear, the IBM solutions executive for securities and capital markets in London. He notes that it may be difficult or impossible, for example, to find a single simple solution that satisfies both trading-room risk controllers and enterprise-wide risk managers.

"The data you want are always in the wrong place—wrong technologically and wrong geographically.”
Michael Adam
executive chairman,

The key to a successful project is figuring out the existing and potential needs for information without creating huge committees that require consensus to move ahead. Otherwise, a bank's natural bureaucratic instincts will lead to such complex requirements that building a practical system becomes impossible.

Once you get past the basic organizational issues, the next challenge is addressing the problems associated with collecting and aggregating the data. Wal-Mart may do a good job of counting the rolls of paper towels in its warehouses and on its store shelves, but the inventory is significantly more complicated in the derivatives business.

"It is not a trivial exercise to understand the relationships of the data well enough to build a useful data warehouse,” says Vinella. "It's not unusual to take three or four tries before getting something that is functional from a business point of view as well as a technical one.” He recalls one project that involved developing a system that could handle everything from barter to securities. "We did a good job on the data model, but it was so large that you could never finish programming it in our lifetime. So the guys there are still using spreadsheets to trade derivatives.”

One of the most important decisions to be made is determining when to capture every bit of data associated with a transaction and when to aggregate. Capturing everything will make a system impossibly slow. But if the data have been aggregated at a lower level to simplify the consolidation process, a risk manager won't be able to drill down to get the details they need to understand a particular deal.

What's driving enterprise-wide risk?
You don't have to look hard to find the most obvious reasons why banks are spending billions to manage risks across their far-flung enterprises. The desire to avoid major losses from Nick Leeson-esque disasters or the Asian currency crisis certainly rank high on the list.

But the primary drivers behind the millions of dollars thrown at data warehouses projects are regulatory requirements, particularly the Capital Adequacy Directive from the Bank of International Settlements. The rules permit banks to reduce their Tier One capital if their proprietary risk management models can justify it. "The BIS requires back-testing value-at-risk numbers,” says David Penny, chief technology officer at Algorithmics. "You need to have a history of actual P&Ls and a history of the VAR number you have counted the night before the P&L actually happened. The BIS rules allow you to use your own models to compute your VAR, but you have to back-test, and if you don't back-test properly, you get your factors increased. No one wants to lock up money in T-bills. That is the primary reason that people are putting in warehouses like this—to satisfy back-testing requirements.”

Having the ultimate enterprise-wide risk system will yield a number of other advantages that should have an immediate impact on an institution's bottom line. A number of banks are trying to establish VAR-based limits for traders. A particularly profitable deal examined in isolation might pop up with an impossibly high VAR number. If it were examined as part of a global portfolio, however, it could be much more palatable.

The same global perspective can help reduce the cost of hedging. One trading desk may be putting on hedges that are matched by trades executed in the opposite direction by another desk a few hundred feet away. A good system that linked the two groups could permit an intrabank hedge at zero cost.

Similar efficiencies are available by accessing credit lines on a global scale. A particular swap transaction might have a high level of credit risk when examined on its own. Viewed incrementally, however, the risk might have been considerably less if the bank had the right counterparty netting agreements in place. If the bank didn't have access to that information, it might have been impossible to do a profitable trade that otherwise made sense.


Before data can be drilled, mined or aggregated, of course, they must be drawn from systems that are often reluctant to release them in any form useful for risk analysis. In most cases, much of the data come from legacy back-office systems that don't store the data with the data models. As a result, the value of an instrument might be calculated on the basis of a model that's nowhere to be found. "If you are trying to value a cap, for instance, you need the cap pricing model as part of the data record,” says Vinella. "Legacy systems usually won't pass you that information, and often it is not even on the ticket, so the price of a deal on the data warehouse will be different from the price on the legacy system.”

Vinella recalls a transaction at a bank where one leg of a swap was not entered into the system as a swap, but simply a set of deposits. "That illustrates the limitations of legacy systems, the limitations of hardware and the limitation of data warehouses,” he explains. "These systems can cost $25 million to $30 million, and you don't get much for your money.”

One way to avoid the hassles associated with a data warehouse project is to skip constructing one altogether. A number of influential vendors and consultants are suggesting replacing the concept of a single data warehouse with forms of virtual data warehousing—linking geographically separated databases. IBM's Bear, for example, believes it makes sense for large banks to coordinate a set of interacting databases rather than trying to move all the necessary data to a single location. "This is not for technical reasons,” he explains, "but to cope with the complexity, the organization's goals and the inevitable internal politics.”

In some cases, the approach involves constructing a virtual data warehouse by calling up the data from different remote systems via middleware and other technologies. Financial Engineering Associates, for example, has developed a system to take overnight data and add new trades to them incrementally during the day, rebuilding the risk profile on the fly. Redpoint, another vendor, keeps the trading information in its system in memory caches for specific applications so traders can check limits against the cache before trading. The system runs calculations off a cached representation of the data, and when transactional changes occur, that cached model can be updated.

Alternatives to data warehouses
Do you really need a data warehouse to perform risk management on a global portfolio? The often nightmarish problems associated with setting up warehouses encouraged one group of vendors to come up with an end-run solution that collects data from existing databases.

One demonstration, which was staged in April in New York, was run on two Sun workstations with a Windows NT front end, using the Sybase Adaptive Server, FAME's Relational Gateway and Inventure's Ranger. The system arrived at an office on a Wednesday and was fully assembled, with Sybase, FAME and Ranger, by noon Friday. FAME's Relational Gateway allowed users to access its time-series data through standard SQL queries, treating the FAME information like a table in Sybase. Ranger, by contrast, allowed users to create complex queries that pulled together the data from FAME, from static databases anywhere in the world, and from live data feeds. The results were displayed in a spreadsheet, graphically, and as heat maps.

The project tried to show how it's possible to pull public and proprietary data, often held in different systems, together in one place to analyze them and publish the results back to users. In most cases, legacy systems store proprietary data in a way to facilitate administration rather than analysis. "Without the preexisting infrastructure to deal with, this could be straightforward,” explains Michael Adam, Inventure's executive chairman, "The data you want are always in the wrong place—wrong technologically and wrong geographically.”

With the immediate access to a wide range of data, a bank could graphically display risk-adjusted return on capital by desk, product or trader and see immediately where they are earning the best returns.

The three-way combination doesn't claim to make existing data warehouses obsolete—it can take information from them and combine it with information that resides elsewhere. "This approach is faster and more adaptable on the fly, which is what finance needs because it is so chaotic,” said Charles Wurtz, a consultant at New York-based Veristics, who organized the project. "This becomes a value-added to what you have already built.” Users can easily plug in their own analytics or buy models from companies such as FEA to fit into the system, he added.


Lurking on the horizon are far more advanced systems using intelligent agents. Halcyon, a Toronto-based firm that originally developed tools to monitor the systems at nuclear power plants, is applying the technology to risk management. Autonomous agents can query data, run algorithms and pull information together from multiple sources and make decisions based on business logic, says Mark Notten, a director at the company. Sun Microsystems plans to use the technology to monitor its internal networks, and Halcyon expects to announce a deal to track financial information for a major international bank.

If creating a data warehouse seems too intimidating to tackle, take heart. Consultants caution against trying to solve every problem in front of you. The old KISS rule—Keep It Simple, Stupid—applies just as well in data warehousing as it does in politics. "It's much better to have a data warehouse for 95 percent of the positions and manage the rest in some sort of pragmatic way, such as spreadsheets for exotics,” suggests IBM's Bear. Technical challenges, such as database size, computational power for Monte Carlo processing and networks, can usually be solved with enough money and equipment. "The technical limits are not so big when compared with organization and people. What is important is to keep it simple on one hand and to be careful not to forget anything on the other. It can be a rather painful experience, but if managed pragmatically, it is worth it.”

"There are benefits, and organizations shouldn't back off thinking it is an impossible dream because it certainly isn't,” adds AMS's Wartman. "In any development cycle we take an iterative approach. We do a small piece, see what went right and change what needs to be changed, and then roll it out by product or geographic area. If you do it on an incremental basis, ultimately you wind up with a system that does as much or as little as you want.”

Wartman adds that he's seen banks lose years by agonizing rather than building systems. His advice: get moving, but move slowly. "We have been talking to people for more than a year, and they are still talking about the same issues,” he concludes. "If organizations had started two years ago they would be finished by now. At some point you just have to bite the bullet and get started.”

One of the biggest problems associated with maintaining a data warehouse is adapting it to the continual flood of new products developed by the front office. Systems people managing the middle office will go to great lengths to create a standard data model and link it to a data warehouse, but when a new product comes down the pike, they often have to retool everything to get it into the system. The trickiest part is integrating the new product into the warehouse without compromising the continuity of historical data, which are so important for risk analysis and management. Traditional data warehouses that use static or custom-built data models hard-wired to front-office systems often require modifications to the data storage to accommodate new instruments or trading systems. That can change historical information. New York-based Axiom Software Laboratories uses a dynamic approach that can readily adapt to new instruments and different models while maintaining consistent history, including original data and calculated results.

"If you used one system developed a few years ago to do interest rate swaps, and then developed a new system, the two systems could have totally different data types, completely different structures and even different environments,” explains Alex Tsigutkin, the company's president and founder. "Nonetheless, you need the ongoing history of information for back-testing. That is difficult to do in the traditional data warehouse built into most risk management systems, because the warehouse employs a standard static data model built for a particular transactional and legacy system.”

Axiom solves the problem through a product it calls dynamic data warehouse, which includes virtual data mapping and dynamic data storage. The virtual data mapping is highly proprietary, so Axiom won't discuss it in detail. It allows a firm to consolidate data sources—which can number more than 100 at a large firm—and administer the data elements of the warehouse.

For dynamic data storage, the Axiom system relies on a configurable server that can maintain multiple data models for both importing and retrieving data.

Unlike other systems, which run a single model and a single method of storage, therefore requiring extensive programming when any changes are made, Axiom builds flexibility into its system. Axiom gives its users administrative tools to adapt to new models and new transaction types, so banks can update their systems without maintaining a large staff of programmers. "In our concept, the middle office and data warehouse are dynamic enough to adapt to any changes in the underlying data coming from the front office, the back office and the market data sources,” says Tsigutkin. "With the dynamic data warehouse methodology, if you replace or modify a trading system, you don't have to reprogram your warehouse and your analytical applications, because this is done automatically by the dynamic data warehouse, which keeps multiple references to the same information.”

Another associated problem is adapting risk management systems to a variety of different models. In many older systems, users are locked into a particular model for pricing certain instruments. The company has gone to great lengths to open up its risk monitor system so users can switch between a number of different, competing models developed by outside vendors, including Monis, FEA and others.

The same approach also allows firms to plug in different risk methodologies. Though most clients use value-at-risk, Axiom is able to structure its software specifically for the risk methodologies in use at the firm in question, including both market and credit risk, as well as consolidated firm-wide risk.

Links to outside information suppliers are particularly important in credit risk management. The firm works closely with both Standard and Poor's and Moody's to integrate credit data into its system. Users can also make use of JP Morgan's CreditMetrics and Credit Suisse Financial Products' CreditPlus as well as Axiom's own proprietary credit risk methodology.

Another thorny adaptation facing software vendors involves updating their systems to allow users to meet the new reporting requirements of the Federal Reserve Bank and Financial Accounting Standards Board. The risk monitor system already has reporting functions in place for both institutions as well as the Bank for International Settlements and other European regulatory bodies.

For firms that don't want to purchase risk management systems, Axiom offers outsourced risk processing. Axiom will download a client's risk data, run the analytics and return the reports to the financial firm.

Accounting firms and software houses project substantial growth in outsourcing, primarily from corporations that don't have the in-house expertise and can't justify the substantial investment required for sophisticated risk systems but still have to meet SEC and FASB requirements to value and report their derivatives holdings.

Fuji Bank's New York office is using Axiom for both market and credit risk, and is integrating Axiom's risk monitor and dynamic data warehouse with its Devon back-office and other front-office systems. Other clients include Swiss Bank Corp., Bayerische Vereinsbank, Lehman Brothers, SBC Warburg Dillon Read and TransCanada Pipelines. The company says it is also marketing its software to energy and accounting firms and is expanding its sales presence in Asia.