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:
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:
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:
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.
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 22.214.171.124.1 in the SSAS Multidimensional Performance and Operations Guide.
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?
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.
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.
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.
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.
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.