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

Looking at the total numbers, any hypothesis could be true, but in reality the data
can be examined to give the real numbers. By using a many-to-many relationship,
it is possible to create a multidimensional model with a "start period" and an "end
period" date and a rating dimension. Using a pivot table, the user can create a
transition matrix that correlates each possible rating at the start period with each
possible rating at the end period.
Further description of this kind of advanced modeling technique is beyond the goal of
this topic. These scenarios and many others are described in detail in a paper named
The Many-to-Many Revolution , which is downloadable from http://www.sqlbi.com/ .
Performance issues
The particular calculations made at runtime to resolve queries involving many-to-many
dimension relationships can be quite expensive and result in performance issues. To
understand why this is, we need to understand how queries involving many-to-many
dimensions are resolved. Taking the example query we've been looking at using Sales
Reason , when the query is run, Analysis Services first takes the set of sales reasons
selected, then scans the intermediate measure group to find all of the individual sales
orders from the Sales Order dimension associated with each sales reason, and then
queries the main measure group to aggregate the requested measures for all of these
sales orders. If there are a large number of sales orders associated with each sales
reason, this could take a lot of time to do, and because the query on the main measure
group is resolved at the sales order granularity, this severely restricts the ability of
Analysis Services to use aggregations to improve performance.
The critical issue is the size (that is the number of rows) in the intermediate measure
group. If it is small, it could be possible to build some aggregations on the main
measure group at the level of the granularity attributes of the dimensions that make
the join with the intermediate measure group. In these cases, aggregations can
improve the execution time of the queries. However, if the intermediate measure
group has millions of rows, aggregations will not improve execution time much.