Fact Table Indexes
Page 1 of 1
Fact Table Indexes
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?
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
Similar topics
» Fact Table Indexes
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Implementing secondary indexes on fact tables. Is it a good idea?
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Implementing secondary indexes on fact tables. Is it a good idea?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum