Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Alter date key while loading to db

  1. #1
    Join Date
    Aug 2013
    Posts
    139

    Default Alter date key while loading to db

    hi,

    I am loading data from oltp to dwh using etl from mysql to mysql but in oltp the data is in 24 hours format

    but the reporting should be done on 6am-6am so while converting to dwh if i can use a conditon like if timekey<060000 then the datekey should be
    date-1day (if date is 20141201 it should not be 20141200)

    in oltp i have only timestamp 2014-12-12 12:12:12

    1. I tried a procedure in database which i used to call as an input to ETL with above logic (but it is dead slow) table contains around 20 million records at any point of time

    2. is there a way that i can convert after loading data in etl
    Thank U For Your Time
    Suresh

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    If Condition, do (A) Else do (B)

    I think a Filter Rows step can do that... You might need to put a calculator step in first to develop your Condition (Hour Of Day < 6 )
    A could be another calculator: Report Date = <OLTP Date> - 1 day
    B could be a select values: Report Date = <OLTP Date>

    Then use an uncontrolled stream merge (eg Dummy Step) and you're done.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.