locked
Key Column Compound key RRS feed

  • Question

  • Hello,

    In one of our dimensions I  see that I find a unique number only when I use two columns to join the dimension from fact to dimension. How can this be  handles in 2008.?

    I know there is an option called collection for key column (where I can use more than one column as key column), but I am not sure how to utilize, like to create a new attribute to use ?

    If at all I use this compound key how can  I make sure that SSAS utilize the under lying index which was put on both the columns so that the retrival query is faster?
    Tuesday, September 29, 2009 2:53 PM

Answers

  • Hello,

    In one of our dimensions I  see that I find a unique number only when I use two columns to join the dimension from fact to dimension. How can this be  handles in 2008.?

    I know there is an option called collection for key column (where I can use more than one column as key column), but I am not sure how to utilize, like to create a new attribute to use ?
    Using the option to add multiple columns to the key property is how you would handle this. You simply go through the dialogs and add both columns to the key property.

    If at all I use this compound key how can  I make sure that SSAS utilize the under lying index which was put on both the columns so that the retrival query is faster?

    When SSAS processes a dimension into a MOLAP structure it reads every row, so it will do a full table scan anyway. (which is a good thing, processing an entire table via a non-clustered index would end up being much slower)

    If you had a ROLAP dimension it may need to loop up individual rows and in this case, if your index covered the query then the SQL relational engine should use it. You don't do anything special from the SSAS side to dictate index usage.


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Proposed as answer by Adam Tappis Wednesday, September 30, 2009 10:35 AM
    • Marked as answer by Raymond-Lee Saturday, October 10, 2009 10:51 AM
    Tuesday, September 29, 2009 11:58 PM

All replies

  • Hello,

    In one of our dimensions I  see that I find a unique number only when I use two columns to join the dimension from fact to dimension. How can this be  handles in 2008.?

    I know there is an option called collection for key column (where I can use more than one column as key column), but I am not sure how to utilize, like to create a new attribute to use ?
    Using the option to add multiple columns to the key property is how you would handle this. You simply go through the dialogs and add both columns to the key property.

    If at all I use this compound key how can  I make sure that SSAS utilize the under lying index which was put on both the columns so that the retrival query is faster?

    When SSAS processes a dimension into a MOLAP structure it reads every row, so it will do a full table scan anyway. (which is a good thing, processing an entire table via a non-clustered index would end up being much slower)

    If you had a ROLAP dimension it may need to loop up individual rows and in this case, if your index covered the query then the SQL relational engine should use it. You don't do anything special from the SSAS side to dictate index usage.


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Proposed as answer by Adam Tappis Wednesday, September 30, 2009 10:35 AM
    • Marked as answer by Raymond-Lee Saturday, October 10, 2009 10:51 AM
    Tuesday, September 29, 2009 11:58 PM
  • Hello,

    In one of our dimensions I  see that I find a unique number only when I use two columns to join the dimension from fact to dimension. How can this be  handles in 2008.?

    I know there is an option called collection for key column (where I can use more than one column as key column), but I am not sure how to utilize, like to create a new attribute to use ?
    Using the option to add multiple columns to the key property is how you would handle this. You simply go through the dialogs and add both columns to the key property.

    If at all I use this compound key how can  I make sure that SSAS utilize the under lying index which was put on both the columns so that the retrival query is faster?

    When SSAS processes a dimension into a MOLAP structure it reads every row, so it will do a full table scan anyway. (which is a good thing, processing an entire table via a non-clustered index would end up being much slower)

    If you had a ROLAP dimension it may need to loop up individual rows and in this case, if your index covered the query then the SQL relational engine should use it. You don't do anything special from the SSAS side to dictate index usage.


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Thanks
    Wednesday, September 30, 2009 6:33 PM