We are in the process of building an insurance data warehouse. Some of the people involved in this project are thinking about using the data warehouse data to calculate the commission amounts for agents. Aside from the fact that a data warehouse is built for analytics, what are the reasons to avoid running operational systems off the data warehouse data?


Joe Oates’ Answer: This is certainly a possibility. However, the data warehouse would need to have the ability of an accounting system to reversing entries of sales and other transactions that affect sales commissions, along with the reason for the reversing entries. If your company has multiple systems, each handling a different insurance business line and each calculating commissions for the same agents, having everything in one place might be an advantage. Before committing to do this, get some knowledgeable users and technical people together and brainstorm in a "what could go wrong" session. For each possible problem, assess whether the impact is high (show stopper), medium (pretty risky) or low (not much impact). Also, for each possible problem, assess the likelihood of the problem happening (high, medium, low). For each combination work out a contingency plan to prevent these problems from actually happening.

Clay Rehm’s Answer: I have developed such applications for insurance companies. However, this type of application requires an operational data store (ODS) instead of a data warehouse. It kind of looks and feels like a data warehouse, but it is not.

The reason for not running operational systems off the data warehouse is the fact that the data warehouse is meant for strategic decisions, not day to day running of the business. It is meant for a different audience and a different reason.

I suggest you build an ODS that extracts data from the OLTP source system (s) and make it easy to use (similar to a DW). The data design may be more normalized than a DW but less normalized than the OLTP systems – a hybrid. It will need to keep the most granular level of detail and should keep current data for performance reasons. Once this in place, move the history to the data warehouse.

For assistance on building an ODS, please refer to the book, Building the Operational Data Store, by Inmon, Imhoff and Battas (ISBN 0-471-12822-8). I also recommend that you retain an experienced consultant who has developed successful ODSs for other insurance companies.

Les Barbusinski’s Answer: Data warehouses aren’t built just for analytics. They are repositories of cleansed, integrated data (both current and historical) that can be used for a variety of purposes. For example, they can provide historical system of record information for legal, regulatory or compliance purposes. They can also provide cleansed data back feeds (such as Customer address information) to operational systems or historical data to what-if applications such as budgeting systems, etc.

Although I would agree with you that calculating agent commissions isn’t usually done in a data warehouse, there may be special circumstances in which the data warehouse can provide information that is otherwise unavailable to the agent compensation system. For example, some special commissions may be tied to year-to-date sales of the agent’s office, district or region. Others may be dependent on special criteria specific to a particular sales campaign.

I’m not sure what the particular circumstances are in your situation, but you may want to consider using your data warehouse assets where it makes sense to do so.

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