Last week, during the system test, the tester rejected newly added SSAS cube functionality. The reason was that the 7th decimal differed from his test script result. After studying his SQL script I noted he used wrong numeric conversions. I pointed him on this and to the fact that business expects an accuracy of six decimals. Furthermore I told him that numeric math with SQL server has some odd behaviour by design, and due to this behaviour he would have some challenges to equal the results. He was not pleased he wanted the full 12 decimals behind the decimal point be equal so he raised an issue.
This week 1 designer and 2 testers did a full investigation on this matter. It costed them half a day to fully understand the mechanics of numeric math in Microsoft SQL Server, and to correct the test scripts accordingly. There was a full match between the expected results and the new SSAS functionality.
I could prevent all of this by just adding a generic round function around the ‘issue’ causing formula in SSAS. The ‘stored’ figures then match the required accuracy. I guess when I implemented this functionality, it did not pass my mind that someone else could have a completely different perception on these figures.
How accurate do you want to be?
You find more about SQL server precision, scale and length at: http://msdn.microsoft.com/en-us/library/ms190476.aspx
SSAS: Microsoft SQL Server Analysis Services.