Fact Table Indexes

View previous topic View next topic Go down

Fact Table Indexes

Post  arowshan on Wed Sep 26, 2012 2:51 pm

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?

arowshan

Posts : 23
Join date : 2011-10-18
Location : Vancouver, Canada

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum