We have now seen how to implement currency conversion using the Add Business Intelligence wizard in BI Developer Studio. The wizard implements currency conversion using techniques, such as MDX Script calculations that are available in both the Standard and Enterprise editions of Analysis Services. However, there is an alternative way to implement currency conversion without the need for calculations in the MDX Script. We can use the
MeasureExpression property of a measure instead, combined with a many-to-many dimension relationship in the cube.
The data model that we'll use to demonstrate this technique is very similar to the data model we used in the first scenario for the wizard. The only change is the definition of a many-to-many dimension relationship between the Currency dimension and the measure group containing the transaction data.
The Currency dimension, here, will work in the same way as the Reporting Currency dimension does in the solution generated by the wizard...