SSAS Dimension Properties: Advanced folder

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

Advertisements

4 thoughts on “SSAS Dimension Properties: Advanced folder”

  1. AttributeHierarchyEnabled: It grays out in the dimension designer screen and you can’t use it at all.

    This is not true. It can be used. Add a relationship between an attribute with AttributeHierarchyEnabled set to true (default) and an attribute with the setting set to false and then you can use the latter attribute as a so called Member property that can add further valuable information to your attribute. E.g lets say that Customer No is set to true and Customer Name is set to false – then create a relationship between Customer No and Customer Name and you can when you use the Customer No attribute choose to show the Customer Name in an additional column (if the client software supports this). This way to add extra information to your attributes is very performance friendly compared to have both Customer No and Name added to your query as 2 separate attributes.

    1. Hi Soren,
      Thanks for commenting. I’ve seen that approach before, and it definitely works. The only issue that I personally have encountered with it is creating the extra indexes on disk. To accomplish the same thing, I could leave the key as Customer No (using the example above) and then set the member name property to the Customer Name attribute, without having to bring customer name into the attribute list at all.

      Good tip, as always definitely more than 1 way to solve a problem. Thanks for sharing!

      1. I know the example with Customer No and Name was a bit simplyfied, but lets say you would like to add the information about the customer address or phone number to the customer attribute without enabling the option to use this information as hierarchies in your cube (just because it would not make any sence). Then AttributeHierarchyEnables set to false is a great way to get this information and still have great performance.

  2. Is there any way to make Dimensions behave this way? Can I combine say a Race dimension with an Ethnicity Dimension and have:

    Customer Attribute Dimensioin container:
    Race
    – Race CD
    – Race TTL
    Ethnicity
    – Ethnicity CD
    – Ethnicity TTL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s