There's an insurance company CTO who is always happy to give mea few minutes of his time simply because it takes him away from thestaff meeting on a data warehouse project. "It's like watchingpaint dry," he said. And that summarizes what data warehousingprojects have going against them-long implementation times, littleinterest. Add to that a high failure rate-as high as 90 percentaccording to a 2000 report on data warehousing by Conning InsuranceResearch & Publications-and it's no wonder the data warehousehas long been the red-headed stepchild of most insurers' ITprojects.

But data warehousing projectscan improve the odds of success byusing technologies, tools, and lessons learned from those who havefailed and those who have succeeded. "There are better technologiesand approaches and there are more experienced people; there'sdefinitely some promise," said Jack Gohsler, Conning's senior vicepresident.

So what does an insurer need to do? Interestingly, most deal notwith issues of technology, but of personnel, management, andbusiness practice.

Never have a data warehouse project. Warehouses are dustybuildings filled with towers of file boxes. Warehouses don'tgenerate revenue. Building warehouses is watching paint dry,remember?

"You need to have an 'improve precision of forecasting' project,or an 'improve underwriting criteria' project," said PaulTheriault, senior vice president of marketing at PinpointSolutions. "What does that mean? It means a specific project thattargets a significant pain-point. It means realizing that we're notanalyzing losses at a detailed enough level to find thecharacteristics of winners and losers [and] what that's costing us.If pricing can be fine tuned across these factors, that will impactthe bottom line. And then you...get sponsorship by marketing,sales, actuarial, and then you have your warehouse."

In other words, think strategically. A successful datawarehousing project only starts with the creation of the warehouseitself. "It's about using your most basic asset to solve businessproblems," said David VanDenEynde, area vice president of insuranceand health care at NCR's Teradata division. "You need to start witha specific business objective and then decide how, or if, a datawarehouse can support the achievement of that objective. You can'tstart building a warehouse and then decide how you are going to useit. That's a hallmark of a company that's divided."

Communicate. Hardly a revolutionary concept, communication is akey to the success of most IT projects. But it's particularlyimportant in data warehousing. Unlike transactional systems inwhich users have no choice but to use the technology once it'simplemented, data warehouses must attract their user base. "If ITdevelops [the warehouse] without business involvement and throws itover the wall to the business users, it will fail," saidVanDenEynde.

Repeat after me: communication, communication, communication."The reasons projects fail most is because that isn't done," saidSid Adelman, data warehouse implementation consultant andinstructor at the Data Warehousing Institute. "That's communicatingexpectations of performance, response time, availability, the typeof function users will actually have, the ease of use of tools,timeliness of data, everything."
Involve and align the staff. Communication will go a long waytoward aligning business and IT, which is indispensable in awarehousing project. "In a data warehouse, you're buildingsomething for which the requirements aren't fully understood inadvance, so you need a much stronger partnership [between businessand IT]," said Gohsler.

Insurers who have forged that partnership have done so bybalancing centralized and decentralized control, creating jointsteering committees, and giving true decision-making authority toline-level staff while having a member of upper management championthe project. "The best model in the world is a dual project managerrole where they walk together in lock step," Gohsler said, "butultimately, there needs to be a sponsor-a well-placed, politicallysavvy person. Someone who has [control of] the money and will makedecisions quickly and mow down all the roadblocks that areencountered."

While there are good tools, know that there is no silver bullet.Data warehousing will not lower your IT costs; rather, it willincrease them through hardware, software, consulting, training, andongoing maintenance demands. It will not allow you to cut youranalyst staff. It will not fix bad business models, poor systemdesigns, or data problems. And there are generally no quicksolutions-experts are wary of middleware promising to pull datafrom disparate systems, and shrink-wrapped solutions oftendisappoint.

That said, the evolution of data warehousing has led totechnologies that can make your project more successful. As theprime example, vendors offer insurance-specific, prearchitecteddata models and automated data dictionaries. These differ fromfully packaged systems in that they are customizable to better meetthe needs of a specific installation.

"The best approach [to warehouse implementation] is between the'blank page and good tools' method and a fully shrink-wrappedsolution," said Bob McCarthy, director of product marketing forbusiness intelligence at Sybase. "We've developed [prearchitecteddata] models over time, but they are not fixed and rigid. They're astructure that's there to be extended, and we deliver them with amodeling and design tool that allows customers to add and modifytables as they implement them."

