Portals eNewsletters Web Seminars dataWarehouse.com DM Review Magazine
DM Review | Covering Business Intelligence, Integration & Analytics
   Covering Business Intelligence, Integration & Analytics Advanced Search

View all Portals

Scheduled Events

White Paper Library
Research Papers

View Job Listings
Post a job


DM Review Home
Current Magazine Issue
Magazine Archives
Online Columnists
Ask the Experts
Industry News
Search DM Review

Buyer's Guide
Industry Events Calendar
Monthly Product Guides
Software Demo Lab
Vendor Listings

About Us
Press Releases
Advertising/Media Kit
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

Is there any database management system which supports temporal databases?

    Ask The Experts published in DMReview.com
January 3, 2006
  By Tom Haughey and Chuck Kelley and Joe Oates


Is there any database management system (DBMS) which supports temporal databases?


Chuck Kelley's Answer: None that I am aware. However, there are lots of research papers and articles that discuss how to implement temporal within relational databases.

Tom Haughey's Answer: I am not completely versed on the new vendor products in this area but here is my general thinking. Currently, there are two vendor approaches to temporal databases: build a new DBMS or extend an existing DBMS. In terms of extending an existing DBMS, there are two approaches: extend a relational DBMS (RDBMS) or extend an object oriented DBMS (ODBMS). RDBMS are very oriented toward processing high volume, short-lived transactions, such as you see in financial transactions, whereas ODBMSs are oriented more toward complex objects and long-lived transactions, such as you see in GIS and spatio-temporal applications. The ODBMSs in my opinion are not capable of handling large volumes of transactions such as you seen in order processing or financial processing, or large volumes of queries (involving huge amounts of data) such as you see in data warehousing. That is the domain of the RDBMS. On the other hand, because the cost to build a new DBMS is huge, in the short-term I would look to existing DBMSs and to temporal extensions in the DBMS and SQL that existing major vendors have to offer. Each of the major vendors (IBM, Oracle, Sybase, etc.) have some offerings in these areas.

The design of temporal databases using conventional DBMSs is becoming more and more prominent. Even though technical features to support temporal data already exist in many products, it is my experience that they are not frequently used in mission-critical applications. Typically, time is handled directly in the design in one of several ways: by qualifying existing tables with time or by setting up separate tables to collect history. Qualifying existing tables means not just adding time but making time part of the natural key (whether you use a surrogate key physically is another matter). Setting up separate tables for time can be done in both OLTP and in data warehousing. Developers then handle temporal operations through SQL or a BI query tool. A true temporal DBMS has time as an inherent part of the architecture of the product and supports a fully temporal version of SQL. See the writings of Richard T. Snodgrass and Chris Date for more details on these. Snodgrass has been writing on this subject for years. Fasten your seat belt before you read them (especially Date) because it is not bedtime reading.

Given that most data warehouses, data marts and other BI applications are temporal in nature, one would have predicted a much greater use of advanced DBMS capabilities today. There are several reasons that temporal DBMS extensions haven't been used more widely. One is that it is not always a safe bet to be on the leading edge of such technologies due to the risk involved. Second, we have established practices for handling time/audit in existing OLTP applications and for dimensioning time in data warehouses. In addition, both RDBMS and multidimensional DBMSs (MDDBMS) can handle data warehouse history needs very well. In these, time becomes one of the dominant dimensions, even in the form of one or more calendars. Facts are inherently historical. As new facts happen, including changes to existing facts, new fact rows are entered. The time dimension, and time varying dimensions, works well for regularly scheduled data loads in which the data warehouse is refreshed daily, weekly or even monthly. It can even be used to support rolling summaries in the warehouse. Recently, there is a trend toward near real-time warehousing and to operational data stores. In these, feeds occur at very short intervals or even nearly synchronous with operational transactions and are often done through message brokers rather than standard ETL. In these situations, there might be a need to keep some amount of history where updates are volatile and unpredictable, which cannot readily be represented using a dimension table. This will be the specific domain of temporal databases, when they fully arrive.

One last point. Time-series data is another temporal subject. History is usually considered as the random occurrence of heterogeneous data and events, such as you see in customer changes, customer orders and order changes, etc. A time-series, on the other hand, is the regular collection of specific attributes at a regular interval. Though the data warehouse is sometimes called a "time-series," in reality is it partly a time series and partly history. Aggregates and periodic statuses, both important grains, represent time-series, such as customer by product by month, or policy monthly status. The changes to an order or to a customer are more historical; they can happen in any way at any time. Look at it this way. Every day every stock on the NYSE has a closing price, but you don't have to buy any stock on any given day. Closing prices are a time-series; stock trading is historical. Some existing DBMSs offer time-series extensions, plus there are specific time-series DBMSs. I believe that it is safer to consider temporal DBMSs and time-series DBMS as niche DBMSs, used for specific purposes, and to continue to use existing robust DBMSs and their extensions (together with established database design techniques) for mainstream applications.

