Check List for Performance Optimisation in SQL Server Analysis Service 2005

There are two options available named FullyOptimized and NotOptimized. When the option is set to FullyOptimized, indexes will be created against the attributes. For the attributes which are not used frequently, you can set the option of NotOptimized.  By setting this option you will save time processing and resources such as disk space.

Turn off Attribute Hierarchy
For most of the cube attributes are used for analysis. However, there may be attributes which you don’t need for analysis. For example, in a customer dimension attributes like birth date, join date will not be used for analysis. Instead they will be used as just as information. For those attributes you can turn off attributes hierarchy.

 

As you can see there are two options available. They are True and False. If the attribute hierarchy is not enabled or AttributeHoerarchyEnabled is set to False, then the attribute cannot be used in a user-defined hierarchy.

Define Hierarchy Relationships 
Most of the time an attribute within a dimension is related to another attribute by a one-to-many relationship. A typical example is customer geography. For a customer it is geography location, country  ? province ? city ? post code.  You can improve your cube processing and query performance by defining those relationships in the relevant dimension. The attribute relationship will help the serve build efficient indices. When a new relationship is created you should remove redundant relationships. Otherwise, these redundant attribute relationships may prevent data from being aggregated when a non-key attribute is used as a granularity attribute in a cube.

You can create the relationship by dragging and dropping it to the place named <new attribute relationship>.  The following image shows how you would see the customer cube, after creating the relationships. 

 

Selecting Appropriate Process Type
When processing a cube there are three types of processing:

• ProcessFull—Erases all the data and rebuilds the dimension data and structure.
• ProcessUpdate—Implements inserts, updates, and deletes based on the type of attribute relationships in the dimension. Information on the different types of attribute relationships is included later in this article.
• ProcessAdd— Provides an optimized version of ProcessUpdate that only handle data insertions.

By selecting the correct process type you can reduce the time to process a cube.