Indexing in SQL Server Star Scheme Data Warehouse

Hi all,

Our star schema design has one fact table and 3 dimensions.

The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:

Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3

Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.

Is the above design good to start with?

Thanks,

VThe indexing looks fine, but as to whether this is a good design or not you only need to check my sig below to get my opinion...|||Thanks Blindman.

The one issue that we are encountering is, we didnt create a separate time dimension (a mistake in design). We have a smalldatetime field (72 distinct values only, one for each month, so 6 years in total) in the fact table.

We are not able to query this smalldate time field efficiently because we didnt index it (as it was not part of the dimension). We would like to change the design now.

We would like to create a time dimension using the following:

1. Create Time Dimension Table
2. Create new column Time_Key in Fact Table
3. Create Non clustered Index on smalldatetime field in fact table.
4. Join on smalldatetime fields in Time Dimension and Fact table to populate time_key in 2 from Time Dimension table.
5. Drop the index and column of smalldatetime field in fact and reassign non clustered index to Time_Key (FK)

Let me know how the above approach sounds to you guys.

V|||My gut feeling is that creating and dropping the temporary index on the smalldatetime column will take as long as doing a non-indexed join. Generally, indexes are only valuable because they are used more than once, so the investment involved in creating them is saved over each subsequent operation.
An index on a set of 72 discreet values may not even give you much performance boost across millions of records.|||I suppose the question I would pose to you, more than your design, would be, have you chosen the right granularity for your fact table? I haven't seen too many fact tables that stop at a monthly level unless they are being used for forecasting or budgeting purposes and even then they align to pre-existing warehouses, like a sales warehouse. My best recommendation would be to take some time and study warehousing and ensure you are providing a solution that isn't going to have to be reworked a couple of months down the road when the end users want to be able to drill down into details.