Index all the dimension key columns, individually, non clustered (SQL Server) or bitmap (Oracle). The dim key columns are used to join to the dimension tables, so if they are indexed the join will be faster. Create the following indexes:

  • Index the fact key separately.
  • consider creating a covering index in the right order on the combination of dim keys
  • if the fact table is partitioned the partitioning key must be included in all indexes.