Indexing Star Schemas

Hi
Can anyone add to / comment on / improve / criticise this logic for
indexing a fact table in a star schema:
Presuming a fact table with 4 dimensions:
* Brand
* Product
* Time
* Store
1. Clustered index on Time dimension
EVERY query is going to filter based on date, so this is an obvious
choice.
It is NOT a composite index for several reasons:
a) keep the record ID narrow so the index (& thus non-clustered
indexes) load into memory easier/faster.
b) common queries don't always a particular other dimension, which
would mean the index would only be useful to the Time level anyway.
c) common queries on product and store are not very selective
2. Non-clustered indexes on Foreign Keys
Create non-clustered indexes on each of the other 3 foreign keys, for
obvious reasons: Brand, Produce, Store.
3. Composite index on all dimensions
Some common queries use all directions. A complete composite index
would assist Cartesian product lookups. Create the index in order of
selectivity: Brand, Product, Store
(I'm not sure if Time would need to be explicity declared in this
index - as this is a non-clustered index, Time is included
"behind-the-scenes" as part of the RID anyway - but does it need to be
explicity declared?)
4. Only remaining index
The only remaining index would be Product, Store. This is done in
order of selectivity.
This means that any combination of 1, 2, 3 or all 4 of the dimensions
as criteria in the query will be catered for.
Does that make for a good strategy? Any comments / further
suggestions?
Thanks
Sean
It appears that you are indexing for select queries against the data
warehouse. Are you reporting directly from the data warehouse or building
cubes and reporting from them?
If you will be building cubes and reporting from them, then the indexes on
the tables should be geared toward integrity ( like the multicolumn index) ,
and the selecttions related to cube build and ETL interaction...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0410080749.cff18d3@.posting.google.co m...
> Hi
> Can anyone add to / comment on / improve / criticise this logic for
> indexing a fact table in a star schema:
> Presuming a fact table with 4 dimensions:
> * Brand
> * Product
> * Time
> * Store
> 1. Clustered index on Time dimension
> EVERY query is going to filter based on date, so this is an obvious
> choice.
> It is NOT a composite index for several reasons:
> a) keep the record ID narrow so the index (& thus non-clustered
> indexes) load into memory easier/faster.
> b) common queries don't always a particular other dimension, which
> would mean the index would only be useful to the Time level anyway.
> c) common queries on product and store are not very selective
> 2. Non-clustered indexes on Foreign Keys
> Create non-clustered indexes on each of the other 3 foreign keys, for
> obvious reasons: Brand, Produce, Store.
> 3. Composite index on all dimensions
> Some common queries use all directions. A complete composite index
> would assist Cartesian product lookups. Create the index in order of
> selectivity: Brand, Product, Store
> (I'm not sure if Time would need to be explicity declared in this
> index - as this is a non-clustered index, Time is included
> "behind-the-scenes" as part of the RID anyway - but does it need to be
> explicity declared?)
> 4. Only remaining index
> The only remaining index would be Product, Store. This is done in
> order of selectivity.
> This means that any combination of 1, 2, 3 or all 4 of the dimensions
> as criteria in the query will be catered for.
>
> Does that make for a good strategy? Any comments / further
> suggestions?
> Thanks
> Sean
|||if you use cubes, i personally wouldn't index a single thing
it's just a waste of time.
keep the clustered on time-- it's generally really helpful
what does the index tuning wizard reccomend?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OSmxSFSsEHA.2808@.TK2MSFTNGP14.phx.gbl...
> It appears that you are indexing for select queries against the data
> warehouse. Are you reporting directly from the data warehouse or building
> cubes and reporting from them?
> If you will be building cubes and reporting from them, then the indexes on
> the tables should be geared toward integrity ( like the multicolumn index)
,
> and the selecttions related to cube build and ETL interaction...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Sean" <plugwalsh@.yahoo.com> wrote in message
> news:3698af3c.0410080749.cff18d3@.posting.google.co m...
>