April 9, 2008 at 9:30 am
Hello,
I have developed some packages to load data into "Fact" tables in the data warehouse.
Some packages are OK, other ones not. What is the problem?: some packages load fact tables with lots of "Lookup - Data Flow Transformation" into the "data flow task" (lookup against dimension tables) but they are very very slow, too much slow to be choosen as a solution.
Do you have any other solutions to avoid using "Lookup - Data Flow Transformation"? Any other solution (SSIS, TSQL and so on....) is welcome to speed up the Fact table loading process.
Thank in advance
April 9, 2008 at 10:05 am
You may have a problem other than simply too many lookup tasks.
I have seen packages move millions of rows through almost 30 lookup tasks and they perform fine.
You can probably get better performance out of staging the data and using joins, but the method of doing the lookup may not be the issue.
April 9, 2008 at 10:09 am
Hello
thank for your contribution,
the packages are developed following the classical reccomandations: like: not use table or view option, limiting the number of columns (only which useful) and so on, but the process is very low.
I think there are too much lookups, more than 10 lookups, but I need it to lookup into several Dimension tables (that they are very big).
Let me know
April 9, 2008 at 10:12 am
How many rows are in the dimension tables?
Lookup components can be tricky when you get to a large lookup table. Their performance can degrade quickly when the number of rows they contain gets high.
Rather than a lookup when you have large tables, you may want to load your data into some staging tables and join them together with T-SQL.
April 9, 2008 at 10:32 am
Hello,
I think the problem is in the dimensional tables too large, but it is my opinion.
I guessed the same...to create some staging table and using TSQL.
But what you would build, can you make some examples?
Thank
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply