Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

24.3. Precision Considerations

Going back to the previous table in the section "Currency Conversion Scenario (m:n)," you may recall that for June 1, 2004, you had conversions (see the following table):

	Euro		US Dollar
	Internet Sales Amount	Average Rate	Internet Sales Amount	Average Rate
June 1, 2004	45,485.67	.98	$44,650.70	1.00

Recall that the Internet Sales Amount in Euros is calculated by taking the [Internet Sales Amount (US dollars)]/[Average Rate (Euro)]. If you were to do the math here, you'll notice that $44,650.70/0.98 = $45,561.94 and not the indicated value of $45,485.67 in the table.

The issue we have here is a case of precision; the [Average Rate] measure displayed its value with a FORMAT_STRING='#,#.00' which means it is showing data only to two decimal places. If you were to execute the SQL query from the "Currency Conversion Scenario (m:n)" section, it would reveal that the actual exchange rate for June 1, 2004 between the Euro and the US dollar is: 0.981643270835379. Now, if you were to do the [Internet Sales Amount (USD)]/[Average Rate (Euro)] you would get the same [Internet Sales Amount (Euro)].

There are two important things to note here — the first is that Analysis Services is performing the division calculation using the full precision of the average rate value even though it is only presenting the value to two decimal places.

The second point is that you need to be careful about the impact of precision in your calculations. Please refer to the "Precision Considerations ...