Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Indexes on fact table Expand / Collapse
Posted Monday, July 30, 2012 12:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 11, 2016 4:23 AM
Points: 336, Visits: 1,026
my fact table consist of 4 dimensions. I have a composite primary key on all the 4 dimensions. the time dimension is of 15 minutes and it is one of the dimensins. i have one more date column says just the date without 15 minutes just to tell which date it is.

it has been designed already and now i can't remove the other date column. and it has been used in reports and other queris.

my question is to speed up the activity should i create an index on the same?

Post #1337066
Posted Monday, July 30, 2012 12:09 AM



Group: General Forum Members
Last Login: Yesterday @ 6:23 AM
Points: 15,499, Visits: 13,162
Which data column did you want to remove exactly?
And which column do you want to speed up?

Typically, indexes will enhance query performance, if created correctly.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1337069
Posted Saturday, August 4, 2012 1:54 PM

Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
As a rule of thumbs it should be an index per each dimension pointing to a specific fact table.

Remember that the function of the dimensions is to provide the means to slice-and-dice the measures stored in the fact table therefore a sound indexing strategy is a must.

A single index on all dimensions would help only queries using all dimensions to filter/acces the data - which is not usually the case.

Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1340215
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse