Under Construction.

Ten steps to data-warehouse success, starting with an understanding of the process.

Increasingly, insurers are using data warehousing as a strategic tool to help them win new customers, develop products and decrease costs. Searching through mountains of data generated by corporate transaction systems can provide insights and highlight critical facts that can significantly improve business performance.

Until recently, data warehousing has been limited to large insurers, but lower costs are making data warehousing practical for smaller companies, too. Turnkey integrated analytical systems are reducing the cost, time and risk involved in implementation. Access to the warehouse has been limited to highly trained analytical specialists, but now corporate portals make it possible to grant access to hundreds or thousands of employees.

The average cost of a system is $1.8 million, according to the Palo Alto Management Group Inc., and actual construction is not easy. The following 10 considerations help improve the chances of success.

1. The task is probably harder than you expect.

Experts frequently report that 30% to 50% of the information in a typical database is missing or incorrect. This may not be noticeable or may even be acceptable in an operational system that focuses on swiftly and accurately processing current transactions. But it's totally unacceptable in a data-warehousing system designed to sort through millions of historical records to identify policies with a higher than acceptable level of risk or to select potential customers for a new product. And, even when the data is correct, it may not be usable in a data-warehouse environment. For example, legacy system programmers often use shortcuts to save disk space or to improve speed--such as using numbers instead of names of cities--that make the data meaningless in a generic environment. Another challenge is that database schema often change over the life cycle of a project, yet few companies take the time to rebuild historical databases to account for these changes.

2. Understand the data in existing systems.

The first step in any data-warehousing project should be a detailed analysis of all databases that might be used. It is important to understand the interrelationships between systems because they must be maintained when the data is moved into the warehouse. The implementation of the data warehouse often involves making changes to database schema. A clear understanding of data relationships among heterogeneous systems is required to determine what the impact of any such change might be. Otherwise, it's possible for changes to create inconsistencies in other areas that ripple across the entire enterprise, creating enormous headaches.

3. Recognize equivalent entities.

One of the most important aspects of preparing for a data warehouse is to identify equivalent entities and heterogeneous systems. A problem can arise when the same piece of information appears under different field names in different parts of the organization. For example, two separate divisions may be servicing the same customer yet have the name entered in a slightly different manner in their records (such as AIG and American International Group).A data-transformation product capable of fuzzy matching can identify and correct such problems. More complicated issues arise during mergers when the merging corporations manage stored data differently. Establishing a common database structure can be just as important as merging the corporate cultures.

4. Use metadata to support data quality.

Metadata is data about data, such as tags that indicate the subject of a Web document. Metadata is critical to the success of a data warehouse. Many types of metadata can be associated with a database, to characterize and index data, facilitate or restrict access to data and determine the source and currency of data. It is challenging to synchronize the metadata between different vendor products, different functions and different metadata stores. A major data center easily might have a 500-page schema layout or COBOL copybooks with more than 200,000 lines. And it is likely many undocumented changes have been made to minimize the work involved in dealing with historical data. That's why it is important to begin early to create and capture metadata for interfaces, business processes and database requirements. There are products available that help integrate metadata from disparate sources into a central repository to help administrators and users.

5. Select the correct data-transformation tools.

Data-transformation tools extract data from the operational sources, clean it and load it into the data warehouse while capturing the history of that process. This transformation process may include the creation and population of new fields from the operational data, summarizing data to an appropriate level for analysis and error checking to validate the integrity of the data. Look for a tool to map data from source to target with a simple point-and-click interface. The ability to track and manage the relationships of interrelated data entities that are affected when changing database structures is also useful. Try to find a tool that can capture and store metadata during the conversion process.

6. Take advantage of external resources.

External sources of information, such as data from an agent's transaction-processing system or credit data provided by a third party, often greatly increases the value of internal information. Rather than simply comparing premium income with that of the same month last year, external data might make it possible, for example, to compare premium growth with the increase in the overall market. Or suppose that you want to estimate the income of each of your customers. You might be able to obtain a database containing the average income for every ZIP code in the country. This will not provide accurate answers for individual customers, but it can generate a good estimate of an aggregate grouping, such as everyone who is between the ages of 30 and 45 in Kansas, to use in a targeted marketing campaign. The integration challenge is even greater when external data sources are involved. In some cases, external data will differ so drastically from existing schema that data-transformation algorithms will be required to ma ke use of the external resource.

7. Use new information distribution methods.

Recently, the biggest technological improvements in data warehousing have been in information delivery. Previously, highly skilled analysts were needed to prepare information in the format requested by users. Now, information can be delivered in several ways directly to people who need it. For example, underwriters can subscribe to regular reports and have them delivered by e-mail. If, when run, the report contains data that meets the subscription criteria, a customer-filtered view of the data will be delivered to the user as an e-mail attachment. The report data stays securely and economically on the server, and only the pages requested by the authorized user are sent on demand. Another option is to let the user log in, search for data and open the reports that provide the needed information. Only the pages requested by the user are sent over the network, to minimize unnecessary traffic.

8. Focus on high payback marketing applications.

Most of the really hot data warehousing applications involve marketing because of the potential for an immediate payback in the form of increased revenue. For example, a life insurer can match personal characteristics to purchases of specific policies and then use this empirical data to produce direct-marketing campaigns that generate higher sales because they are targeted much more closely to their customers' needs. Insurance firms selling higher-cost business policies are weaving data warehousing into their relationship-based businesses to dramatically increase the information flow between their firms and their customers. Some firms are using similar methods to sort out applicants whose characteristics match the profile for fraud risks.

9. Tout early successes.

A wide range of off-the-shelf solutions has made it possible to drastically reduce cost and lead-time requirements for data-warehousing applications. Prepackaged software usually won't complete project objectives, but it often can be used to provide specific solutions in a short time that serve as a training and demonstration platform and, most importantly, build momentum for full-scale implementation. Even for the largest-scale applications, technology surveys should be performed to maximize the use of prebuilt technology.

10. Don't underestimate resource requirements.

The hardware requirements for data-warehousing database servers are surprisingly high. The primary reason is the speed required to slice and dice data over and over again to meet the varying needs of users throughout the organization. Database size also plays a part in the server performance requirements-which range up to terabytes and higher. Be sure to select a scalable platform regardless of how much headroom you have provided in your server specification. The typical data-warehouse implementation starts out at the departmental level and grows over time to an enterprisewide solution. Purchasing servers that can be expanded with additional processors is one possible approach. A more ambitious idea is to combine loosely coupled systems that enable the database to be spread out over multiple servers, although it appears as a single entity to users.

The people who design and build the data warehouse are critical to its success. The experience of a diverse team coupled with a business focus and proven methodology can make a huge difference. A large percentage of midsize and large companies use outsourcing to avoid the difficulty of locating and the high cost of retaining skilled information technology personnel.

Phil Blackwood is vice president of business intelligence, Syntel Inc., Troy, Mich.