Ask the Experts
Temporal processing delivers past, present and future insight.
One of the most exciting innovations in Teradata 13.10 is the option to incorporate temporal processing capabilities into the data warehouse. This ability to analyze the time dimension of data gives organizations a clearer picture of data trends and changes, enabling them to be more agile and competitive. It also represents a turning point in data warehouse development.
Teradata Magazine caught up with Ramesh Bhashyam, Teradata Engineering Fellow and architect of temporal capabilities for Teradata, to find out more.
What is a temporal database and how does it differ from a traditional database?
A temporal database provides the ability to capture the evolution of information over time and use that information for competitive insight. It provides the ability to reason with a time-ordered sequence of data transformations.
A traditional database merely captures the current state of the data. It does not capture the evolution of data or enable its analysis based on how data relates to other data over time. For example, a product may move from one inventory category to another in April. A query in November on the sales of that category one year ago would yield misleading results because the system would not know that the category makeup has changed.
"The Teradata solution makes temporal processing transparent and performs it in the database. This improves data integrity and reduces application complexity."
Teradata Engineering Fellow
Typically, data analysis has been limited to how two data elements relate to each other, such as projected revenue change based on product sales assumptions. Time-based analysis was limited to comparing events that occur at different points in time, like sales today versus sales a year ago. Systems that enable such analysis are called decision support systems (DSSs). DSSs have been limited to understanding the relationship between data values that represent the current state of the data.
Then there is active data warehousing, which allows you to do such analysis in a timely fashion across more data areas.
Temporal is the next step in analytic capability. It analyzes how a data element evolves or how it relates to other data over time—for example, which products were in a category last year, which are in that category this year and how that change will affect sales. This argues for a much deeper understanding of the time dimension.
How is Teradata’s temporal implementation unique?
Other vendors offer some temporal support, but it mostly relates to TransactionTime, the date the transaction was entered into the database. That is sufficient for reporting purposes, but it isn’t well suited for deep analytics. In the case of, say, insurance policies, TransactionTime alone doesn’t tell you when a policy actually went into effect. That is known as ValidTime, and it may have a different value from TransactionTime.
Teradata’s temporal implementation offers a complete, bitemporal solution that makes use of both time periods. It knows when the data was actually true (ValidTime) as well as when the data was entered into the database (TransactionTime). With the insurance example, the database would know that a claim for November 7 is payable because the policy was valid on November 1 even though it wasn’t entered into the system until November 15. It would also know that future claims are payable if they fall within the policy time period of, say, 365 days.
Click to enlarge
Our solution can also handle variations that would result in multiple versions of a row, each with its own ValidTime value, such as short-term changes in an insurance policy. [See tables.] We’ve implemented a period data type that allows you to represent periods or durations of time rather than points in time. It allows you to compare and manipulate durations directly in the database instead of through user SQL. Both TransactionTime and ValidTime have a period duration and are represented using the period data type.
Academic experts such as Richard T. Snodgrass, University of Arizona; Christian Jensen, University of Denmark-Aalborg; and Michael Bohlen, University of Zurich, laid the foundation for temporal databases. We have followed their research closely and have leveraged our understanding of DSSs and active data warehousing to create a tailored temporal implementation that is truly best in class and exceeds current offerings in the market.
How do you analyze ValidTime and TransactionTime in a temporal database?
It’s not that different from the analysis capabilities available within a traditional database. You don’t really create a “temporal table.” Instead, you create a regular table and include period columns to hold ValidTime and/or TransactionTime data. True bitemporal processing requires both columns.
Teradata understands the meaning and implication of each of these column types and automatically knows what to do when a data element is updated or how to query based on the time of interest to the user. SQL statements can use prefixes for ValidTime, TransactionTime or both to return very specific results. It’s a logical extension of how you currently define and manipulate a table in Teradata.
What specific capabilities does the Teradata temporal solution deliver that homegrown temporal solutions don’t offer?
There are many unique features, but two that our customers will find interesting are constraints that ensure data quality and query optimization techniques that enhance performance.
Custom implementations use modification statements and complex SQL queries on date columns. The SQL, not the database, embodies the semantics of time, such as the knowledge that time durations are continuous and that the data element represented in the row is valid at every point in that time duration. Furthermore, user applications must perform the data evolution and ensure that all information at every point in time is recorded in the database.
The Teradata solution makes temporal processing transparent and performs it in the database. This improves data integrity and reduces application complexity. Also, enforcing time-aware constraints in the database improves data quality, such as a business rule that requires that there can be only one owner of a property at any time.
Temporal query optimization is another critical area. A temporal database contains more history rows than a traditional database. There is more data to consider when running queries and updating tables. Query optimization becomes essential because queries that perform time-based analysis are naturally more complex.
"Temporal is the next step in analytic capability. It analyzes how a data element evolves or how it relates to other data over time. … This argues for a much deeper understanding of the time dimension."
Teradata Engineering Fellow
Teradata’s temporal solution provides query optimization capa-bilities such as inner-join elimination, outer-join elimination, outer to inner join conversion and partitioned primary index. In fact, all existing Teradata optimization techniques are made available for temporal queries. Query optimization on temporal tables can make the difference between a query that completes and one that runs forever.
What types of companies would benefit from the Teradata temporal solution?
There are those customers who have not implemented any temporal applications at all; to them we are saying, “Go temporal with Teradata,” because there’s a direct business benefit, which is greater insight into past, present and future customer behavior.
There are also those customers who have tried to create time columns and time durations, and they have tried to do temporal analysis through their own implementations. For them, the Teradata solution offers an easier, more efficient and much more effective way to gain insights available through temporal processing.
For both groups, we have made the transition to temporal as smooth as possible, without the need for complex SQL statements or new table types. The intent is for existing applications to continue to work with temporal tables. If you have a non-temporal table, you can make it temporal and still run all of your applications. We are minimizing the training required and are making sure that existing application investments are protected.
So it’s a seamless transition?
Maybe not completely seamless, but we have definitely minimized the impact of implementing temporal processing by keeping things as simple as possible and making sure the results are well worth any effort required by the customer.