Hi,

the article is good but I missed the usage of the new possibilities of SQL Server 2005 SSIS. In SQL 2000 it was a common way to use a "staging area" in form of a temporary table for the dimension mapping, calculations and whatever. However, performance problems arised when dealing with tons of data. Because every time you write data into your database you have disk IO which slows down performance dramatically.

With SQL 2005 you can avoid this behaviour, because the SSIS architecture enables you to perform nearly all transformations in memory and then writing the data into the fact table. This is much faster than using a staging table and common best practice.

Another thing I do not agree with is using the combination of all surrogate dimension keys in the fact table as clustered index. How does this work in a table with xx million rows and 15 dimensions? This is much afford for the dbms to maintain such an index and I do not believe that it works performantly.

I made good experiences by using one of the date surrogate keys as clustered index for due date reporting fact tables. Other experiences?

Best regards,

Stefan