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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics