ROI.

Who would have thought three letters could create such grief?But lets face it: If the sky was ever the limit for IT budgets, itsure hasnt been for some time. Execs signing off on datawarehousing projects want to see results, and fast.Unfortunately, that doesnt fit well with the historical model ofdata warehousing, with its multi-year timeframes and high costs.Todays economic realities have caused many insurers to shift theirdata warehousing efforts to projects that have more immediate andclearly defined financial or operating impact. Those realities alsohave driven the development of some economic strategies on whicheconomically successful warehousing projects develop. For a guideto tried and proven tactics, read on.

1. Focus on impact analytics that save or make money. Like anybusiness, insurance looks to increase revenue and reduce expenses.Meeting these objectives requires focusing on customer and produceranalytics for such goals as gap analysis, distribution channelefficiency and profitability, and targeted marketing, as well asfinding ways to cut operational and claims costs and cancelbusiness thats likely to be unprofitable.

A textbook example is Allstate Finan-cial, whose datawarehousing efforts garnered the insurer a 2002 Best Practices inData Warehousing award by the Data Warehousing Institute. Thewarehouse, which took just over a year to implement, can hold up tothree terabytes of data in an Oracle database, using Ab Initio forextract, transform, and load (ETL) from nine differentadministration systems that support Allstates life insurance,long-term care, annuities, and mutual fund businesses. SASEnterprise Miner and Brio are used for analytics, and Proclarity isused for online analytical processing (OLAP).

One of our key strategies was to become consumer-centric, saysJohn Hershberger, Allstate Financials assistant vice president ofdatabase marketing. You cant do that without information about yourcustomers. So first and foremost, we looked to better understandwhat their needs were and to meet their expectations aboutproducts, service, and support. That meant continued involvement ofboth business and IT in the warehouse design.

One of the first impacts of Allstates warehouse was theelimination of duplicate mailings to policyholders who heldmultiple annuity contracts for different beneficiaries. We built aninternal householding process using Trillium and built a carrierpresort mail file. We estimate that putting a single prospectus inthe hands of a customer approaches $5. Currently Allstate isdevoting its resources to analysis of the economic values ofproducer relationships from the individual agent up to theaggregated agency level, including studying customer retentionactivity.

While Allstates actual project costs were not available,Hershberger reports the company spent serious money on theinstallation. We still needed to create value along the way, heexplains, and break-even is not a long way off; were getting there.It was never a concern to us that were not going to gain value, butweve kept a focus on areas of impact on profit and loss.

Carriers may still find it difficult, however, to base decisionsregarding warehousing and analytical projects purely on economicfactors. In the business case supporting the project, you canstruggle about how to measure the results because you dont have theinformation in the first place; thats why you need the warehouse,says Richard Marx, vice president at Cap Gemini Ernst & Youngand leader of its North American insurance consulting practice. Ittakes a leap of faith by management to make that investment, and inthese economic times, its a tough sell.

2. Decide if an enterprise data warehouse (EDW) or data mart isa better fit for your legacy environment. The industry continues tobe divided into two camps when it comes to the better way toapproach a warehousing project: Create an enterprise data warehousefrom the start, or develop individual, tactically focused datamarts. The more disparate systems and different data formats aninsurer has, the more difficulty there will be in building an EDW.But even if tactical data marts are the immediate solution, thedifficultand potentially expensivework of standardization shouldstill be completed first to save money in the long run.

The trick with taking the approach of starting small is how doyou set it up in a way that you can expand the depth and breadth ofthe warehouse so you dont have to redo it, says Marx. Fortunately,the advantage companies now have over the ones that started threeto five years ago is the tools, particularly ETL tools, have gottenso much better. Its feasible to start small and, through variouswell-planned iterations, expand the comprehensiveness of thewarehouse and begin delivering value.

Those who have built an EDW first have found they can, in turn,create subordinate data marts that share common standards whilealso addressing point solutions. Consider, for example, InsuranceServices Office, Inc. (ISO), which reports to have the largestinsurance datastore in the world, with seven terabytes of onlinedata and 114 terabytes of near-line data. Over time, ISO hascreated a number of subordinate data marts, such as its ClaimSearchdatabase, containing 360 million records of claims.

