Convert Date and Time Data with the Informatica Mapplet

Rohit PatrikarDecember 24, 2015

The BI industry deals with enormous volumes of data shared across many disparate locations from cloud-based to physical locations. For the purposes of this article, we will use the retail industry as an example where organizations have distribution centers and stores around the world.

 

Data from distribution centers often originates from diverse time zones and ends up at one central warehouse for further analysis. As the data loads, local time from the original source to the warehouse frequently does not account for time zone differences. This presents a challenge when the data must be analyzed based on local time of origination because BI reports can display the wrong results. These reports, stamped with the time at the warehouse’s location, can reflect a time lag and influence decisions without using accurate data and potentially cause financial losses.

 

With many clients facing these challenges, we discovered a need for the source data time to be converted into the local time zones of the target system. None of the existing ETL tools were smart enough to handle the time zone difference and daylight savings adjustments in certain geographical areas.

 

Correctly converting source time to destination time with special handling for daylight savings adjustments is complex and tedious, so careful consideration was needed when developing a solution that could do both. Generally, many codes ignored time zone adjustments or standardized the source time data as it was presented.

 

The process of gathering this data and standardizing it is time-consuming and frequently inaccurate. This is also a non-scalable solution because each time a new geographic location is added, the code has to be manually altered to provide results based on the additional time zone.

 

InfoCepts Solution:
Our solution to this time zone conversion issue was to create a script and logic that quickly and automatically converts source time zone into destination geographical time zone during data loading in order to effectively populate proper BI reporting metrics. InfoCepts’ solution in Informatica converts date and time data of a given time zone to a target time zone and includes accommodations for daylight savings.

 

The Informatica mapplet is one of the components in the PowerCenter Designer, which is generally used for designing reusable code. It can be embedded into any mapping code easily and will run during respective sessions or task-runs via the Informatica Workflow to provide accurate results. It can also be used in Informatica Developer by importing the XML.

 

Usage:
This mapplet enables location-based analysis, where the results need to be in the local time zone rather than the originating source data’s time zone. This is especially useful for supply chain projects generating data in different time zones for given dates.

 

Benefits of Informatica’s Time Zone Conversion Mapplet

  • Easy to embed in any PowerCenter mapping to fulfill business requirements
  • Eliminates the requirements of creating complicated Excel spreadsheets with formulas and transformations to convert to the desired time from different time zones for complete analysis and BI metrics reporting
  • Allows for minimal manual intervention by effectively using automated code, saving time and money
  • Can handle dates from YEAR – 1901 to YEAR – 2038
  • Highly reusable and can be used with any source or target databases supported by Informatica or flat files
  • Adds more meaning to data thus helping the user to make more accurate decisions
  • Perfect for supply chain projects, where distribution centers and stores are in different time zones and all the time lines (delivery time, order time, and more) need to be converted into local time zones

With the time zone mapplet, it becomes easier to generate and manage data with real-time results across different time zones with greater accuracy. Want to learn more about how the time zone mapplet can ensure accurate and timely reporting of all your data? Find specs and details in the Informatica Marketplace.