Managing Time in Relational Databases
Time present and time past
Are both perhaps present in time future,
And time future contained in time past.
T. S. Elliot, Burnt Norton
Joe Celko says:
“You cannot escape temporal data. You need to get over it, sit down and read what Tom and Randy are telling you in this book.”
"Tom is the ….. only person doing useful work with temporal data. He is one of the people I think is smart.”
Richard Snodgrass, Professor of Computer Science at the University of Arizona, says:
Asserted Versioning is “…..a significant advance in the theory and practice of managing time-varying data.”
Mitch Yelton, Senior Manager at Wipro Consulting, says:
"Tom Johnston is quite simply the best Data Architect I have ever worked with. His ability of understanding business requirements, data integration and OLAP modeling is world class. ..... He can act as a business analyst, data analyst, data modeler and data solution architect all in the same engagement, and do all roles to the same level of expertise – exceptional! " .
Bitemporal data management is becoming mainstream. In late 2010, IBM announced support for bitemporal data in their DB2 10 z/OS DBMS. Around the same time, Teradata announced similar support in their Teradata 13.10 DBMS. Oracle's 11g DBMS, with its Workspace Manager component, also supports bitemporal data.
Our company, Asserted Versioning, LLC, offers support for bitemporal data in the form of a middleware product called the Asserted Versioning Framework, (AVF). Unlike vendor-specific solutions, it is an enterprise solution that provides a consistent and queryable implementation across databases managed by different DBMSs. Unlike vendor solutions, the AVF supports an extension of transaction time (which we call assertion time) so that data can be created and managed in future assertion time.
The AVF recently participated in a Proof of Concept for a major international bank. The product performed well, and demonstrated its extended bitemporal functionality for managing future assertions. During this Proof of Concept, we and the client developed a deep understanding of the relevance of future assertion time for the management of macroeconomic data.
Using the AVF, temporal requirements do not have to be expressed in data models. Using the AVF, no procedural logic is needed in application programs in order to maintain temporal data. Using the AVF, all temporal data is contained in production tables, and none of it is scattered around various other physical or logical datasets.
Read on for more information about how Asserted Versioning simplifies the management of temporal data.
Our book, Managing Time in Relational Databases (Morgan-Kaufmann, 2010, ISBN 978-0-12-375041-9), describes both the standard bitemporality beginning to be implemented by DBMS vendors, and also an early version of our own extended functionality supporting deferred transactions and future assertions. This book may be purchased from Morgan Kaufmann Publishers online at MorganKaufmann
Enter discount code 29131 at checkout to save 20%.
Dr. Tom Johnston spoke at the EDW 2011 Conference in Chicago this year. His presentation was on Understanding Bitemporal Data.
Tom is also available as a consultant on data management projects that involve the management of temporal data. He can be reached at tom.johnston@assertedversioning.com.
For several decades, Tom Johnston has been an independent consultant specializing in enterprise data architecture, and in relational, object-oriented, OLAP and data warehouse modeling in various industries, including telecommunications, health care, banking, retailing and transportation.
During this time, as a data modeler and data architect, he designed and developed history tables and version tables to support the temporal data requirements of his clients. For the last several years, he immersed himself in the extensive work done by computer scientists on bitemporal data. Then, last year, he co-authored a book on bitemporal data which combines the best of that work with his background in real-world IT databases.
Tom has a PhD in Philosophy, with a concentration in logic, semantics and ontology. He spoke at the Semantic Technology 2008 Conference on using ontologies to manage data dictionaries.
He can be reached at TomJohnston
and his personal website is MindfulData
Randall Weis, founder and CEO of InBase, Inc, has more than 24 years of experience in IT and IT management, specializing in enterprise data architecture. Weis' technical expertise is in sophisticated, multitiered systems. He has designed logical and physical data models and implemented several high profile, very large database (VLDB) systems in the financial and insurance industries. These systems have had very stringent performance and real-time history requirements. His software development company, InBase, Inc., has developed software and Web sites used by some of the nations largest companies. Weis has been a presenter at various user groups, including Guide, Share, Midwest Database Users Group and Camp IT Expo. His technique for modeling history, retro activity and future dating has been reviewed and approved for the physical implementation of IBM's Insurance Application Architecture (IAA). He may be reached via email at randyw@inbaseinc.com..
