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

query resolution involving many-to-many dimensions, see Chapter 30, “Architecture of
Query Execution—Retrieving Data from Storage.”
The performance of queries that use many-to-many dimensions is worse than the perfor-
mance of queries that use regular dimensions. Even if you have not explicitly used a
many-to-many dimension in your query, Analysis Services uses the default member from
this dimension. (For more information about the default members, see Chapter 5,
“Dimensions in the Conceptual Model,” and Chapter 10, “MDX Concepts.”) There is a
way to query a measure group that has a many-to-many dimension without performance
degradation: by defining the direct slice for your many-to-many dimension. We explain
the concept of the direct slice in the next section, “Measure Expressions.”
When it comes to loading data into a measure group and indexing it, many-to-many
dimensions work pretty much the same way that indirect referenced dimensions work:
The data in the measure group isn't loaded by the dimension, and the measure group isn't
indexed by the dimension. Indeed, a many-to-many dimension is an indirect dimension.
When an indirect dimension is changed, or added to, or removed from a measure group,
the measure group doesn't require reloading and re-indexing. There is no need to reload
and re-index your measure group if there is a change in the intermediate measure group
that binds the dimension to your measure group.
Measure Expressions
A measure expression is a definition of an arithmetic operation that calculates value of a
measure using values of two other measures. Analysis Services allows only two operations
between measures: multiplication and division.
The measure expression is a functionality that enables you to provide an effective solution
for a small but very common set of problems. For example, corporations that sell in more
than one country record sales in the currency of the country where the sale is made.
Because the Sales fact contains sales in different currencies, it is almost impossible to
analyze the sales of the corporation across countries. To solve this problem, you use a
measure group that contains the daily conversion rates between currencies. You need to
get all the sales into one currency, so choose one currency—exchange currency (some-
times also called pivot currency)—and multiply sales by the conversion rates of your
chosen currency. Figure 8.7 shows a diagram of this type of analysis.
In our FoodMart example, you want the Sales measure group to save the type of the
currency for each sale. The dimension that defines the currency in which the transaction
was reported is Transaction Currency . The Transaction Currency is a role-playing
dimension for the Currency database dimension. The Exchange Rate measure group
contains a daily exchange rate between the members of the Transaction Currency dimen-
sion and members of the Exchange Currency dimension, which is a role-playing dimen-
sion for the Currency database dimension.
The Exchange Currency dimension is also included as a many-to-many dimension in the
Sales measure group. This makes it possible to choose the currency in which you want to
see sales analyzed. With this model, all you have to do to get your sales in any currency is