Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: 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,

    V

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The 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...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2002
    Posts
    53

    Indexing Star Schema

    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.
    KDK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •