American Bankers Association - Trends - Issue No. 53
Reprinted from Trends Magazine with the permission of the American Bankers Association

IN-DEPTH

Building a Financial Data Warehouse

— by Alex Tsigutkin

What is a Data Warehouse?

The term "warehouse" does not necessarily have positive connotations, particularly within the finance industry. Warehouses have often tended to be the home of old paper records, and memories of them often include requests for information or copies of records that may have resulted in lengthy delays and dissatisfied customers, auditors, or regulators. Fortunately, record-keeping methodology has improved tremendously in the past decade, and along with the development of optical storage, most of the apprehension at the thought of a "warehouse search" has taken its place in history.

Today a new image of a warehouse has appeared — in this case a data warehouse. Understandably, potential users have some valid questions, such as: "Will I be able to get to my data?" "How fast can I get to it?" and "How much will it cost?" These three questions all have the same answer: It depends. As with a physical warehouse, the location, organization, and accessibility will all have an impact on the ability to retrieve information.

Perhaps it's best to start by identifying what a data warehouse is not: it is not a tape archive or off-site replication of data, although this second concept may represent a step in the update of a true data warehouse.

In reality, a data warehouse is a common repository of data from multiple source systems aggregated based on a highly sophisticated meta data (business rules) with historical data retention capabilities. Business rules are dictated not by existing systems but by the needs of the decision maker.

The location, type of computer, database system, and software used to manage the warehouse will all have an impact on the cost and the accessibility of the data.

Who Needs a Data Warehouse?

Truthfully, not everyone in the financial industry will require this type of tool, but in practice most can benefit from a well-designed and implemented data warehouse. Whether performing routine business activities calls for combining information form more than one in-house system or performing sophisticated analysis of the bank's balance sheet, chances are that you're a prime candidate. Any time spent "eye balling" multiple reports or re-entering data into spreadsheets or other off-line tools is time that could be spent on more productive tasks. When you add in the potential for error with these inefficient tasks, you can easily start to accumulate a considerable cost for not having a data warehouse.

The typical financial institution has multiple transaction processing systems that may reside on mainframes, client/server databases, LAN's, and occasionally even a stand-alone PC. When you factor in multiple branches, possible parent/subsidiary relationships, and mergers with dynamic changes and constantly increasing complexities of tasks such as enterprise wide regulatory and consolidated reporting, asset/liability management, tax reporting, limit control, profitability measurement and risk management become increasingly complex. Even on a departmental level, particularly in trading and securities-related departments, the ability to consolidate data on a transaction-level from several systems presents a serious challenge and an additional cost.

Financial Data Warehouse vs. General Purpose Data Warehouse

General purpose warehouse products primarily address data storage questions without solving current infrastructure problems. The integration of transaction processing systems with general ledger and other back-office systems currently ill-suited for sophisticated analysis will not automatically answer the questions of the decision maker.

The scope of new financial products with their dynamically changing attributes requires far more sophisticated meta data management capabilities, the ability to use business rules to perform complex calculations with the aggregated data, for example, mark-to-market valuation, dynamic series engines, and netting agreements processing. These product-, customer-, and market-related requirements demand more than a general purpose date warehouse can offer and adding them on, if possible, makes the general purpose data warehouse far more costly to implement.

A successful financial data warehouse must address issues of volatile financial markets and provide financial experts with the tools to define business rules in a way that manages the data warehouse. These same tools must continue to manage as those rules evolve, rather than leaving the institution at the mercy of hard-coded financial applications or placing additional pressure on internal system staff.

Getting Started

Once you've decided to implement a data warehouse where do you start? As is the case with most projects, the plan is as important to success as the implementation. A successful plan must identify your business objectives and the source systems, evaluate the technology available, and prototype the output. Once you have reached this point and are satisfied with your findings, an implementation plan can be developed, reviewed, and put into practice.

Identifying Business Objectives

Put simply, this step consists of identifying on a high level everything you expect to get from a data warehouse, and how all of this fits into your organization at the enterprise level. Individual goals should be established on a business level by identifying areas that will benefit from the availability of consolidated data on a timely basis. Be sure to take into consideration all reporting requirements, including any reports currently constructed manually.

Consideration should be given to all areas where the rapid evolution of products and services has resulted in data being spread over several systems, rather than being consolidated. This is most typical of trading areas, where systems supporting options may supplement securities trading systems, which may in turn supplement a primary treasury system. It also has been common to the trust area, where short-term instruments, structured instruments, bonds, and equities all may reside on different systems, and global processing has become a requirement at both a custodial and issuance level.

The short-term benefit of financial data warehouse deployment is to preserve existing systems while providing a multidimensional and user-defined view of existing requirements. In the long term it can provide the company with a strategic competitive advantage for efficiency re-engineering decision support and transaction processing systems and procedures.

Identifying Source Systems

Transactional.
Includes most traditional banking systems that store information by trade or event

Credit Related.
Includes loans, guarantees, letters of credit, credit facilities and other related products

Control Data.
Covers a variety of enterprise-wide information, such as identification codes
(currencies, countries, etc.) accounts and customer files.

Market Data.
Includes prices, interest rates and other time-series information.

