CODEWITHSUNDEEP
pentahokettle
Raj Kamuni
Raj Kamuni

Reputation: 388

Issue with create dimension table with last update time column in Pentaho Data Integration

I am creating dimension table with last updated time(from GetSystemInfo)in Pentaho Data Integration(PDI).It works fine except it enters new rows even there is no changes in row and reason is there is lookup is also performing on last updated time field which should not perform. But when I removes this field from key field from attribute Dimenssion lookup/update it works as expected but values in lat time updated field goes empty.Thanx in advance for any solution/Suggestion.

Upvotes: 1

Views: 1509

Answers (2)

prasad reddy
prasad reddy

Reputation: 1

I think it is better to use the below components

  • Step 1: Using table input step you can get a max value from target system and pass the value to next step
  • Step 2: Take one more table input step and write a source query and assign the previous value in where clause(like ?)
  • Step 3: Then perform the as usual operation on target level

I think you are getting the above steps.

Upvotes: 0

mzy
mzy

Reputation: 1764

I expect you are talking about SDC II. (Slowly changing dimension of type 2) here and you want to store a date of when a row is inserted to a SCD table.

  • Instead of obtaining data from GetSystemInfo step, you can use Date of last insert (without stream field as source) type of dimension update in the Fields tab of Dimension Lookup / Update step which stores a datetime automatically in defined table column.

Additional hint: If you need to store maximum value of some date from a source system table which is relevant for loading new / changed data, store its maximum right after Dimension Lookup / Update step into a separate table and use it as when loading updated data at the beginning of a ETL transformation.

Upvotes: 1

Related Questions