January 2, 2009 at 8:14 am
Hi Everyone,
I'm looking for the best way to load the fact table. Let me explain the problem I have right now. I have more than 20 dimensions. And I have more than 2 million of records in staging. Using lookup tasks I'm getting the ID's from the dimensions and loading to the fact table. when the data grows the fact package tasks take time to complete. Can you suggest me the best way to load the fact table with good performance? Let me know if you need more details.
Thanks in Advance.
Thanks & Regards,
Sathish Kumar.G
January 3, 2009 at 7:40 am
Is this just for an initial load or are you loading 2M rows on a daily basis? Are you using SSIS 2005 or 2008? Here is a posting in regards to a few items to consider in regards to lookup performance - Getting Optimal Performance with Integration Services Lookups.
What types of settings are you using on the lookup transform? Are you using Full Cache mode? How large are the dimension tables being referenced? You can find some more information here - http://msdn.microsoft.com/en-us/library/ms141821.aspx for lookup information and there is a link to get to the 2005 documentation.
You can also refer to a few comments in the 'Optimize the SQL data source, lookup transformations, and destination.' section fo the Top 10 SQL Server Integration Services Best Practices.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 3, 2009 at 8:59 am
Thanks for your inputs.
We are using SSIS 2005 and loading data on monthly basis. In lookups, we are not setting the Caching in the lookups. 3-4 dimensions are having data around 1M. And it will grow month by month. Other dimensions are having records in hundreds.
January 3, 2009 at 10:19 am
I would definitely look at and utilize the caching options available in the lookups so that you save on re-querying the database to perform the lookup value. Just remember that using the Full Cache option is case sensitive and there are numerous postings about that (make sure you trim and also perform and UPPER or LOWER operation to ensure they are equivalent).
If you are considering going to SSIS 2008 then you can utilize the new Cache Transform (similar to Hash Files in DataStage) and reference these in your lookups (very useful if you are performing multiple fact table loads with similar dimensions). There are also quite a few improvements to the lookup transform in 2008 that you will benefit from.
Using the SQL 2008 Lookup and Cache Transforms in a SQL Server Integration Services (SSIS) Package
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply