Expert Consultancy from Yellow Pelican

Reusable Job LibMakeDimTime

A site about Talend

LibMakeDimTime

This reusable and configurable Job, generates the data that is required to populate a Data Warehouse Time Dimension. People often use this term to describe a Date Dimension; however, in this series of articles, we will be creating Dimensions to support both Date and Time. Time will, therefore, refer to a Dimension that supports only a time element.

Download LibMakeDimTime

You may download LibMakeDimTime and import (Job Designs->Import items) in to a Talend Project of your choice. This download is supported by Talend 5.2.2 and above.

Download

Usage

This Job may be added to any other Job (the parent Job), and provides the input for creating a Time Dimension. You may use the output as-is, drop the columns that are not of interest, modify existing values or add additional ones - without the need to modify the library Job itself.

To see this library Job in use, read our article Create Data Warehouse. This is one of a series of articles that are described in Talend Stock Market Analysis Project.

Generated Data

The table shown, below, demonstrates the data generated by this Job. You may drop columns that are not required (from within your own Job), modify values, and add your own. You may modify the textual values by passing your own, as detailed above.

Naming Conventions

The variable naming conventions break away from the Talend (Java) convention as it is expected that this data will be loaded in to a database and it is, therefore, more helpful to use a naming convention that is more in keeping with what you would expect to see in a database.

TIME_ID

TIME_ID is the Primary Key for this Dimension and, as can be seen from the sample data shown below, it has been derived from the Time. Some may argue against this and say that it should simply be an arbitrary number. I would, generally, agree with this; however, I have chosen to do this so that the key may be calculated by data loaders, rather than requiring a look-up.

Sample Data

ColumnSample ValueDescription
TIME_ID0Primary Key
THE_TIME00:00:00The time in 24 hour clock.
THE_TIME_1212:00:00The time in 12 hour clock.
HOUR0
HOUR_1212
MINUTE0
SECOND0
MERIDIEMAMAM or PM

Job Design

This Job is added to a parent Job using the tRunJob Component. Once added, you should hit the button Copy Child Job Schema and then connect this component to another component of your choice. This may, for example, be a t[DB]Output component.




Expert Consultancy from Yellow Pelican
comments powered by Disqus

© www.TalendByExample.com