topic how to create surrogate keys for output tables and do full outer join them? in Talend Studio

how to create surrogate keys for output tables and do full outer join them?

Anonymous — Wed, 14 Nov 2018 18:40:04 GMT

Hi together

I have DBInput and want to push the rows into 4 different DBOutput tables to get a star schema. (it's 1 fact table and 3 dimension tables)

In tMap I created for each dimension table a new column as surrogate key by using Expression Numeric.sequence("s1",1,1).

schema editor: surrogate keys are datatype int, key flag, null no flag

In my fact table i created the surrogate keys of the tables as well to have a foreign key also by using Numeric.sequence("s1",1,1).

schema editor: surrogate keys are datatype int, but it's not possible to set key flag for all 3 columns (only 1).

Job runs without any failures but the when i do a sql query with all kind of joins the fact table is NULL because it didn't match.

Can someone help me, how to get the matching? Below you can see the screenshots!

Thanks in advance

fact_table
fact_table_schemadimension_tabledimension_table_schmea

Re: how to create surrogate keys for output tables and do full outer join them?

Anonymous — Thu, 15 Nov 2018 04:32:02 GMT

Hi,

My suggestion will be to always keep the surrogate key sequence within the database and you can always pick the last number+1 from the database when you are calling the next value function.

Once you extract the next value back from DB to Talend during load time, you can formulate the sequence generation with the first number in Talend sequence from the above value (instead of starting the sequence every time from 1).

Could you please try this option and if you get any issues, feel free to comeback with job flow screen shot and the screen shot of the component where you are getting error?

Warm Regards,

Nikhil Thampi

Re: how to create surrogate keys for output tables and do full outer join them?

Anonymous — Thu, 15 Nov 2018 14:18:04 GMT

To be honest I didn't really understand your solution. However, in the meantime I found out my two mistakes: I just wrote the sequence expression next to the column - I didn't create a variable for it. That was the first problem. Second was, that I didn't rename "s1" in sequence expression. I've had three variables named the same so the counting was wrong. Now it works as it should.

Thank you anyway

Re: how to create surrogate keys for output tables and do full outer join them?

Anonymous — Thu, 15 Nov 2018 14:24:08 GMT

Hi,

Apologies if my words have confused you.

Let us discuss the flow with an example. You are trying to load the data to Person dimension table and you are allocating new records based on sequence. Assume today you have loaded 10 records to this table. So the surrogate key will have 1 to 10 in the personid column.

Tomorrow, if we are getting another 20 records, the sequence you have created in Talend will again start from 1 to 20. This means the first 10 records out of 20 on day 2 will be rejected due to primary key violation in target table.

Hope you are getting the issue what I am highlighting.

Warm Regards,

Nikhil Thampi