You are not logged in.
Here's what I want to do:
There's a list of product codes I get from a DB2 database and I want to populate a dimension with that data. So basically I make a query to get the product list, pass it thru a filter (to get out garbage data) and enter it to a tMap. This is working fine.
I'm trying to make the tMap compare the incoming list with the records currently in the dimension table so the new ones are added to the dimension. Also pass the next key for new items (select (case when max(product_key)+1 is null then 1 else max(product_key)+1 end) newKey from product) which gets 1 if the dimension is empty or the next key otherwise.
Is this the correct approach? I'm totally wrong?
Help is really appreciated!
There are some SCD (Slowly Changing Dimension) components. I haven't looked into these too much. But, it looks like you want some sort of effective dating / tracking. I believe that's what these do.
As for the key - I'd recommend (if you can) that you let DB2 do the working of tracking the next sequential key either via an insert trigger or define that column as an identity column. Something like this in the DDL:
"YourColumnName" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
NO ORDER )