Raw claims data is stored in a central datastore, and separateclaims records are stored in the ClaimSearch database. Tosynchronize data when a carrier makes a change to a reported claimtransaction, ISO created an online correction tool that buildsonset and offset records. Its a closed-loop process, says MichaelDAmico, ISOs chief technologist and director of systemsengineering.

3. Decide what data to warehouse. With data-storage costscontinuing to decrease, this decision no longer involves quantityas much as it does history. Weve definitely evolved from the pointwhere we hem and haw over a field, says Marty Solomon, businesssystems architect in CIGNAs health care space.

CIGNA has a multi-terabyte divisional data warehouse for itshealth-care business, hosted on an OS390 mainframe. The warehousehas been online for nine months and is fed by 22 different systems.CIGNA uses ETIs Extract for ETL and Brio for reporting. Solomonsays that in deciding how much historical data to warehouse, legaland regulatory requirements have been the primary driver.

History is an issue because the costs of data cleansing increasethe farther back insurers go. Typically, what most insurance CIOswant is several years of data in the warehouse, says Marx. Hereports that adding a shorter-term, operational datastore is oneway insurers can get more economically to the detailed informationthey need to support customer-facing functions such as callcenters. They may need to understand every premium, everydispersement on an annuity that took place in the last 90 days, forexample, he explains. When looking at the warehouse, what insurerswant are monthly buckets about what premiums were paid to handletrend analysis.

In deciding what data should be warehoused, Allstate developed astrategy that was designed to minimize current data extract issuesyet allow the most future flexibility. We did not know in buildingthe warehouse which data would or wouldnt add value, Hershbergerexplains. So we went into the source system and looked at thesegments that are used in the mainframe systems. Using Ab Initio,we took all of the data in the mainframe and dropped it into acollection area. We then went to our mainframe source system andasked for an evaluation of all the segments that were utilized on aregular basis, using the ETL tool to select only those portions wethought would have value. So if we need to go back and extractadditional information at a later time, we can do so moreeasily.

4. Plan for hidden costs. Realize that a successful warehouse,or even a successful intermittent deliverable, will generateinterest in the project among business users and get them thinkingabout new ways to analyze the information. And that, in turn, meansthe likelihood of changed and additional project requests.

Usually costs go up because the requirements get rearticulatedor expanded, says Solomon. For instance, an insurer might beginwith a request to analyze customer retention by region, but thenrealize the answer to that does not address why those policyholdersare leaving. Did my count drop because I closed down my main officein that region so I lost contact? Did those policyholders move? Didthey reach the age of 55? Was there insurance through an employerwho switched to another carrier?

Planning for the unknown is not only difficult, but alsopotentially expensive if the anticipated needs do not ultimatelymaterialize. Justifiably, the business doesnt want to spend upfront for what it wont use, so its a constant battle, Solomon says.You have to compromise between the visionary and the tactical, andhopefully end up with the strategic.

Theres no substitute for good planning and business/IT alignmentwhen it comes to managing this issue. We try to inform the users,and have been successful in educating them on subject areas and howthey work, says Solomon. Not necessarily to know the wholeacademics behind dimensional data modeling, but to give them anunderstanding of it so that when were building something, it allowseveryone to be more flexible and to handle the questions that comeup along the way.

5. Dont circle the wagons. Sticking to proprietary formats is atempting way to reduce costs, but consider the long-term savings ofstandardization. This is a time for companies to be looking outwardat whats going on from a technology standpoint, and you cant affordto come up with a proprietary solution to every problem.Additionally, the more we move toward data standards, the morevendors will be able to design products that can be easilyimplemented, says Gary Knoble, past president of the Insurance DataManagement Association and vice president of data management at TheHartford. With the crunch toward expense savings and lack ofresources, youve got to be looking for off-the-shelf solutions,even though you will still need to do work on them to implementthem.

6. Do it right the first time even if it costs more in the shortterm. Data quality is a continued impediment to quickimplementation of a data warehouse. However, failing to addressissues of data quality intelligently will serve only to increasethe ultimate cost of the warehouse.

Scrubbing techniques can be very sophisticated, but in its mostunsophisticated method, its nothing more than force coding, saysKnoble. There are certain data problems you can only find bylooking at the original values, rather than simply making the datapass a system edit. You often dont understand the consequences ofmanipulating data in a certain way because that data has to be useddownstream.

