Different rounding methods in SQL Server relational engine and SSAS

My super cool colleague Dan told me, that apparently the rounding methods implemented in the SQL Server engine and in SSAS MDX are different. Gotcha!

Let's try to round the values -25.5, -24.5, 24.5, and 25.5 to 0 (zero) decimals in the different engines.

In MDX, we get -26, -24, 24, 26:

In T-SQL, we get -25, -24, 24, 25:

Oh, and in SSAS tabular DAX, you just specify your rounding behaviour, using functions ROUNDUP or ROUNDDOWN

Read more about

Rounding problem in SSAS MDX here

http://www.bp-msbi.com/2009/08/custom-rounding-and-truncation-of/

DAX rounding here

https://msdn.microsoft.com/en-us/library/ee634402.aspx

Wikipedia (as always) have more information on rounding that you would wish for your worst enemy to read here:

https://en.wikipedia.org/wiki/Rounding

T-SQL script:

use tempdb go

select Round(23.5, 0) as [Round(23.5, 0)] , Round(24.5, 0) as [Round(24.5, 0)] , Round(-23.5, 0) as [Round(-23.5, 0)] , Round(-24.5, 0) as [Round(-24.5, 0)]

MDX script:

WITH MEMBER MEASURES.Round24_5 as Round(24.5,0) MEMBER MEASURES.Round25_5 as Round(25.5,0) MEMBER MEASURES.RoundMinus24_5 as Round(-24.5,0) MEMBER MEASURES.RoundMinus25_5 as Round(-25.5,0) SELECT { MEASURES.Round24_5 , MEASURES.Round25_5 , MEASURES.RoundMinus24_5 , MEASURES.RoundMinus25_5} ON COLUMNS FROM [<cube name>]

#SQLServer #SSAS #Gotcha