Hello All,

I was asked this question : " How will you go about optimizing Star Schemas
?". I understand data warehouses and understand what a Star Schema is.
However, I was not sure how to address this question.

Can someone point me in the right direction.

Thanks,
rgn

Hello Gopi,

I guess it might be good to qualify what you mean by "Optimizing Star
Schemas"

In my view there are two main points of Optimization

1. ETL: The process of populating the Star Schema.

2. Star Schema Query performance: The creation of reports and ad hoc
queries.

I am assuming you are really interested in Star Schema Query
performance. I am also assuming you are using surrogate keys. Here are
some basic rules.

1. Make sure all dimensions PK keys have a clustered index that is
used for joining to the fact table.

2. Make sure the Fact table has a good covering index for all the
corresponding dimension keys. A cluster index would be preferable.

3. Check the covering index is in the right order for most common
queries.

I would recommend that you look at using Analysis Services 2005 as your
main query engine. It will give you the best query performance by far.

Hope this helps,

Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/