With the increasing popularity of data warehouses and data marts, the ability to refresh data in a timely fashion is more important than ever. Strategies of the past included completely rebuilding the data warehouse periodically to ensure that information used for reporting was current. As the data warehouse/data mart increases in complexity and the demand for more up-to-the-minute data increases, the possibility of maintaining the data warehouse in this fashion becomes intractable. One solution to this problem is to monitor the operational data sources, detect changes of interest and populate the data warehouse with the deltas. This article focuses on the several approaches for capturing changed data that are available today. The majority of the data in operational systems is transient. This is a manifestation of the needs of the business. At any one point in time, the operational system contains only the latest version of all records in the database. This is the view that is of interest to the operational side of the business. In some cases, an operational system may implement a form of semi-periodic history (e.g., the last five changes are preserved for each record). The problem with this technique is that it is impossible to guarantee that all changes will be preserved. For example, we may have seven changes for a record and two will be lost.

For operational systems where history is critical to the business operation (e.g., the insurance industry or for database marketing programs), all changes to records are preserved for a set period of time. Since there is no guarantee that changes will be captured for us on the operational system(s), we must utilize a technique to capture the transient and semi-periodic data. Once changes of interest are identified, they must be stored in a persistent area to ensure that these records are available for updating the data warehouse. The level of information stored should be rich enough to allow the transformation application to make update decisions accurately. Typically, this level of information would be an indication of the event or the full record before or after the change.

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