Hi,

I have a fundamental question to ask. I have to populate 7 new data
marts (Fact tables and non-shared dimensions) from our enterprise
datawarehouse (EDW). So I have to import data from EDW, calculate
measures and retreive dimension ids. The fact tables will have many
rows and will be updated monthly (monthly snapshot). I'm using DTS to
Extract-Transfert-Load the data. Our EDW has several tables with high
volume (up to 140 M of rows for the largest table, around 800 GB of
data for the overall database). To reduce the complexity and enhance
the overall performance, as it is recommended, I will break up complex
data load and transformation processes into multiple steps that uses
temporary tables.

Currently, there is 2 datamarts in production (developped 2 years ago).
These 2 datamarts use also DTS to ETL the tables, but they use flat
files instead of temporary tables to store and process data in the
intermediate steps.

I would like to know if there is a "performance" best practice to use
flat files instead of temporary tables? Does anybody has experimented
both procedures???

Thanks

Hello endt,

Chances are if this was a "more than once" operation and it sounds like it
is I would be making these temp tables permanent tables.
Now the issue comes with which is quicker.

Have a read of this

http://www.sql-server-performance.co...emp_tables.asp

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hello Alan,

Thank you for your answer. As you have suggested, I would certainly
use permanent tables instead of temporary tables in the intermediate
steps. But, I would like to know if the use of flat files to
manipulate data instead of tables in the intermediate steps could be an
option to improve performance, reduce the process time?

Thanks

Eric.

Hello endt,

I do not see that they would enhance perf any no. If you want to then manipulate
the data in the files upon extract then you are struggling when using the
text file driver as it is not flexible whereas going against a SQL Server
source is.

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com