RE: precision of calculations in MDX, implications on sorting

#1

Okay, so the datatypes are exactly like these in SQLServer.

About the solution you proposed (switching Decimal to Currency): I cheered
too early... Now it seems that our front-end (Excel 97, aaargh!) interprets
the values of the returned cellsets effectively as currencies. Because the
reports are huge we use all kinds of tricks to load the data efficiently. It
doesn't work well with the currency datatype nor with a currency casted to
decimal. Sigh. But I guess this is off-topic.

Thanks for your input,
Tom

"Joerg" wrote:

Quote:

Hello Tom,
AS creates aggregations of your measures.
The algorithms to precalculate this aggregations are working
with the selected datatype.
Because the money datatype is smaller then decimal it should be
faster. But I think you will unfortunelty not realise any performance
Improvement.

If you have problems associated with performance visit:
http://www.mosha.com/msolap/tech.htm#Performance

HTH
Jörg

"Tom VdP" wrote:

Hello Joerg,

I changed the datatype to "Currency" (that's what you meant I suppose).
It does solve the precision problems, thanks!

I could not find any documentation in BOL regarding data types in Analysis
Services.
Will this change from Decimal to Currency have any impacts on performance,
calculations, ... ?

Thanks again,
Tom

"Joerg" wrote:

Hello Tom,
set "Data Type" for your measures to "money"

HTH
Jörg

"Tom VdP" wrote:

We have a problem with the precision of the sum() function in MDX.

Fact table data:
Month & value

2003_02 0
2003_02 -49.93
2003_03 0
2003_03 0
2003_04 -534.89
2003_04 49.93
2003_04 0
2003_04 534.89

Calculating the sum with a calculated member yields a rounding error.
It should of course be exactly 0.

The MDX below returns 7.105427357601E-15.

with
set [SelectedMonths]
as '[DimKalender].&[2003_02]:[DimKalender].&[2003_04]'
member [Measures].[PeriodSum]
as 'sum([SelectedMonths], [Measures].[SomeValue])'
select
{[Measures].[PeriodSum]} on columns,
{[DimClient].[Clientgroep].&[282967]} on rows
from CubeIncasso

In a listing that is sorted on this calculated member this particular client
is not sorted correctly because for a descending sort it will appear before
other clients where the sum is exactly 0. (There are many clients with
a sum of 0, within these the sort is alphabetical.)

If more data is added to the fact table (more months) and the sum therefore
no longer equals 0, the summation is correct again.

The fact table datatype is Decimal(9,4). All measures have max 2 decimal
digits.
The measure's datatype in the cube is Numeric (same happens with Decimal).

Can anybody shed some light on this?
FWIIW, the pre-calculated aggregate behaves exactly the same: if only data as
above is present then the sum for (2003) is not 0. As the fact table grows
the
sum is again correct.

Adding Round() on the calculated members probably solves the problem, but we
already are experiencing performance problems...

Many thanks,
Tom

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

RE: precision of calculations in MDX, implications on sorting

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

RE: precision of calculations in MDX, implications on sorting - 09-01-2004 , 02:41 AM