Designing More Complex Dimensions - Expert Cube Development with SSAS Multidimensional Models - page 75

• Use of a parent/child hierarchy in a query can lead to very poor query

performance. In general, performance of small parent/child hierarchies
of up to a few hundred members is fine, but as soon as you have more than
a few thousand members on a parent/child hierarchy, the performance can
suffer badly.

• Following on from the previous point, you cannot build an aggregation at

the granularity of a parent/child attribute hierarchy; you can only build
aggregations above the granularity of the hierarchy or ones at the granularity
of the key attribute. It is not possible to build aggregations on any of the
intermediate levels that appear in a parent/child hierarchy. This makes it more
difficult to tune query performance, although it should be noted that it is not
the sole cause of the poor query performance of parent/child hierarchies.

We can work around some of these problems (notably, not being able to build multiple
parent/child hierarchies on the same dimension; having to build them from the
key attribute; and surrogate keys being used in unique names) by creating multiple
Analysis Services dimensions from the same physical dimension table. We would
create a dimension to hold just the surrogate key and then build separate dimensions
for each parent/child hierarchy and use non-surrogate key columns on the dimension
table as the key attribute for them; these then would be linked to the main measure
group by using a Referenced relationship (which we'll talk about in the next chapter)
via the new surrogate key dimension. Doing this, however, is likely to have an impact
on the processing performance if you materialize the referenced relationships or query
performance if you don't materialize the referenced relationships.

We recommend that you avoid setting the IsAggregatable property of a
parent/child hierarchy to False whenever you can. If you do, then every
query will be forced to the granularity of the key attribute of the dimension with
the parent/child hierarchy, even when the query doesn't use the parent/child
hierarchy. Setting IsAggregatable to True though means that an All Member will
be created on the hierarchy and an aggregation can be built at that granularity. For
the same reason, we also recommend that you think very carefully before setting the
DefaultMember on the parent/child hierarchy.