How to optimize performance for dimension attributes

cubedimensionssas

I'm looking to enhance the cube processing time and someone suggested me to set "AttributeHierarchyEnabled='False'" for attributes which were not used at user defined hierarchies but I'm not clear with the "AttributeHierarchyEnabled" property and it's functionalities.

Can anybody just explain me when to set

AttributeHierarchyEnabled='True'

and

AttributeHierarchyEnabled='False'

and it's effects ?

Also I would like to know the use of "AttributeHierarchyVisible" and "IsAggregatable" properties.

Please give me some information about it which is going to be very useful for me.

Best Answer

MSDN states:

The value of the AttributeHierarchyEnabled property determines whether an attribute hierarchy is created. If this property is set to False, the attribute hierarchy is not created and the attribute cannot be used as a level in a user hierarchy; the attribute hierarchy exists as a member property only. However, a disabled attribute hierarchy can still be used to order the members of another attribute. If the value of the AttributeHierarchyEnabled property is set to True, the value of the AttributeHierarchyVisible property determines whether the attribute hierarchy is visible independent of its use in a user-defined hierarchy.

  • IsAggregatable

    By default, an (All) level is defined for all attribute hierarchies. To disable the (All) level for an enabled attribute hierarchy, set the value for this property to False.

If we set AttributeHierarchyEnabled to False, we cannot add that Attribute to a user defined hierarchy, we restrain SSAS from making any MOLAP-storage optimizations for this attribute. When we use such attributes in an MDX query as Attribute Hierarchy, we get no results. Although we can still use this as a member property in a query using

 Member_Expression.Properties(Property_Name [, TYPED])

According to Dustin Ryan's post:

This will prevent end users from slicing with the attribute, reducing storage requirements and increasing performance.

If we set AttributeHierarchyVisible to False, the Attribute Hierarchy is active but stays hidden when a user browses the cube while you can still use it in MDX query.

If we set IsAggregatable to False, SSAS does not create the aggregated All member for us for that attribute.

Relevant MSDN thread that might be useful: Dimension attribute with AttributeHierarchyEnabled=true and AttributeHierarchyVisible=false

Related Question