Time Zones Conversion and Standardization Using Informatica PowerCentern
When your data warehouse is sourcing data from multi-time zoned data sources, it is recommended to capture a universal standard time, as well as local times. Same goes with transactions involving multiple currencies. This design enables analysis on the local time along with the universal standard time. The time standardization will be done as part of the ETL, which loads the warehouse. In this article lets discuss about the implementation using Informatica PowerCenter.

We will concentrate only on the ETL part of time zone conversion and standardization, but not the data modeling part. You can learn more about the dimensional modeling aspect from Ralph Kimball.

Business Use Case

Lets consider an ETL job, which is used to integrate sales data from different global sales regions in to the enterprise data warehouse. Sales transactions are happening in different time zones and from different sales applications. Local sales applications are capturing  sales in the local time. Data in the warehouse needs to be standardized and sales transaction need to be captured in local as well as GMT time.

Solution : Create a reusable expression to convert the local time into GMT time. This transformation can be reused in all the ETL process, which needs a time standardization. This reusable transformation can be used in any Mapping, which needs the time zone conversion.

Building the Reusable Expression

You can create the reusable transformation in the Transformation Developer.

In the expression transformation, you can create below ports and the corresponding expressions. Be sure to have the ports created in the same order, data type and precision in the transformation.
    • LOC_TIME_WITH_TZ : STRING(36) (Input)
    • DATE_TIME : DATE/TIME (Variable)
    • TZ_DIFF : INTEGER (Variable)
    • TZ_DIFF_HR (V) : INTEGER (Variable)
    • TZ_DIFF_MI (V) : INTEGER (Variable)
    • GMT_TIME_HH : DATE/TIME (Variable)
    • GMT_TIME_MI : DATE/TIME (Variable) 
    • GMT_TIME_WITH_TZ STRING(36) (Output) 
Now create expressions as below for all the ports.
    • DATE_TIME : TO_DATE(SUBSTR(LOC_TIME_WITH_TZ,0,29),'DD-MON-YY HH:MI:SS.US AM')
    • TZ_DIFF : IIF(SUBSTR(LOC_TIME_WITH_TZ,30,1)='+',-1,1)
    • TZ_DIFF_HR : TO_DECIMAL(SUBSTR(LOC_TIME_WITH_TZ,31,2))
    • TZ_DIFF_MI : TO_DECIMAL(SUBSTR(LOC_TIME_WITH_TZ,34,2))
    • GMT_TIME_HH : ADD_TO_DATE(DATE_TIME,'HH',TZ_DIFF_HR*TZ_DIFF)
    • GMT_TIME_MI : ADD_TO_DATE(GMT_TIME_HH,'MI',TZ_DIFF_MI*TZ_DIFF)
    • GMT_TIME_WITH_TZ : TO_CHAR(GMT_TIME_MI,'DD-MON-YYYY HH:MI:SS.US AM') || ' +00:00' 
Note : The expression is based on the timestamp format 'DD-MON-YYYY HH:MI:SS.FF AM TZH:TZM'. If you are using a different oracle timestamp format, this expression might not work.
Below is the expression transformation with the expressions added.
Time Zones Conversion and Standardization Using Informatica PowerCenter
The reusable transformation can be used in any Mapping, which needs the time zone conversion. Below shown is the completed expression transformation.Time Zones Conversion and Standardization Using Informatica PowerCenter You can see a sample output data generated by expression as shown in below image. 
Time Zones Conversion and Standardization Using Informatica PowerCenter

Expression Usage

This reusable transformation takes one input port and gives one output port. The input port should be a date timestamp with time zone information. Below shown is a mapping using this reusable transformation.
Time Zones Conversion and Standardization Using Informatica PowerCenter
Note : Timestamp with time zone is processed as STRING(36) data type in the mapping. All the transformations should use STRING(36) data type. Source and target should use VARCHAR2(36) data type.

Download

You can download the reusable expression we discussed in this article. Click here for the download link.

Hope this tutorial was helpful and useful for your project. Please leave you questions and commends, We will be more than happy to help you.

Share with your friends

Readers Comments