Calculated Measure: Wierd scaling issue when subtracting two decimal numbers

I have a calculated measure which is a simple difference between a measure on consecutive dates. It seems to be working fine, except that the original numbers have a scale of 2 decimal points (they are currency measures).

This is the calculation part of the MDX expression:

([BusinessDate].[Date].CurrentMember, [Measures].[GBPEQUIV-SENSITIVIES])

-

([BusinessDate].[Date].CurrentMember.Lag(1), [Measures].[GBPEQUIV-SENSITIVIES])

eg: 2,588,829,302.21 - 2,572,404,828.64

The real answer is -16,424,473.57

However, the actual value of the calculated measure is -16,424,473.5770621 which has a higher scale than the actual numbers and is in fact inaccurate.

I presume it is doing some sort of floating point calculation but this is just wrong!

How can I fix this?

Do the actual values being loaded in from the underlying fact table only have 2 decimal points of precision? Or is the 2 decimal point precision simply set as a display format for the measure in SSAS? If the former, then I'm not sure why the value would be inaccurate. If the latter, however, the actual values being stored in the measure may contain more precision (if the underlying values from the fact table do) and you would thus be seeing the result of the calculation using the higher precision...

HTH,

Dave Fackler

|||Like Dave, I suspect the formatting, rather than precision of calculation, since Currency performs exact operations on 4 digits after the digital point. Can you specify FORMAT_STRING='Currency' for your calculated member.|||

Underlying data from Oracle has a data type of NUMBER(22,4) for these measures.

Yes they can be formatted as currency.

Even so, when doing a simple difference between two numbers with a scale of 4, the result cannot be at a greater scale, surely?

|||

When you look at the properties of the measure in the cube designer, what data type is listed for the source of the measure?

And what aggregation function (assuming Sum, but want to verify) is listed for the measure?

Dave Fackler

|||What I am saying is that the result is most likely correct, but the visual representation (formatted value) looks different because the input measures and calculated measure have different formattings.|||

Orignal data type from database is as above. In the DSV it is System.Decimal (in the XML it is xs:decimal), in the cube the data type is set to inherited.

Aggregation function is Sum.