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

eign keys might not be defined. In such cases you would need to define the rela-
tionships between the tables in your DSV. Choosing the key attribute can actually
impact processing and query performance. If you know that you have two columns
in the dimension table that each uniquely identifies a row in the table and if they
are of different data types, such as integer and string, try to choose the column
that is an integer. Key attributes that are of integer data types occupy less storage
and are faster to retrieve than those of type string, because the number of bytes
used to store string types are typically larger than that for integer data types. In
addition, if you have a choice of choosing a single column as a key attribute as
compared to choosing multiple columns in the table, choose the single column as
the key column. If you are already aware of these techniques and have designed
your database accordingly, it's great. Some might think that all they need is just
more disks — we consider that disk space is quite cheap to buy and they are much
faster than before — and some might think a few bytes might not make a big differ-
ence. However, imagine your dimension has millions of members — no matter how
much disk space you have, accessing each member during queries takes time.
The fastest processing time of fact-table partitions occurs when the fact
table has integer foreign keys for the dimension, and the dimension con-
tains the integer key as an attribute (perhaps hidden). This allows the
SQL query sent by Analysis Services during processing to be efficient
in terms of query performance. We are aware of several international
major customers with large data warehouses using composite keys with
long strings. Each of these companies had a data warehouse before they
started using Analysis Services and they consider a change prohibitively
expensive. Their design works and their business benefits from using
Analysis Services. But their time window for processing fact-table parti-
tions would be much smaller if they had used integer keys.
Avoid Unnecessary Attributes
Because Analysis Services supports attributes hierarchies, you can cre-
ate many attributes which allow the users to analyze their data along
those attributes. However, if you create too many attributes that are
never used in customer queries, it will waste system data storage slow-
ing both processing and query performance. We recommend you look
at each dimension in detail and eliminate attributes that will never be
queried by users. Although your dimension table(s) might contain sev-
eral columns it is usually not necessary to convert every single column