- Take advantage from table partitioning for large fact table.
- Put clustered index on fact table key, specially for datetime column and do partitioning based on this column.
- Put non clustered index on non datetime column of fact table, when the query usually using exact criteria.
- Do the query based on interval criteria, put BETWEEN on WHERE clause when dealing with datetime key, ofcourse after put clustered index on it.
There are another tips related to Analysis Services, hardware, and deal with Integration Services. Download my presentation here: SQL2005LargeDW
Due some users that encountered problem when download entracting the file, I have uploaded again. I have double tested and it works fine.