Handling Transactional-Level Data - Expert Cube Development with SSAS Multidimensional Models - page 137

In general, to optimize cubes with many-to-many dimension relationships, it
is necessary to reduce the number of rows in the intermediate measure groups
somehow—this can be achieved by "compressing" the data, so that frequently
occurring patterns of relationships are represented by a single row in the intermediate
measure group instead of multiple rows. After that, we can try to minimize the
number of rows scanned for each query, for example, by partitioning the intermediate
measure group using one of the dimensions that has a regular relationship with the
intermediate measure group as well as the "primary" measure group. In the case of
transition matrix, the Date dimension should be related to both measure groups as a
regular dimension, and partitioning the intermediate measure group by members of
the Date dimension will reduce the number of rows scanned at query time.
The optimization of many-to-many dimension relationships, including the
compression technique mentioned previously, is covered in a white paper named
Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques ,
which is downloadable here: http://tinyurl.com/m2moptimize .
Finally, in some scenarios where there is a large dimension linking the
intermediate measure group and the main measure group, the users do
not actually need to be able to query it directly—it is just used to resolve the
many-to-many relationship and its Visible property is set to false . If this is the
case, we can avoid the processing overhead associated with such a large dimension
by breaking it up into multiple smaller dimensions, and at the same time, improve
performance of many-to-many queries too. This technique is described in detail
here: http://tinyurl.com/breaklargedims
Summary
In this chapter, we have seen how to use the drillthrough feature, discussing in detail
several options available to model and optimize drillthrough actions. We have seen the
differences between using MOLAP and ROLAP dimensions to enable drillthrough on
transaction details. We have also discussed how to include a many-to-many dimension
relationship in our model, highlighting that this single feature enables much more
complex modeling techniques, giving some examples of the possible scenarios and
providing links to external resources about modeling and optimization for this feature.
In the next chapter, we will show how to add complex calculations to the model.