Performance Optimization - Professional SQL Server Analysis Services 2005 with MDX - page 630

in the dimension table(s) to attributes hierarchies in the dimension. You
can see an example in the sample Adventure Works DW project's cus-
tomer dimension. Figure 13-3 shows the Customer dimension, where
you can see the list of columns in the DSV and compare it against the
list of attribute hierarchies included within the dimension.
Figure 13-3
You can see that the Dim_Customer relational table contains several
columns. However the Customer dimension includes only a subset of
those columns that are essential for data analysis as attribute hierarch-
ies. For example the column Title in the Customer's table is not used
in data analysis and hence it is not included as an attribute in the Cus-
tomer dimension.
Turn Off Optimization for Rarely Used Attributes
Although some attributes in a dimension are used for data analysis, they might be
needed only on rare occasions. For example, you might be querying an employ-
ee's zip code or phone number infrequently for analysis. By default, Analysis Ser-
vices creates indexes for each attribute; assuming the attribute hierarchy will be
used often in queries. By turning off attributes' optimization via their AttributeHier-
archy OptimizedState, you will save processing time and resources by not creating
indexes for such attributes. A query involving any NotOptimized attribute will be
slower; however, because it is rarely used, it won't hurt most of your users' query