3567 views since 2012-08-05 Page last modified on 2021-05-31.
  XHTML RSS QR Commons
Valid XHTML 1.0 Transitional RSS Logo and feed page link Hist-Op URL in QR encoded form Creative Commons licensing

tem·po·ral adj \ˈtem-p(ə-)rəl\
1. Of, relating to, or limited by time: a temporal dimension; temporal and spatial boundaries.

These two wonderfully scientific sounding terms, “bi-temporal” and “2-dimensional”, merely mean that they have something to do with time. All three of the following tables have temporal components and depict the same basic information, with more or less detail. In this case Elmer Fudd from NH made his first purchase on 2007-01-01 and his data was entered into the system on 2007-01-10. He then moved to MA on 2008-08-05 but didn't notify the change in address until 2009-03-22.

Table with a temporal column
Cust Name State Valid
1 Elmer Fudd MA 2008-08-05
1-dimensional table
Cust Name State ValidFrom ValidTo
1 Elmer Fudd NH 2007-01-01 2008-08-04
1 Elmer Fudd MA 2008-08-05 9999-12-31
2-dimensional table
Cust Name State ValidFrom ValidTo TechFrom TechTo
1 Elmer Fudd NH 2007-01-01 2008-08-04 2007-01-10 12:00:00 2009-03-22 17:59:59
1 Elmer Fudd MA 2008-08-05 9999-12-31 2009-03-22 18:00:00 9999-12-31 23:59:59

The first table has just one column for temporal data, it contains the date on which the change was made. Since there is no way of storing historical data, the address change on 2008-08-05 overwrites the previous contents.

The second table has two temporal columns storing the beginning and end points in time where the record is valid. This is referred to in data warehousing circles as a “Type II SCD (Slowly Changing Dimension)”. This is a very common storage method for information as it allows historical information about data to be stored in the same table. In this case the address change from NH to MA is portrayed correctly.

The third table is a bi-temporal or 2-dimensional one. It has 2 time axes, as with the Type II SCD it has a range depicting the valid begin and end dates; but in addition it has two more columns that store the information about when the data changes occurred. The time lag between the data validity beginning date (2008-08-05) and when it was actually entered (2009-03-22) is shown.

The additional accuracy between the 1- and 2-dimensional tables is evident when considering the query "How many customers did we have in MA on 2008-10-01?". If this query is made before 2009-03-22 then the answer would be 0 in both dimensional tables. But if that query were performed on 2009-04-01 (after the change of address is entered into the table) then the answer would be 1. If an analyst compares the two queries and notices the discrepancy there is no way of determining what has changed in the case of a 1-dimensional table, since the ValidFrom date refers to the address change date and not the date upon which the data became effective in the database. In the 2-dimensional table this information is stored, and each query needs to contain 2 points in time - the date for the data validity and the date that the data was effective in the database, a snapshot of the database contents at a specific point in time.