Implementing Teradata Temporal in a Physical Data Model using ERwin

There is no question that a database-supported temporal implementation gives Teradata users a powerful tool, because the time dimension has been added to data management and query processing. For the business user, the ability to ask more sophisticated time-based questions from their data warehouse, and receive more insightful answers, can yield a distinct competitive advantage. For the physical modeler, however, adding the constructs required for a temporal implementation can be a significant and tedious task, particularly when dealing with models of significant size – like Teradata’s Industry Data Models (iDMs). One of the biggest hurdles to overcome is the lack of support for Teradata temporal in current data modeling tools such as ERwin. Without automated support in the tool, all temporal columns and syntax additions must be added either manually as columns to each table or via custom post-scripts, each of which can be complicated by syntax changes between the various versions of Teradata and ANSI temporal.

Realizing that many iDM owners will be faced with the task of manually implementing temporal on top of an industry model, Teradata has developed a set of collateral, The Temporal Toolkit for Industry Data Models, which fully automates the temporal implementation process using data modeling tools such as ERwin Data Modeler (v9.64). Utilizing a customized forward engineering template (FET) and a set of user-defined properties (UDP) that can be added to any data model, we’ve not only overcome ERwin’s lack of support for Teradata temporal but also provided a quick and efficient way of implementing and managing temporal warehouse structures – even when they span multiple versions of temporal.

Given that most data models contain time-based components built into them, it is necessary to first review and prepare a model for temporal implementation. The toolkit contains a guide to help modelers identify and categorize the time-based components already in the model, defining specific actions to be taken with attributes that parallel the Valid Time (VT) or Transaction Time (TT) components to be implemented using temporal.

Once a model has been prepared, a series of UDP’s are then added to define the temporal options. At the model level we specified the overriding syntax being used (the original introduced in Teradata 13.10, the updated Teradata 14.10 syntax, or ANSI), default column names and data types for the VT and TT additions to each table, and the default PK type for valid time tables (Sequenced, Nonsequenced or Current). At the table level we specify whether or not the table will be implemented temporally, and if so what level of temporal implementation it will have – VT, TT or both (bi-temporal). Additionally, we allow the user to override the model level option for syntax and PK style. Utilizing the bulk editor in ERwin, it’s possible to set these table UDP’s en masse by importing them from an Excel .csv file, removing the need to manually edit every table – a huge time saver. Finally, at the column-level, we allow for the use of existing columns as substitutes for temporal additions. In other words, already modeled Start and End dates can be flagged as Valid Time Begin and Valid Time End if desired, which can greatly simplify implementing temporal into a pre-existing warehouse structure.

Once all UDP’s have been set, simply point to the customized temporal FET file in the ERwin Forward Engineering function, and syntactically correct temporal DDL can be produced – including the insertion of any derived PERIOD columns required.

Current Industry Data Model (iDM) product subscribers already have access to all toolkit collateral. For iDM customers without a subscription, or for implementations not involving an iDM, the toolkit can be made available as a resource for Teradata Professional Services personnel working with a Teradata client.

JakeKurdsjukBioJake Kurdsjuk is Product Manager for the Teradata Communications Industry Data Model, purchased by more than one hundred Communications Service Providers worldwide. Jake has been with Teradata since 2001 and has 25 years of experience working with Teradata within the Communications Industry, as a programmer, DBA, Data Architect and Modeler.

Leave a Reply

Your email address will not be published. Required fields are marked *