Defining Dimension Attributes

In Microsoft SQL Server Analysis Services, there are many properties that determine how dimensions and dimension attributes function. The following table lists and describes each of these attribute properties.

Property

Description

AttributeHierarchyDisplayFolder

Identifies the folder in which to display the associated attribute hierarchy to end users.

AttributeHierarchyEnabled

Determines whether an attribute hierarchy is generated by Analysis Services for the attribute. If the attribute hierarchy is not enabled, the attribute cannot be used in a user-defined hierarchy and the attribute hierarchy cannot be referenced in Multidimensional Expressions (MDX) statements.

AttributeHierarchyOptimizedState

Determines the level of optimization applied to the attribute hierarchy. By default, an attribute hierarchy is FullyOptimized, which means that Analysis Services builds indexes for the attribute hierarchy to improve query performance. The other option, NotOptimized, means that no indexes are built for the attribute hierarchy. Using NotOptimized is useful if the attribute hierarchy is used for purposes other than querying, because no additional indexes are built for the attribute. Other uses for an attribute hierarchy can be helping to order another attribute.

AttributeHierarchyOrdered

Determines whether the associated attribute hierarchy is ordered. The default value is True. However, if an attribute hierarchy will not be used for querying, you can save processing time by changing the value of this property to False.

AttributeHierarchyVisible

Determines whether the attribute hierarchy is visible to client applications. The default value is True. However, if an attribute hierarchy will not be used for querying, you can save processing time by changing the value of this property to False.

CustomRollupColumn

Specifies the column that defines a custom rollup formula.

CustomRollupPropertiesColumn

Specifies the column that contains the properties of a custom rollup formula.

DefaultMember

Specifies a Multidimensional Expressions (MDX) expression that defines the default measure for the attribute.

Description

Contains the description of the attribute.

DiscretizationBucketCount

Contains the number of buckets into which to discretize.

DiscretizationMethod

Defines the method to use for discretization.

EstimatedCount

Specifies the estimated number of members in the attribute. Until you run the Aggregation Design Wizard, the default value is zero. Either you can allow the wizard to count the number of records or you can enter an estimated value. Enter a value manually if you know the number of members and want to save the time that is required to query the database for the count. If you are working with a test subset of your production data, you can use the counts of your production data so that the aggregation design will be optimized for the production data instead of the test data.

GroupingBehavior

A user defined value that provides a hint to client applications on how to group attributes.

ID

Contains the unique identifier (ID) of the dimension.

InstanceSelection

Provides a hint to client applications about how a list of items should be displayed, based on the expected number of items in the list. The available options are as follows:

None No hint is provided to the client application. This is the default value.

DropDown The number of items is small enough to display in a drop-down list.

List The number of items is too large for a drop-down list, but does not require filtering.

FilteredList The number of items is large enough to require users to filter the items to be displayed.

MandatoryFilter The number of items is so large that the display must always be filtered.

IsAggregatable

Specifies whether the values of the attribute members can be aggregated. The default value is True, which means that the attribute hierarchy contains an (All) level. If the value for this property is False, the attribute hierarchy does not contain an (All) level.

KeyColumns

Contains the column or columns that represent the key for the attribute, which is the column in the underlying relational table in the data source view to which the attribute is bound. The value of this column for each member is displayed to users unless a value is specified for the NameColumn property.

MemberNamesUnique

Determines whether member names in the attribute hierarchy must be unique.

MembersWithData

Used by parent attributes to determine whether to display data members for non-leaf members in the parent attribute. This property value is only used when the value of the Usage property is set to Parent. This means that a parent-child hierarchy has been defined. The available options are as follows:

NonLeafDataHidden Non-leaf data is hidden.

NonLeafDataVisible Non-leaf data is visible.

MembersWithDataCaption

Provides a template string that is used by parent attributes to create captions for system-generated data members in the parent attribute. This property value is only used when the value of the Usage property is set to Parent. This means that a parent-child hierarchy has been defined.

Name

Contains the user-friendly name of the attribute.

NameColumn

Identifies the column that provides the name of the attribute that is displayed to users, instead of the value in the key column for the attribute. This column is used when the key column value for an attribute member is cryptic or not otherwise useful to the user, or when the key column is based on a composite key. The NameColumn property is not used in parent-child hierarchies; instead, the NameColumn property for child members is used as the member names in a parent-child hierarchy.

NamingTemplate

Defines how levels are named in a parent-child hierarchy constructed from the parent attribute. This property value is only used when the value of the Usage property is set to Parent. This means that a parent-child hierarchy has been defined.

OrderBy

Describes how to order the members that are contained in the attribute hierarchy. The default value is Name, which specifies that ordering of the attribute members is based on the value of the NameColumn property, if any. Otherwise, members are ordered by the value of the key column. The available options are as follows:

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

Identifies the attribute by which to order the members of the attribute hierarchy.

RootMemberIf

Determines how the root or topmost members of a parent-child hierarchy are identified. This property value is only used when the value of the Usage property is set to Parent. This means that a parent-child hierarchy has been defined. The default value is ParentIsBlankSelfOrMissing, which means that only members that meet one or more of the conditions described for ParentIsBlank, ParentIsSelf, or ParentIsMissing are treated as root members. The following values are also available:

ParentIsBlank Only members with a null, a zero, or an empty string in the key column or columns are treated as root members.

ParentIsSelf Only members with themselves as parents are treated as root members.

ParentIsMissing Only members with parents that cannot be found are treated as root members.

Specifies the column that provides unary operators. It is a binding of DataItem type that defines the details of a column providing a unary operator.

Usage

Describes how an attribute is used.

The available options are as follows:

Regular The attribute is a regular attribute. This is the default value.

Key The attribute is a key attribute.

Parent The attribute is a parent attribute.

ValueColumn

Identifies the column that provides the value of the attribute. If the NameColumn element of the attribute is specified, the same DataItem values are used as default values for the ValueColumn element. If the NameColumn element of the attribute is not specified and the KeyColumns collection of the attribute contains a single KeyColumn element representing a key column with a string data type, the same DataItem values are used as default values for the ValueColumn element.

The default member on an attribute is used to evaluate expressions when a member from the hierarchy is not explicitly included in a query. The default member for an attribute is specified by the DefaultMember property on the attribute. Whenever a hierarchy from a dimension is included in a query, all the default members from attributes corresponding to levels in the hierarchy are ignored. If no hierarchy of a dimension is included in a query, then default members are used for all the attributes in the dimension. For more information on default members, see Defining a Default Member.