In the insurance industry there are a lot of calculations that could be performed on the premium amounts. Most of the calculations have to do with the term premium, effective date and expiration date. If we store the value in the warehouse, it could be easily moved or used in another calculation. How do we determine whether or not a calculated field should be stored in the warehouse versus a mart or in a report?


Les Barbusinski’s Answer: Precalculate and store a metric if:

  • It is complex, or
  • Frequently used (by itself or in other calculations), and/or
  • Needs to be aggregated

Calculate the metric on the fly if:

  • It is simple, or
  • Infrequently used (by itself or in other calculations), or
  • Cannot be aggregated (e.g., variable price)

Hope this helps.

Steve Hoberman’s Answer: The two main advantages of storing the calculated fields in the warehouse are efficiency and consistency. Efficiency meaning as you pointed out it can be calculated once and used many times. Consistency meaning that by storing it in the data warehouse you don’t need to be concerned about marts, reports and users calculating the same field different ways. You need to weigh these two advantages with the extra space and processing required to maintain these derived fields. If the fields are derived once and never updated and disk space is not a concern, store it in the warehouse. If however, space is a concern and these derived fields are continuously getting updated and killing performance when loading the warehouse data, then derive the fields on the fly or consider using some form of materialized view or snapshot. I will be having an upcoming Design Challenge on this topic, and if you are interesting you can join our discussion group at www.stevehoberman.com by clicking on design challenge.

Chuck Kelley’s Answer: Since you wondered whether or not the fields should be stored in the data warehouse versus a data mart or report, I will assume that you have a "central" data warehouse and use data marts for the user community to query. As a general rule, I store as many of the calculated fields in the data warehouse as possible. The general rule follows the theory that calculating once is better than calculating every time. However, if the calculation changes often, I would probably create an object in my ETL tool that defines the calculation and then use that object when populating the data mart.

Clay Rehm’s Answer: You may want to talk to your users of the system to determine how they would use it. My vote is to store the calculation in the data warehouse so you have it available to all, and there is meta data for it.

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