The Analysis Services Performance Guide from bunch of Microsoft Analysis Services experts have been updated since 2005 edition for 2008 R2 and for 2012.

• Microsoft SQL Server 2005 Analysis Services Performance Guide
• Microsoft SQL Server 2008 R2 Analysis Services Performance Guide
• Microsoft SQL Server Analysis Services Multidimensional Performance and Operations Guide

I highly recommend the guide. It is short, to the point and it’s FREE.

Here is my summary of how to build optimal dimensions from the Part 1 of the guide.

1. Efficient Use of Attributes Is a Key Design Skill to Master

• Dimensions are composed of attributes, which are related to each other through hierarchies.
• Studying and implementing the attribute relationships available in the business model can help improve cube performance.
• Using the KeyColumns, ValueColumn, and NameColumn properties effectively can reduce processing time. They also reduce the size of the dimension and the likelihood of user errors. This is especially true for attributes that have a large number of members, that is, greater than one million members.
• KeyColumns: define the attribute; uniquely identify each instance of the attribute; It is a best practice to assign a numeric source field, if available, to the KeyColumns property rather than a string property; Furthermore, use a single column key instead of a composite, multi-column key.
• NameColumn: displayed to end users; if empty, default to the KeyColumns property.
• ValueColumn: typically used for calculations; strongly typed and can be accessed through the MemberValue MDX function.
• Using ValueColumn, and NameColumn properties: eliminates the need for extraneous attributes. This reduces the total number of attributes in your design, making it more efficient.
• Consider using Key fields (numeric) for the KeyColumns property, rather than the alphanumeric fields.

2. Considering Hiding Most Attribute Hierarchies

• Set AttributeHierarchyVisible = false for most attribute hierarchies.
• Hiding the Surrogate Key.

3. Considering disabling Attribute Hierarchy

• If attribute is included for ordering purpose, set AttributeHierarchyEnabled = false and AttributeHierarchyOptimizedState = NotOptimized to save on processing operations

4. Considering Disabling Ordering of Attribute

• For such hidden attribute that you used only for implementation purposes, you don’t care about the ordering of an attribute (the surrogate key is one such case), set AttributeHierarchyOrdered = false to save time during processing of the dimension.

5. Set Explicit Default Attribute Members Properly (carefully)

• Default Attribute Members: Any query that does not explicitly reference a hierarchy will use the current member of that hierarchy.
• The default behavior of Analysis Services is to assign the All member of a dimension as the default member.
• Explicitly set default members only on dimensions with single hierarchies or in hierarchies that do not have an All level.

6. Removing Meaningless All Level

• It can even be expensive to ask for the All level of dimension if there is not good aggregate to respond to the query.
• You can disable the All member in a hierarchy. You do this by setting the IsAggregateable = false on the attribute at the top of the hierarchy.
• Note that if you disable the All level, you should also set a default member as described in the previous section–if you don’t, Analysis Services will choose one for you.

7. Identifying Attribute Relationships Other Than the Relationships to the Primary Key

• Bushy attribute relationship: By default, all attributes are related to the primary key, and the attribute relationship diagram represents a “bush” where relationships all stem from the key attribute and end at each other’s attribute.
• Redefine attribute relationships: You can optimize performance by defining hierarchical relationships supported by the data.
• Attribute relationships help performance in 3 ways:
  • Cross products between levels (for example the cross-product between Subcategory and Category) in the hierarchy do not need to go through the key attribute, saving CPU time during queries.
  • Aggregations built on attributes can be reused on related attributes, saving resources during processing and for queries.
  • Auto-exits can be more efficient to eliminate attribute combinations that do not exist in the data.
  • If the attribute relationship is defined, the Analysis Services engine knows beforehand which category each subcategory belongs to via indexes built at process time.

8. Flexible vs. Rigid Relationships: considering using rigid relationships

• A flexible attribute relationship (customer-city) is one where members can move around during dimension updates, and
• A rigid attribute relationship (month-year) is one where the member relationships are guaranteed to be fixed.
• Processing flexible attribute relationship is expensive: When a change is detected during process in a flexible relationship, all indexes for partitions referencing the affected dimension (including the indexes for attribute that are not affected) must be invalidated. This is an expensive operation and may cause Process Update operations to take a very long time. Indexes invalidated by changes in flexible relationships must be rebuilt after a Process Update operation with a Process Index on the affected partitions; this adds even more time to cube processing.
• Flexible relationships are the default setting. Carefully consider the advantages of rigid relationships and change the default where the design allows it.

9. Considering unnatural user hierarchies

• There are two types of user hierarchies, natural and unnatural hierarchy.
• Unnatural hierarchies have at least two consecutive levels that have no attribute relationships, commonly created for drill-down paths of commonly viewed attributes that do not follow any natural hierarchy. An example is gender-education relationship.
• Unnatural hierarchies are not materialized on disk, and the attributes participating in unnatural hierarchies are not automatically considered as aggregation candidates. Rather, they simply provide users with easy-to-use drill-down paths for commonly viewed attributes that do not have natural relationships.
• By assembling these attributes into hierarchies, you can also use a variety of MDX navigation functions to easily perform calculations like percent of parent.

10. Considering accessing attributes via member properties only

• Disable the attribute’s hierarchy: by setting the AttributeHierarchyEnabled property to False if you only want to access an attribute as member property.

• Member properties provide a different mechanism to expose dimension information.
• For a given attribute, member properties are automatically created for every direct attribute relationship.
• For the primary key attribute, this means that every attribute that is directly related to the primary key is available as a member property of the primary key attribute.

• After you verify that the correct relationship is in place, you can disable the attribute’s hierarchy.
• Disabling the attribute hierarchy can improve performance and decrease cube size.
• Because the attribute will no longer be indexed or aggregated. This can be especially useful for high-cardinality attributes that have a one-to-one relationship with the primary key.
• High cardinality attributes such as phone numbers and addresses typically do not require slice-and-dice analysis.
• By disabling the hierarchies for these attributes and accessing them via member properties, you can save processing time and reduce cube size.