While working with the dimension designer, it is important to understand some of the useful attribute properties that determine how dimensions and dimension attributes function. To view the properties, just select the required attribute in the dimension designer and press F4 to open the properties window. There are many properties and explaining each one is out of the scope of this post, but will definitely check some of the useful ones.
Properties that control the display and visibility
- AttributeHierarchyDisplayFolder – Organize the attribute hierarchies into a folder which is displayed by the client applications. Let’s have a look at the client dimension in which the details have been divided and arranged into two separate folders, Personal Details and Additional Details.
- AttributeHierarchyEnabled – Determines whether the attribute hierarchy is enabled for this attribute. If it is not, the attribute cannot be used in a user-defined hierarchy. Values available are True or False and by default the vale is True means the attribute hierarchies are enabled. The best practice is to keep only the required attributes as it adds to the complexity and storage of a dimension and may affect the performance.
- AttributeHierarchyOrdered – Determines the attribute members sorting. By default, the value is true which involves an additional processing time for sorting which can be saved by making it as false if sorting not required.
- AttributeHierarchyVisible – Indicates whether the attribute is visible to the client application. Values available are True or False. If the value is set as false, the attribute would not be visible for browsing through the application but still be available for querying.
- DefaultMember – Defines the default member for an attribute. In absence of the default member, ALL level is used. This property can also be specified using the MDX expression.
Properties related to the attribute column bindings
- KeyColumn – The KeyColumn connects the dimension to the fact table. The key column can be formed using one or more columns. Typically it is the primary key of the dimension tables.
- NameColumn – The Name column provides the value for an attribute that the user will see. Basically it can be used to mask the key column to show user friendly information. For example, in a Product dimension, Product Key is the primary key and is the KeyColumn for the Product dimension but it is of no use to the user to browse the data as it is an integer value. We can set Product Name as the NameColumn for Product Key and the user will see the Product Name when he browses the data by Product Key. Similarly we can use a calculated column created as a named calculation in the DSV. For example in the Employee dimension, Employee Key is the key column but we want to display employee full name instead. To do this, we can create a named calculation “FullName” by concatenating first name and last name in the DSV and use this calculated column as the NameColumn.
- ValueColumn – Specifies the column that provides the value of the attribute member. The Value column can be accessed by MDX calculations.
Other properties
- AttributeHierarchyOptimizedState – Determines the level of optimization applied to the attribute hierarchy using the indexing strategies.
- Description – Contains the description of the attribute.
- IsAggregatable – Indicates whether the attribute member values can be aggregated. It decides whether an attribute has a ALL level or not. Setting the property to True, the system defines an ALL level which contains the aggregated data. In other words, to hide the ALL member, IsAggregatable property is set to False. In that case a default member is used.
- MemberNamesUnique – Indicates whether member names are unique for this attribute.
- MembersWithData – Used in the parent-child hierarchy by the parent attribute to specify the treatment of the fact data associated with non-leaf members.
- Name – Contains the user-friendly name of the attribute.
In the next part we will check the properties that control the sorting and grouping of attributes. Stay tuned…
Regards
Amit Karkhanis
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
Nice description very helpful. 🙂
Thanks