Ideally, tools insurers choose should be database-agnostic,capable of integrating with any database that an insurer ordepartment within an insurer may use.

Cost justify the warehouse. In this respect, the data warehouseis no different than any other IT project. But determining a costbenefit, let alone an ROI, can be difficult. "The value of gainingbetter access to data is nebulous, so it's difficult to get to anROI, and therefore some companies don't even try," said Gohsler."So when a CFO looks at a bill and asks, 'What are we getting forthis?' and there's no answer, the project is often cut. If there isa cost-benefit supporting the project, or at least a high levelconcept of benefits that has been shared with the businesscommunity, continued support for the effort is more likely."

Unlike a transactional system, where concrete increases in thenumbers of claims that are handled or policies that are issued canbe tracked and evaluated, data warehouse gains require anunderstanding of the strategic importance of the ultimate system.Look for productivity gains, assess marketing opportunities, andevaluate the results of precision pricing.

"Right now analysts...probably spend 80 percent of their timelooking for data and integrating data, but only 20 percent of thetime doing analysis," Adelman said. "We're trying to flip thosenumbers so we can point to some real productivity benefits. Butthere are other areas as well. If what you're trying to do isincrease your customer base, and each customer is worth $200, youcan attribute a tangible number to them."

Invest for the future. A well-designed warehouse will attractusers-you don't want poor performance turning them away. You'll bespending a lot of money on your system, so take some time to kickthe tires and look under the hood.

Start with a relational database management system (RDBMS) thatcan support not only scheduled analyses but also the ad hoc queriesusers will come to demand. "When you have someone with a70-million-policy data warehouse, and they want to look at everysingle policy on 20 dimensions, the general database will be adog," said Theriault, "but an ad hoc data engine will do it. That'sthe difference between seven hours run time and 30 seconds."

So plan for growth. "Am I buying a platform and a database thathas true scalability, so when I grow from 100 to 300 GB, I can getroughly the same response time?" asked VanDenEynde. "You don't wantto hit a magical number, in terms of gigabytes or number of users,where the system crashes or the response time is so bad no onewants to use it."

When it comes to performance promises, don't take the vendor'sword for it. "Use a database-independent query tool, talk to thedatabase vendors you have in mind, and tell them you want to do abakeoff," Theriault explained. "Once you do that, you'd knock out acouple contenders in the marketplace."

Commit to-and budget for-ongoing maintenance and development."In any other [IT] project, there's a beginning, a middle, and anend," VanDenEynde explained. "Business people might be involved,but IT people generally run the project. And when the project'sdone, everyone goes off onto other projects. And that's not what adata warehouse is about. A data warehouse is a dynamic, ongoingresource that, if done correctly, will continue to grow and providemeasurable business value to the enterprise."

Set a realistic schedule/be patient. How? By involvingtechnologists who know the requirements. "In one of the classes Iteach, I always ask the question, 'How many have schedules imposedon you?' And every hand goes up. Almost always these areunrealistic schedules," Adelman said.

An understanding of scheduling is critical for data warehousingbecause of the 80/20 rule: 80 percent of the work involved inimplementing a warehouse involves examination and preparation ofsource data, while only 20 percent is required to extract,transform, and load the data into the warehouse, according toConning's report. Without a clear understanding that no benefitwill be received until after the bulk of the work has been done,many insurers lose patience with the warehousing project, and oftenthe project is stopped before it is ever completed.

Why does preparation take so long? Because regardless of whatyou think, your data are dirty. Front-end edits don't catcheverything, and even if they do, creative data entry can get aroundthem. What's good enough to work for transaction processing oftenwon't work for data analysis.

So plan carefully. Test the data mart with sample data beforeyou load it. Build a small-scale data warehouse to vet the systemand uncover problems. In short, just because the warehouse won'tcause your policies to stop being issued or your Web site to crashdoesn't mean it's time to abandon those good ol' bestpractices.
Assign-or hire-staff with the right skill sets. Vendors' latestofferings have made it easier to manage the warehouse, but they arenot a panacea for lack of knowledge. "As the old saying goes: Atthe end of the day, a fool with a tool is still a fool," saidAdelman.

Your database administrator (DBA) is obviously critical,performing the heavy lifting of the project-design, monitoring,tuning, backup, recovery. Your data administrator handles datamodeling; the logical representation of the data; as well asmetadata, descriptions of the data itself.

