SlowlyChangingDimensions

KCH Workflows
1
This is a companion discussion topic for the original entry at https://kni.me/w/6hFVfC1345GV--pr
2

Can someone please explain how does the DB Update(SCD) node works ?

4

Hi @nhbadza,

The DB Update (SCD) component is a naive implementation of Type 2 Slowly Changing Dimensions. That is, an update to the DB will not just update a row, but it will also keep the previous state of the row and keep the time until when the previous state was the correct state.

Hope that helps!

Best,
Stefan

6

Thanks @stelfrich

But I think I’m still lost on how the SCD Type2 workflow works
Please help ?

7

Does the node only work for SQLite because I’ve tried to connect it to Microsoft SQL connect and it’s giving me the error “Execute failed: Invalid object name 'sqlite_master”

8

It should be database agnostic because it only uses regular inserts and updates. I have, however, only tried it with SQLite to be perfectly honest.

It is a pretty naive implementation that does the following, given you already have a table that has the columns Valid From (date and time), Valid To (date and time), and Valid (boolean):

  1. Take a table of entries to be updated
  2. Get the current state of the database table to see if you find any duplicates with the input table: Don’t update those (because no information has changed)
  3. For the rows where data has changed
    a. replace the Valid To entry with the current execution time and set Valid to false
    b. insert a new row into the database table with the new data and set Valid From to the current execution time and Valid To to Infinity

That’s about it… Please note that the execution in Box 1 only creates a table for you to use. The first real update happens in Box 2 because the Table Creator contains changed data.

Best,
Stefan

1 Like
9

I tried to implement it the way you mentioned above but I still get an error Invalid object name sqlite master
Please let me know if there is any other way of implementing Type 2 in knime