Joe Oates' Answer: A temporal DBMS is one that comes with built-in time aspects, e.g., a temporal data model and a temporal version of structured query language. The goal of a temporal DBMS is to provide temporal aspects that usually include the concept of the time period in which a fact is true (valid-time) as well as the time period when the transaction was actually recorded and believed to be true in the database (transaction-time). These attributes are combined in a temporal DBMS to provide the ability to record bitemporal data which is the combination of both of valid-time and transaction-time.

I don't know of any commercially popular relational DBMS that supports the temporal aspects of a structured query language (e.g., "overlaps," etc.). The above concepts, while very important to BI analytics, were not important to transactional processing for which most popular DBMS systems were designed.

However, a properly designed BI database can simulate temporal DBMS features very well. The only caveat is that "normal" SQL statements must be used.

For example, consider if John Smith was a product manager for widgets from October 1, 2002, until December 31, 2004. Let's also say that the fact that John Smith started being the widget product manager was entered on October 3, 2002, with the "start date" recorded as October 1, 2002, and the "end date" with no entry because at the time that John Smith was appointed, no one knew how long he would be the product manager.

Let's also say that when John Smith became product manager for widgets the price was $12 per widget, and this was true until November 1, 2004. So, the original record for Widgets having the price of $12 had the "start date" recorded as July 1, 2001, which was entered on July 5, 2001, and the "end date" had no entry.

When the price was changed from $12 per widget to $13, it became necessary to update the above record by entering October 31, 2004, in the "end date." This update was recorded on November 7, 2004. Additionally, a new record was added for widgets that contained the new price of $13, the "start date" for this price was November 1, 2004, the "end date" had no entry, and the new record was entered on November 8, 2004.

On January 1, 2005, someone else took over as widget product manager from John Smith. Therefore, the record that recorded John Smith being the product manager for widgets will now have to be updated with a valid "end date" and the date on which the record was updated.

This simple example allows queries to be written that can tell us, among other things:

  • When John Smith was the widget product manager and how long he held this position;
  • That John Smith was the widget product manager when widgets were $12 (his time being the widget product manager overlapped the time in which widgets were $12);
  • That anyone querying the database on November 3, 2004, would not have known that the price for Widgets had been increased to $13.


For more information on related topics visit the following related portals...

Tom Haughey is the president of InfoModel LLC, a training and consulting company specializing in data warehousing and data management. He has worked on dozens of database and data warehouse projects for more than two decades. Haughey was former CTO for Pepsi Bottling Group and director of enterprise data warehousing for PepsiCo. He may be reached at (201) 337-9094 or via e-mail at tom.haughey@InfoModelUSA.com.

Chuck Kelley is a senior architect in the business intelligence practice for Hitachi Consulting (www.HitachiConsulting.com), a globally recognized leader in delivering value-based business and IT Solutions. Kelley is an internationally known expert in database and data warehousing technology. He has 30 years of experience in designing and implementing operational/production systems and data warehouses. Kelley has worked in some facet of the design and implementation phase of more than 50 data warehouses and data marts. He also teaches seminars, co-authored three books on data warehousing and has been published in many trade magazines on database technology, data warehousing and enterprise data strategies. He can be contacted at chuckkelley@hitachiconsulting.com.

Joe Oates is an internationally known speaker, author and consultant on data warehousing. Oates has more than 30 years of experience in the successful management and technical development of business, real-time and data warehouse applications for industry and government clients. He has designed or helped design and implement more than 30 successful data warehouse projects. He can be reached at joates_48323@yahoo.com.

Solutions Marketplace
Provided by IndustryBrains

Data Validation Tools: FREE Trial
Protect against fraud, waste and excess marketing costs by cleaning your customer database of inaccurate, incomplete or undeliverable addresses. Add on phone check, name parsing and geo-coding as needed. FREE trial of Data Quality dev tools here.

Speed Databases 2500% - World's Fastest Storage
Faster databases support more concurrent users and handle more simultaneous transactions. Register for FREE whitepaper, Increase Application Performance With Solid State Disk. Texas Memory Systems - makers of the World's Fastest Storage

Manage Data Center from Virtually Anywhere!
Learn how SecureLinx remote IT management products can quickly and easily give you the ability to securely manage data center equipment (servers, switches, routers, telecom equipment) from anywhere, at any time... even if the network is down.

Design Databases with ER/Studio: Free Trial
ER/Studio delivers next-generation data modeling. Multiple, distinct physical models based on a single logical model give you the tools you need to manage complex database environments and critical metadata in an intuitive user interface.

Free EII Buyer's Guide
Understand EII - Trends. Tech. Apps. Calculate ROI. Download Now.

Click here to advertise in this space

E-mail This Ask The Experts E-Mail This Ask The Experts
Printer Friendly Version Printer-Friendly Version
Related Content Related Content
Request Reprints Request Reprints
Site Map Terms of Use Privacy Policy
SourceMedia (c) 2006 DM Review and SourceMedia, Inc. All rights reserved.
SourceMedia is an Investcorp company.
Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.