"There's another key person, the 'ETL jockey'," Adelmansaid-someone responsible for extract, transform, and load. "Whetheryou're using an ETL tool or not, there's a lot of work involved,"he explained. Between IT and business, you also need an access andanalysis staffer who knows the business reporting tools, isinvolved in user training, monitors use of the tool, and makes surepeople are using it the way they should.

Start small, think big. Warehouse projects that offer quick,intermittent deliverables to key departments can help overcome the80/20 scheduling problem. "Companies get too bogged down...wantingto solve world hunger in the first phase, making it such a hugeproject that it takes 12 or 18 months. Business folks are waitingfor the first sample of data," said VanDenEynde. "Coming back witha small- to medium-sized win in six months is critical. If youbuild correctly, a small win does not preclude you from a largerwin down the road. At the same time, you're returning value,getting business folks interested, getting positive chatter aroundthe warehouse."

That strategy works as long as the early deliverable doesn'tturn the warehouse into a pet project. "Say we're developing anapplication for underwriting; you might be tempted to get somethingup quickly that doesn't consider claims," VanDenEynde continues."You might say, 'Why do we want to architect a database to includeclaims?'[But you need to] plan for the whole house even if you onlyput furniture in a couple of rooms."
Also, to combat long deliverable times, insurers frequently deploydata marts to meet point objectives such as a quick claims analysisor targeted marketing program, with the intention of laterconnecting those marts into a larger, federated data mall. Withouta forward-looking plan, however, insurers can be left instead withindependent systems that are redundant, or worse, incompatible.

"The data mart is not good to the extent that [it] presents abuy-down, but-to the extent that the data mart is an intermediatepoint to a more global view of customer data, premium data, and soon-it can work well," Gohsler said. The key is to define a singleconceptual data model of products and customers across all the datamarts. That's no small task when dealing with independentdepartments, each potentially with its own IT budget, customizedsystems, and equally empowered directors. It points again to theneed of a highly placed data warehouse sponsor.

The best data mart strategy is to first build the largerwarehouse, and then build dependent data marts that attach to thecentralized repository. In that way, common data definitionsbetween data marts aren't an issue. An ancillary benefit is thatindividual departments can perform heavier analytical tasks usingtheir own systems without affecting the response time.

Keep your eyes on the road ahead. Analysts and actuaries whodream of pulling years of historical data will be disappointedbecause the cleansing requirements are simply too stringent.Business acquisitions and mergers, code changes, and legacy systemmigrations have all resulted in what Conning calls a"conglomeration" of data at many insurers: inconsistent, poorlydocumented, and error prone.
"Some of the data you use today weren't even captured five yearsago," said VanDenEynde. "So the easiest way to do it is to startright now-to capture data today."

If you absolutely, positively, have to have a longer historicalview, minimize problems by focusing on analysis priorities. What'sa reasonable mark for pulling historical data into the warehouse?Two years is the point of some agreement, although no rigid rulesapply. "If you're trying to analyze loss trends over several years,you need several years worth of history, but if you're a salesperson...you're more interested in what you sold last year,"VanDenEynde said. "Let the...associated business value dictate thespecifics of your data warehouse."

RDBMS
IBM: DB2 (www.ibm.com)
NCR: Teradata Warehouse (www.ncr.com)
Oracle: Oracle9i (www.oracle.com)
Sybase: IQ Multiplex (www.sybase.com)

Modeling
Computer Associates: Erwin (www.cai.com)
Oracle: Designer (www.oracle.com)
Sybase: Industry Warehouse Studio (www.sybase.com)

ETL
Ascential Software: Datastage (www.ascentialsoftware.com)

Evolutionary Technologies International: ETI Extract (www.eti.com)
Informatica: Informatica Applications (www.informatica.com)

Query and Reporting
Brio: Brio.Report (www.brio.com)
Business Objects: InfoView (www.businessobjects.com)
Cognos: Cognos Query (www.cognos.com)

Insurance Analysis Suites
Insight Decision Solutions: Insight Warehouse (www.insightdecision.com)
Pinpoint Solutions: ProfitCube (www.pinpnt.com)
Thazar Solutions Corporation: InsSight Business Intelligence Suite(www.thazar.com)

References and Resources
Conning (www.conning.com)
DataWarehouse.com (www.datawarehouse.com)
The Data Warehousing Institute (www.dw-institute.com)
Sid Adelman & Associates (www.sideadelman.com)