Implementing secondary indexes on fact tables. Is it a good idea?

Hello, we have surrogate keys on our fact tables and dimension tables.

While designing my stars, I had to leave two columns (eg: doc_id and doc_item_num) that are a part of business keys on the fact table itself. The other columns were Customer Dimension and Date dimension. I defined the primary index as (doc_id, doc_item_num, dte_surr_key). This was a good selection as the users are running time-based queries quite often.

Now, on certain cases, the queries are being written with just doc_id, doc_item_num and the database is not able to respond. The query is taking a long time to respond. The DBA suggested we add secondary indexes on the fact table for doc_id and for doc_item_num. I know that adding secondary indexes on a fact table will slow down ETL load processes. I also read that adding additional indexes on fact tables is not a good practice.

Please, suggest any alternatives or your points of view on this one.

Thank you,
Sreekanth

Drop indexes before loading and rebuild when done. Secondary indexes are fine and required.

If this is a SQL Server database then the engine can actually use the the existing clustered index for both those scenarios. It can use columns from left to right so if the index is set as you have specified "(doc_id, doc_item_num, dte_surr_key)" then the engine will still use this index to run a query where the predicates are "doc_id, doc_item_num" as they are the first two columns in the index.

As for dropping indexes to to clarify, only drop the non clustered indexes not the clustered index. Also this is not always a 100% sure shot to speed ETL processing.