At the base of any good BI project is a solid data warehouse or data mart.  We can talk star schemas all day long and about the variation of snowflake versus but the main goal of this post is acknowledging the neglecting of the fact table as the foundation on which the star schema’s house resides.  I see way too often clients with poorly performing retrievals against their subject areas as I enter new engagements and work from the client’s existing infrastructure.  This is really unacceptable not only from a professional implementation perspective but also from one of query performance.  This post will look at what the default indexing of any fact table should be, why the default indexing is needed, why composite keys are good and bad, and why you should care.

I owe this post to my buddies Owen and Greg.  This one has been a long time in the writing and I have finally gotten around to putting fingers to keys.

Default Indexing Best-Practice

Short and sweet – all foreign key columns should have a non-clustered non-unique index. 

So what does that mean?

It means that a foreign key reference on a column to a table containing the primary key reference only provides the database engine with a point-of-reference to your dimension table.  It does nothing for how the data in the fact table is organized.  So, after you create a foreign key (FK) DDL statement be sure to create the non-clustered, non-unique INDEX as well.
Read the full story