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

The measures involved in a measure expression can be from one measure group or from
several different measure groups. The model uses the same process to resolve a join of two
measures as it uses for many-to-many dimension. Here is the process that Analysis Services
follows to resolve the join:
1. Creates a list of dimensions that are common to the two measure groups
2. Identifies the deepest common granularity
3. Retrieves the value of the measure
4. Executes the measure expression operation for every record with the common
granularity
Everything you read about many-to-many dimensions in the “Many-to-Many
Dimensions” section earlier in this chapter is also true for measure expressions that are
defined by measures from different facts, with one exception: Executing the operations for
a measure expression requires more resources.
You've been using measure expressions to analyze sales in some currency other than
dollars: To see the results in a specific currency, you choose the corresponding Currency
member from the Currency Exchange dimension. If, on the other hand, you need to
analyze only U.S. sales, you're not interested in an analysis in any other currency; the
transaction data is already in U.S. dollars. The process that Analysis Services goes through
is to convert the data from dollars to dollars by first multiplying all the records with
common granularity by the value 1.0, and then summing the results. Using a measure
expression in this case lowers performance without giving any benefits in return.
A better approach to this analysis is to add a direct slice for the Currency Exchange
dimension. To do this, in addition to regular list of currencies, such as euros, rubles, or
dollars, add an artificial member called SalesCurrency to the Currency Exchange dimen-
sion. For this member, define the exchange rate as equal to 1.0, regardless of the
Transaction Currency . In practice, introducing this new member means that you can
analyze sales in the same currency in which they were made.
Now you can set the DirectSlice property for the Currency Exchange dimension to the
member SalesCurrency . When a request is made for SalesCurrency data from the Sales
measure group, the data can be used directly without using a measure group expression,
and Analysis Services will not apply the join between two measure groups. You can use the
SalesCurrency member of the Currency Exchange dimension to get your results in dollars
for U.S. transactions, which results in better performance than you will get if you use a
measure expression alone.
Linked Measure Groups
Use a linked measure group when you want to include a measure group from another
cube in your cube. That other cube can be in the same database, another database, or even
on another computer. (We discuss linked measure groups in detail in Chapter 25,
“Building Scalable Analysis Services Applications,” where we cover methods of storing data
in the physical data model.)