Extending and Customizing Cubes - SQL Server Analysis Services 2012 Cube Development - page 157

Defining measure expressions

Measure expressions allow the performing of a calculation at the individual dimension
member-level and subsequently aggregating the result for the entire dimension. A
measure expression is either a product or division of two measures that could be-
long to the same or different measure groups. For example, the sample Adven-
ture Works multidimensional database contains Reseller Sales and Exchange
Rates measure groups. Since the fictitious bike company operates globally, we might
want to express the [reseller sales amount] measure in multiple currencies.
As you know, the currency rates vary from time to time, and hence, we maintain the
Exchange Rates measure group that tracks how various currencies perform in com-
parison to the base currency of the USD. Both Reseller Sales and Exchange
Rates measure groups have direct relationships with the date dimension. However,
Exchange Rates is also directly related to the destination currency measure group,
whereas Reseller Sales is not. To relate the Reseller Sales measure to the
Destination Currency dimension, we need a many-to-many relationship with the
Exchange Rates measure group. This makes sense if you examine Reseller
Sales Amount values reported in just two currencies: USD and EURO. For simpli-
city's sake, let's just have a look at the second half of 2006:

Average
Rate

Reseller Sales
Amount

Row Labels EURO US Dol-

lar

EURO US Dollar

CY 2006 .90 1.00 € 4,134,145.70 $3,713,626.10

H2 CY 2006 .90 1.00 € 4,134,145.70 $3,713,626.10