Lookup recommendation
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?
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?