Question

Create slowly changing dimensions

  • 12 February 2021
  • 1 reply
  • 5 views
jayqueue
Badge

Hello,

I have an original table and api result  that I monitor with a ChangeDetector. I created changes {} and do a ListExploder to return each record and write to a DIM table. Now I want to add the dates ValidTo and ValidFrom to the DIM so I can keep a history of those records.

Short said, which each Update output from ChangeDetector I need to create a new record and change the dates (in the previous with the same id) 

 

ID     AttrColumn     ValueColumn
1       AttrValue1          1
2       AttrValue2          2
DIM_id      AttrChanged     ValidFrom    ValidTo            Active
1                  ValueColumn   2021-02-01  2021-02-12     N
1                  ValueColumn   2021-02-12  DefaultValue  Y

Hope it's clear..

Any ideas?

Thanks!

 

 

1 reply

tomf
Rank
Badge +7

For each update feature you will need to clone it so that you have two copies.

One will be the feature that updates the existing record, matching on the id field. For this feature, Create/Set ValidTo to the date value and Active to N. Drop the AttrChanged attribute so that you don't overwrite the old value with the new. Then write the feature out using an UPDATE mode.

The second will be the new record, so Create/set ValidTo to the date value and Active to Y (and possibly ValidTo to the default value unless the db will auto populate this) and write the features our using an INSERT mode.

For DB operations you can make use of the fme_db_operation attribute which should be already set to UPDATE coming out of the Update port of the ChangeDetector. Just change the value to INSERT for the new features, then write out using your DB writer setting the DB mode to use fme_db_operation and row identifier to be ID.

This tutorial on Updating Databases should help you, particularly the section on updating existing features

Reply


Community Stats

28,694
Posts
109,440
Replies
36,546
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.