For each system and type you should identify not only the data itself, but information on the ability to transport the data (interfaces and downloads available, reporting tools, etc.), frequently required (daily, intra-day, longer time periods for "control" data), and level of data (in some cases transaction-level may be appropriate, in others it makes more sense to use positional or net data). Consider security and administration policies at this point, as well as any data-retention requirements.

Typical steps in the process include construction of conceptual process flows and data models, documentation of data structures, and compilation of volumes for data sources. A composite of all data structures should be created, identifying common information and detailing differences.

Evaluating Technology

The previous section touched on "interfaces and downloads" at the source system level. An extension of this is identifying the internal network of the enterprise (commonly referred to as an "Intranet"). The ability to move data efficiently over an internal network has become vital to any financial institution's ability to process and personnel involved in the administration and support of your institution's network should participate in any effort that could make substantial demands on network resources.

You should also review any internal standards for data base products and platforms. The widest variety of tools will most likely be available for client/server (including both the Unix and NT server platforms), but some financial institutions are not currently positioned to administer these systems. Similarly, the available SQL-based relational data base products each offer advantages over flat file systems, but all have slightly different administrative requirements. Most of these products also have gateways to other products, which make the process of transporting data smoother; some of your current data base products may support replication. You should take into consideration any expertise and products currently available in-house.

"Prototype" the Output

While it may seem premature to build a full prototype, you and other potential users should prepare samples of printed reports for the primary functions to be addressed by the data warehouse. Review the business requirements and current reports to determine what modifications should be made to take advantage of consolidated data and new reporting tools. Don't forget current manual reports, and don't settle for a different version of what you already have. This is your opportunity to define what you want, not what you'll settle for.

At the same time, you should consider on-screen viewing as well as printed reports, taking into consideration that what you see on the screen does not have to be limited to what you would see on paper.

Identify groupings, sorting, and selection criteria possibilities. If a report can be produced, chances are these properties can be built-in with the currently available reporting tools, and that they may be available on a "drill down" basis. Any requirements for graphical reporting should be identified at the same time.

Alternatives

You should now be in a better position to make two important decisions:

1. Do I continue with the project or live with what I have?

This question must always be asked at least once, and usually comes up at several stages. By this point you should have a good idea of the potential benefits, the deficiencies of the current environment, and the scope of the project. If you put all these together and arrive at a decision to continue, the next question should be:

2. Do I build my own, or buy a warehouse?

Once again the answer must be, "it depends." There are many tools available that streamline the process of building a data warehouse and getting the necessary output. At the same time, there are vendors available with products that can support most requirements. Key questions to consider will be:

Building

1. Do I have the resources (financial and personnel) available to build a data warehouse?
2. Will I have the resources to maintain it as business requirements change?
3. Assuming I build, will there be a commitment to complete the project and not settle halfway?

Buying

1. Is the proposed solution open and adaptable? Can it conform to my data and the way I business, or must my data and what I do conform to the "solution?"

2. Can other applications use the data in the warehouse or will I be totally dependant on one tool for access?

3. How can the data warehouse handle the idiosyncrasies of my institution? Will it understand that USA, US and U.S. all are the same country, or will it be additional expertise to handle these types of issues?

4. Will I be able to construct complex business questions (and answer them), such as "What is the net credit amount for liabilities to foreign banks over $ 100,000 with residual maturity under one year?"

5. What do I do if my business requirements change? Are the tools to keep up to date included or must I pay extra and/or wait? Are updates available at all?

6. Does the proposed solution provide sufficient and flexible data management tools?

Consider Support

Whatever your decision, you must be able to support your warehouse, which raises a few additional questions:

1. How will the data be furnished? How often?

2. How can I achieve the same level of security? Will "sensitive" data be restricted to responsible parties?

3. Will availability of a data warehouse cause me to change organizational structures? (i.e. establish a utility unit to support multiple products?)

There are many other questions to be addressed in all of these cases, and many will apply to more than one. They key issues will be getting the ob completed (an incomplete warehouse will be better than none at all), achieving data consistency (making it possible to net and group data across existing systems), and making the data accessible (available to applications and reporting tools).

What Can You Expect From Your Data Warehouse?

Assuming your project was a success, you should now have a mechanism available for looking at your enterprise data---data across systems, departments and branches---in formats meaningful to you and to other users. This should be provided through organized tools that offer you the ability to define views of data, totals and summaries at defined aggregation levels and "drill-down" capability. You should not be limited to time-consuming searches for routine data or to skimming over data until you find the one piece you need.

Finally, you should have the ability not only to work with the data access tools but to utilize your data within additional tools that can provide analysis beyond the scope of the warehouse itself. An effective warehouse should allow applications to efficiently access consolidated and historical transaction data, or at the very least provide extracts in user-defined formats which may be made available to outside applications.

At the end of the project you should have a tool powerful enough to meet the challenges of your enterprise regardless of business growth, acquisitions of new systems, integration of new data bases, or evolution of requirements, making it possible to spend more time focusing on your business decisions and less on piecing together information from different systems.

Alex Tsigutkin is President of Software Laboratories Inc. Mr. Tsigutkin founded SL 10 years ago and has specialized in providing Banking Systems Integration solutions. These include Financial Data Warehouse and Integration Center Tools, as well as Risk Management and Regulatory Compliance. Before founding SL, Mr. Tsigutkin served as a consultant for leading financial institutions on Relational Data Bases, Transaction Processing and Decision Support applications.