Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Indexes on fact table Expand / Collapse
Author
Message
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, July 18, 2014 9:11 AM
Points: 317, Visits: 929
hi
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?

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 13,606, Visits: 10,489
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 LessThanDot.

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,068, 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