Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

SSAS Dimension Properties : Advanced

  • 2 March 2013
  • Author: Chris Schmidt
  • Number of views: 14831
  • 0 Comments

Yesterday I blogged about the properties of a dimension in the basic folder, and what each property means. Today I’m going to cover the advanced folder. In the advanced folder, there are 13 different properties, all of which do (in my opinion) some pretty cool stuff.

 

Here’s screenshot of our properties window:

 

image

 

AttributeHierarchyDisplayFolder

This specifies what the folder is in the client tool that the attribute displays under. If you leave it blank it shows up directly under the dimension in SSMS, or under a folder called “More Fields” in Excel. Changing this setting to a folder you type in controls how you see it. For example, I have grouped all of the attributes in Spanish in the Adventure Works Customers dimension into a folder called “Spanish Fields”, this is what it looks like:

image

 

In Excel, it would look very similar, except the rest of the fields would show up under the “More Fields” folder mentioned above.

 

BTW, this same concept can apply to nesting folders. For example, if I wanted to nest “Spanish” under a folder called “Education Types”, in the property I would just specify my folder with a backslash, as Education Types\Spanish. My folder structure would then look like:

image

 

AttributeHierarchyEnabled

This attribute specifies whether or not SSAS even uses the attribute at all. Technically, every attribute in a dimension is an hierarchy, just at one level (Dimension level –> Attribute). If you set this to False, the attribute isn’t visible, can’t be used in a user-defined hierarchy, can’t be referenced by MDX statements, well you get the gist. It grays out in the dimension designer screen and you can’t use it at all.

 

AttributeHierarchyOptimizedState

This property has 2 options: FullyOptimized and NotOptimized. FullyOptimized is the default, and means that SSAS processes the attribute as normal. It builds indexes on disk for that attribute to keep query speeds fast. NotOptimized means that no such indexes are created, and therefore querying speeds may suffer. Only use NotOptimized if you are sure that a) it is not a queried field, and b) that attribute does not have a high cardinality in comparison to the rest of your attributes. For more information, see section 2.4.3.1.1 in the SSAS Multidimensional Performance and Operations Guide.

AttributeHierarchyVisible

This property is either True or False. True means that it is visible to all client applications, and can be sliced and diced on.

False means that the attribute isn’t visible to client applications, but can be referenced via MDX statement, actions, calculations, etc. This is handy if you have an attribute you’d like to use to order something, or just want to make available via a tooltip or drilldown or something. Think something like “Customer Address.” Do users really want a pivot table with 2 million addresses on the left of a pivot table report? Or do they want to double-click on city and see the list of addresses in that city in the detail level?

DefaultMember

This is the default member of the dimension. If this is left blank, the default member of the attribute is the all level, unless the “IsAggregatable” property below is set to false, in which case there is no All member. Then the default member is set to the first member of the attribute. SSAS uses the default member to evaluate an expression if that attribute isn’t used in a query.

DiscretizationBucketCount and DiscretizationMethod

Ok, I’m grouping these 2 together as I recently wrote a separate blog post about them indicating what they are and what they do. For more information, see here.

 

EstimatedCount

This field is used for aggregation designs. You can either enter the estimated count manually, or when using the aggregation design wizard in the cube, it will automatically count the attributes and enter it for you. This is especially handy in environments where development only contains a subset of your production data. You can enter in the actual production counts and when SSAS is aggregating, it will aggregate at the higher number you input for production, keeping your performance up.

IsAggregatable

This property specifies whether or not to create an level for that attribute. There are certain situations where it doesn’t make sense to aggregate every member of the attribute to an All level, so you can turn this off. However, if you set this property equal to FALSE, it is highly recommended that you specify a default member. 

OrderBy

This property has 4 options: Name, Key, AttributeKey, and AttributeName. It sets how to order the leaf members that are contained within the attribute when they are displayed by a client application. The descriptions below are straight out of BOL:

  • NameColumn     Order by the value of the NameColumn property.

  • Key    Order by the value of the key column of the attribute member.

  • AttributeKey    Order by the value of the member key of a specified attribute, which must have an attribute relationship to the attribute.

  • AttributeName    Order by the value of the member name of a specified attribute, which must have an attribute relationship to the attribute.

OrderByAttribute

If you select AttributeKey or AttributeName, you must fill in this field. This tells the OrderBy field what to order this attribute by, if those fields were selected.

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating

Please login or register to post comments.