Lookup recommendation

perspicax · Posted: Mon Feb 12, 2018 3:39 pm

New to DS parallel edition. I am trying to build a DS job for a fact table which has references to 35 dimensions (via Surrogate key).

What is the best way to populate the Surrogate keys. I do not want to form a gaint SQL with left outer join to reference tables from driving table. Rather I would like to use the lookup stages. I added 4 lookup stages serially, and I see that the data being pipe-lined nicely. But job construction is taking a long time.

I am also looking to re-use the lookup in few other jobs involving fact tables because the tables have almost same number of dimensional references.

Instead of using one lookup per reference table, is it a better idea to use one lookup stage with all the reference tables?

How is this normally done in DS?

bhargav_dd · Posted: Tue Feb 13, 2018 8:54 pm

What is Size of your reference table because lookup consumes more memory if reference tables are of more size as it traverses entire record to perform operation