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.
Identifies the folder in which to display the associated attribute hierarchy to end users.
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.
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.
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.
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.
Specifies the column that defines a custom rollup formula.
Specifies the column that contains the properties of a custom rollup formula.
Specifies a Multidimensional Expressions (MDX) expression that defines the default measure for the attribute.
Contains the description of the attribute.
Contains the number of buckets into which to discretize.
Defines the method to use for discretization.
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.
A user defined value that provides a hint to client applications on how to group attributes.
Contains the unique identifier (ID) of the dimension.
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:
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.
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.
Determines whether member names in the attribute hierarchy must be unique.
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:
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.
Contains the user-friendly name of the attribute.
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.
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.
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:
Identifies the attribute by which to order the members of the attribute hierarchy.
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:
Contains the type of the attribute. For more information, see Configuring Attribute Types.
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.
Describes how an attribute is used.
The available options are as follows:
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.
For more information about how to set values for the KeyColumn property when you are working with null values and other data integrity issues, see Handling Data Integrity Issues in Analysis Services 2005.
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.