locked
Attribute Relationships, Aggregations and Query Performance RRS feed

  • Question

  • Hello all,

    It has taken me some time, but I have finally gotten a good grip on how to properly maintain attribute relationships within my dimensions.  

    Now I am trying to get a better understanding on how this leads to improved query performance.

    I have seen many comments on this forum and elsewhere that maintaining proper attribute relationships is essential to allowing the cube to design aggregations that, in turn, can lead to improved query performance.

    My question, and this never seems explicitly stated, is are these aggregations created automatically at the time of cube processing?  Or, when people make a comment such as this, are they implying that you will also go into the Aggregation Wizard and build aggregations against partitions?  

    I guess I am wondering if the process of improving query performance is two parts (i.e. 1. define attribute relationships 2. use aggregation wizard to build aggregations) or just one part (i.e. 1. define attribute relationships and the cube processing will auto-build aggregations based on these attribute relationships).

    I hope what I am stating is clear, and I appreciate any responses.

    David


    David Duncan Consultant I.B.I.S., Inc.

    Wednesday, April 18, 2012 7:01 PM

Answers

  • Actually its both (As per my understanding), you can create the efficient attribute relationship and during the cube processing, aggregations will be build based on the relationshiptype and attributeusage properties.

    And you can also create the efficient attribute relationship and the use the Aggregation wizard to build the aggregations which actually gives you more control.

    • Proposed as answer by LearnQuick Thursday, April 19, 2012 2:47 AM
    • Marked as answer by David J. Duncan Thursday, April 19, 2012 12:56 PM
    Wednesday, April 18, 2012 8:10 PM

All replies

  • Actually its both (As per my understanding), you can create the efficient attribute relationship and during the cube processing, aggregations will be build based on the relationshiptype and attributeusage properties.

    And you can also create the efficient attribute relationship and the use the Aggregation wizard to build the aggregations which actually gives you more control.

    • Proposed as answer by LearnQuick Thursday, April 19, 2012 2:47 AM
    • Marked as answer by David J. Duncan Thursday, April 19, 2012 12:56 PM
    Wednesday, April 18, 2012 8:10 PM
  • Thank you for taking the time to respond.

    I was hoping for an answer such as this (combination of both).  It means that tackling query performance as it relates to attribute relationships and aggregations can be a two part process.  I can create valid attribute relationships during the initial phase of my design, but then utilize Usage Based Optimization Wizard to pinpoint specific aggregations after users begin using the cubes.

    One more question: are there any other attribute properties I want to pay attention to in order to ensure that the "automatic" aggregation process is working as well as it can?

    Thanks,

    David


    David Duncan Consultant I.B.I.S., Inc.

    Wednesday, April 18, 2012 8:29 PM
  • These are few attribute properties which you may want to pay attention to:

    AttributeHierarchyEnabled
    AttributeHierarchyOptimizedState 
    IsAggregatable

    Also, setting the keyColumn as shortest numeric field can help in improving the performance too.

    Thursday, April 19, 2012 2:46 AM