SSAS 2008 Unleashed - Chapter 8. Advanced Modeling

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 (sometimes 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 dimension and members of the Exchange Currency dimension, which is a role-playing dimension 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 define a measure expression for the Store Sales measure: the multiplication of the sales from the Sales measure group by the exchange rate of the Exchange Rate measure group. Listing 8.4 shows the DDL that defines this measure expression.

Listing 8.4. DDL for Measure Expression

<Measure>
     <ID>Sales</ID>
     <Name>Sales</Name>
     <Source>
          <DataType>Double</DataType>
          <Source xsi:type="ColumnBinding">
               <TableID>dbo_sales_fact_1997</TableID>
               <ColumnID>store_sales</ColumnID>
          </Source>
     </Source>
     <MeasureExpression>[Sales]*[Rate]</MeasureExpression>
</Measure>

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:

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 dimension. 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.