What roadmap should I use for an insurance system data warehouse?


Sid Adelman's Answer: The roadmap for insurance is not that different than for other industries. Pick up Business Intelligence Roadmap by Larissa Moss and Shaku Atre. However, it is very important to understand the types of DW applications that are going on in the insurance industry. These are some of them:

Insurance Company 1: Established subject areas (i.e., claims, marketing)

  • Incorporate both internal and external data (i.e., information on competitors and the insurance industry trends).
  • Forecast and monitor changes in the industry, thereby allowing better positioning in the marketplace.
  • Identify characteristics of profitable business.
  • Analyze information related to retention of business at renewal including patterns of customers who do not renew, determine reasons why and resolve issues that will assist in retaining valued clients.

Insurance Company 2:

  • Accurate, consolidated view of customer portfolios.

Insurance Company 3:

  • Analysis of profitability by customer, product, geography and sales hierarchy.
  • Analysis of sales offices for profit and loss.

Insurance Company 4:

  • Analysis of quality of new business.
  • Premium uplift.
  • Catastrophe exposures - which customers are at risk.
  • Early-term claims by ZIP code.
  • Direct response analysis - customer responds to a coupon offer.
  • Survival modeling - how long a customer will stay with you.

Property and Liability Insurance:

  • Data mining review of claims by actuarial department.

Auto Insurance:

  • Analysis of valid VINs on insurance cards.

Workers' Compensation Insurance:

  • Recommend health insurance deductibles.
  • Analysis of claims by the employer, cause of injury, body part injured and the percentage of employees who have suffered similar injuries.
  • Fraud analysis using data mining.

Health Insurance:

  • Provider evaluation.
    • Physicians profiles.
    • Cost.
    • Length of hospital stay.
    • Procedure evaluations.
    • Provider abuse.
  • Impact on subscriber services and cost.
  • Employee costs per employer.
  • Service usage.
  • Fraud detection.
  • Actuarial analysis.
  • Patterns of insurance usage.

Health Insurer:

  • Fraud detection - Searching for claims where the service has not actually been provided. Looking for patterns that would suggest further inquiry into the claim.
  • Abuse detection - Searching for patterns indicating that certain providers are performing unnecessary procedures, prescribing expensive medication where a less expensive drug would be as effective, performing unnecessary tests and keeping a patient in the hospital longer than necessary.

Blue Cross:

  • Analysis of claims.
  • Providers analysis.
  • Reporting to groups, government agencies, trade associations.
  • Analysis of quality of care and costs.
  • Marketing managed-care contracts.
  • Actuarial.
  • Underwriting.
  • Financial analysis (actual expenses vs. planned expenses).
  • Profitability of manager care arrangements.
  • Capitated contract performance.
  • Fraud analysis based on the provider's healthcare specialty and the geography of the claim.

Pharmaceutical Insurance:

  • Sales and marketing.
  • Provider profiling.
  • Government reporting.
  • Utilization.
  • Claims.
  • Actuarial.
  • Integrating pharmaceutical information with medical claims.
  • Cost analysis by patient demographics and geographical distribution.
  • Cost analysis by provider, provider specialties and treatment protocols.
  • Analysis by diagnosis/prescription.
  • Generic/brand name drug comparisons.

Tom Haughey's Answer: I'm not sure that the roadmap for an insurance data warehouse would differ significantly from the roadmap for any warehouse. I offer first a general roadmap, as follows:

General Roadmap:


  • Parallel planning, design and startup.
  • Scope definition (define functionality over time).
  • Ends with install of live DB with base data.


  • User team tests and explores data, its usage and user interfaces.
  • ETL team tests content and quality.
  • Database team iteratively tests physical database.
  • Ends when time-boxed targets are reached.


  • Traditional production rollout.
  • User training and preparation.
  • Ends with install of new release.

Detailed Roadmap:

This can be viewed more broadly and in more detail as follows:

1.0 Establish the DW Program:

  • 1. Develop the business case.
  • 2. Define the opportunities.
  • 3. Define the DW architecture.
  • 4. Create the DW program.
    • Future vision.
    • Current environment assessment.
    • Gap analysis.
    • The migration plan.

2.0 Plan the Project:

  • 5. Identify the project tasks.
  • 6. Establish the infrastructure.
  • 7. Acquire the tools.
  • 8. Collect the team.

3.0 Initiate the Database:

  • 9. Gather user requirements.
  • 10. Create the logical data model.
  • 11. Identify the data sources.
  • 12. Create the physical model.
  • 13. Collect source data.
  • 14. Populate the database.

4.0 Prototype the System:

  • 15. Repeatedly test and revise the database design.
  • 16. Have users interact with the database.
  • 17. Tune design and add aggregates, indexes.
  • 18. Plan production increments and migration.

5.0 Implement the Release:

  • 19. Prepare for incremental release.
  • 20. Train the users (very, very important).
  • 21. Initiate support processes.
  • 22. Migrate to production.

6.0 Enhance the DW

  • 23. Manage the DW inventory of requirements.
  • 24. Coordinate with business needs.
  • 25. Market the warehouse and spread the word.
  • 26. Repeat.

Clay Rehm's Answer: By "roadmap" I assume you mean a methodology or process to build a new insurance data warehouse.

Because your subject area is insurance, you must have people on your team that are subject matter experts (SME) at insurance - and not only insurance, but also understand how your insurance processes work. Additionally, you need team members who don't understand insurance but understand how to build a data warehouse. These folks will ask the questions that may seem obvious, but they will challenge your current business processes.

Regarding the roadmap, there are many schools of thought (i.e., Kimball, Inmon, etc). The path you choose will be the one that makes the most sense for your organization, in terms of culture, size, talent, resources, etc. No matter what path you choose, it is wise to start with a small pilot to prove the need and benefit of the data warehouse. Choose something easy and small that can be done quickly with minimal resources. Make sure you involve your business partners from day one and keep them involved and possibly in charge of the project.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access