Fact Table Indexes

[arowshan]

I am trying to create indexes on a fact table which I have SSAS partitions for. I have a surrogate primary key column which we use to update fact rows. I would also like to have an index for the date key since the partitioning is done by date. If I include the date key in the primary key (suggested in an SSAS book), I would have two options for the key of the index: (factKey, dateKey) or (dateKey, factKey). If I go with the first option, date queries for partitioning of the cube would be slow since the table is ordered by factkey first. If I go the other way, updating fact rows would be super slow.

One option I have is to create a non-unique clustered index for the date key and create a non-clustered primary key index on the fact key. The other option is the make the surrogate primary key clustered and make the date index non-clustered which I would either have to live with column lookups or include all columns in the date index making the index big. Any suggestions?