The salient question is, what do you do when you find bad data?Do you attempt to address it back in the provisioning system, or doyou put some scrubs in your warehouse? Hershberger asks, addingAllstate has opted for the latter strategy. We try to trap as manybad variables as possible. If something is clearly an error, wetake it out of the data warehouse during the ETL process andreplace it with a variable that indicates the source system valueis incorrect. If we have multiple sources of data, we make adecision as to which would dominate.

In short, economics should never trump accuracy. Ours is apragmatic approach, Hershberger says. I dont want to guess about acustomers variable attributes any more than I want to guess aboutproducers activity. If the data doesnt support a fact, then thefact that theres no data becomes the fact.

7. Get more mileage out of what you already have. Insurers havea wealth of information locked in legacy administration, ERP, andCRM systems, and need to leverage information across those systems.A data warehouse can be a way not only to link those systems but toefficiently use data captured from new front-end systems withoutincurring the cost in dollars and time to modify legacy systems toaccept new data formats.

Previously, system development efforts focused on reengineeringinsurance processes as they related to input and transactionprocessing. Now, the goals are output and decision support.However, many legacy systems were not designed to achieve thesegoals, says Mike Schroeck, partner at IBM Business ConsultingServices. Unfortunately, the cost of modifying those legacy systemsto use new data formats is significant. The better solution is touse the warehouse as a standardized repository and middleware toconvert the data to formats existing systems can use while stillretaining core data for business analytics.

8. Realize that sometimes economics are a secondary concern.There are instances when the most economical way to implement adata warehouse is incompatible with the regulatory requirementsinsurers must contend with. Cooperation and coordination betweenbusiness and IT is essential not only to increase the likelihood ofa projects success, but also to catch areas of data use or analysisthat arent allowed by ever-evolving security and privacyregulations.

For example, while customer data can be used within theorganization, insurers need to take care regarding how muchinformation can be shared with affiliates and subsidiaries in theircross-selling and upselling campaigns. Also, there are instanceswhen data elements may need to be discarded altogether, such aswith the prohibition against using social security numbers forcustomer identification, necessitating the creation of new customerID tables for some carriers.

And lastly, continued legislation addressing corporatecorruption, such as holding corporate executives personallyresponsible for financial reports, will likely be a driving factorin centralized data warehousing efforts. When signing off on thesefinancial reports, executives have a renewed interest andcommitment to ensuring they are accurate, consistent, and timely,says Schroeck. Data warehousing enables this, particularly wheninsurers are running several different financial systems.

Data Warehousing Vendor Guide

Net2S
New York, N.Y.
212-279-6565
www.net2s.com

Basis100
Toronto, Ont.
416-364-6085
www.basis100.com

CSC Financial Services
Austin, Tex.
310-615-0311
www.csc.com

Data Instrument Group
Mountain View, Calif.
408-516-8812
www.digdb.com

Decision Support Inc.
Matthews, N.C.
704-845-1000
www.decisionsupport.com

Delphi Technology, Inc.
Cambridge, Mass.
617-494-8361
www.delphi-tech.com

Evoke Software
San Francisco, Calif.
415-512-0300
www.evokesoft.com

FirstApex
Flower Mound, Tex.
866-700-2739
www.firstapex.com

Fiserv, Inc.
Brookfield, Wis.
262-879-5000
www.fiserv.com

Insight Decision Solutions, Inc.
Markham, Ont.
905-475-3282
www.insightdecision.com

lookNomore
Valley Stream, N.Y.
516-216-2311
www.looknomore.com

Millbrook, Inc.
Center Valley, Pa.
610-797-7400
www.millbrookinc.com

NCR/Teradata
Dayton, Ohio
937-445-5000
www.teradata.com

OuterBay
Campbell, Calif.
408-340-1200
www.outerbay.com

Priority Data Systems
Omaha, Neb.
800-228-9410
www.priority-quote.com

Risk Laboratories
Marietta, Ga.
678-784-4600
www.risklabs.com

Sagent Technology
Mountain View, Calif.
800-233-5478
www.sagent.com

Wipro Technologies
Richardson, Tex.
972-671-6130
www.wipro.com