Advanced Modeling - Microsoft SQL Server 2008 Analysis Services - page 100

Sale and Time of Arrival . Now we have two different dimensions, one to use in each of
the facts.
As it turns out, the Product dimension is common to both facts. The Time and Customer
dimensions are regular dimensions of the Sales measure group. The Warehouse dimension
is a regular dimension of the Warehouse measure group and a many-to-many dimension
for the Sales measure group. The dimension that's included as a many-to-many dimen-
sion should be present in the intermediate fact (the one the relationship is defined
through). The common dimensions have to be either regular or referenced dimensions.
Many-to-many dimensions can create complex chains, with facts being related to one fact
through many other facts. Analysis Services determines whether there are loops in chains,
and lets you know about it by generating an error message.
When you define relationships between measure groups that are defined by a many-to-
many dimension, you can end up with common dimensions that have different granular-
ity for different measure groups. If this happens, Analysis Services uses the deepest
common granularity for the relationships between the measure groups.
Now let's take a look how Analysis Services generates a response to the query that involves
many-to-many dimension. For example, we introduce a question to determine the value
of the sales of products delivered by a warehouse. If we ask for the total sales supplied by
just one warehouse, Analysis Services iterates the sales by product, selects only records
with the products the particular warehouse supplied, and sums the sales of those products.
If you were to ask for the sum of sales for each warehouse, Analysis Services would go
through the same process for each warehouse. If you were to then add the sums of the
warehouse sales to get the total sales of all warehouses, you would get a strange result. The
sum of sales by warehouses will be larger than sales by the stores. Results differ because
data from some records is included in the sum multiple times. For example, multiple
warehouses can supply the same product, and sales of that product will be applied to sales
of all the warehouses. Therefore, the summing (aggregating) of a measure using a many-
to-many dimension doesn't follow the rules of aggregation—that is, it's not aggregatable,
and not additive.
It's not always easy to interpret the results of a query that uses many-to-many dimensions;
in many cases, you can get quite unexpected results. If you look at the result of sales in
our FoodMart example by the Warehouse dimension, the results don't make sense. That's
because we built only one Time dimension in to the sample. You actually need two Time
dimensions for products in relation to warehouses: the time the product arrives at the
warehouse, and the time it was sold in a store. If you have only one Time dimension, that
dimension is common and is applied to only the sales of products on the day those prod-
ucts arrived at the warehouse. That sales figure would be much less than expected; the
data is unusable.
Using many-to-many dimensions for queries requires more system resources, especially
when you have many common dimensions and their granularity is at a low level.
Resolution of a query under these conditions requires that Analysis Services get all the
records of the measure group at a granularity level common to all the common dimen-
sions—and doing so can really eat up your system resources. For more information about