Accuracy

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.

Advertisements

About Kees Beimans

Passionate Business Intelligence consultant. Interested in why, what, when, wherefore in any order. View all posts by Kees Beimans

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: