How to perform lookup of dimension keys when loading a huge fact table?
Hi,
I have a fact table which has around 50 million data. When I perform lookups for dimension keys, it is taking long time to load. What is the fastest method to load Fact table?
nasayoo
October 4th, 2011 11:38pm
Hi,
I have a fact table which has around 50 million data. When I perform lookups for dimension keys, it is taking long time to load. What is the fastest method to load Fact table?
nasayoo
How do you know your lookups slow down the process? How many rows are your dimension tables? Also what component do you use for destination and what is the destination database?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
October 5th, 2011 12:05am
I perform dimension key lookups in a script component(transformation) which performs lookups on both dimension tables and in some source tables as well. I use OLD DB Destination and the destination database is SQL Server DB.
btw, I couldn't use the lookup component, since my lookup logic is little bit different from traditional lookups. nasayoo
October 5th, 2011 12:31am
I perform dimension key lookups in a script component(transformation) which performs lookups on both dimension tables and in some source tables as well. I use OLD DB Destination and the destination database is SQL Server DB.
btw, I couldn't use the lookup component, since my lookup logic is little bit different from traditional lookups.
nasayoo
Can you describe why it is not possible to use standard Lookup?
How many records do you have in your Lookup data (not fact data) ? SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
October 5th, 2011 12:34am
Just realized that I could use custom query in partial cache mode of Lookup component. What are the best practices to ensure the fast data load into the fact table?nasayoo
October 5th, 2011 12:49am
Just realized that I could use custom query in partial cache mode of Lookup component. What are the best practices to ensure the fast data load into the fact table?
nasayoo
It is complicated because it depends on your transformation logic and complexity. The general rule of thumb is you have to find, which part is slowing you down by measuring. This
article by Todd McDermid is very good starting point.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
October 5th, 2011 12:58am
Just realized that I could use custom query in partial cache mode of Lookup component. What are the best practices to ensure the fast data load into the fact table?
nasayoo
It is complicated because it depends on your transformation logic and complexity. The general rule of thumb is you have to find, which part is slowing you down by measuring. This
article by Todd McDermid is very good starting point.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
October 5th, 2011 7:52am