index

[dellsters]

For a junk dimension, do you create individual indexes on columns or one index with multiple columns? How do you determine which way to go with?

Also in the fact table, do you create one index with all the foreign keys or each individually?

[ngalemmo]

For fact tables, create an index for each foreign key. In an unstructured ad-hoc query environment, compound keys are, for the most part, useless. If you are using Oracle, enable star schema optimization and use bit map indexes for the facts.

For dimensions you should have a PK index on the surrogate key and an index for the natural key (although the latter is optional if you are using a ETL tool and intend to cache dimension lookups). Individual attribute indexes are optional.