Comparison of decimal values with NBi

When you’re developing some tests with NBi it’s not uncommon that you’ve some issues to compare decimal values and this is especially true if your tests involve an MDX query. Usually, you’ll meet a pitfall where NBi is comparing two rows and returns that the value “10.3” is different than “10.30” or even “10,30”. The following test simulate this issue:

<testSuite name="Compare decimal values" xmlns="http://NBi/TestSuite">
	<settings>
		<default apply-to="everywhere">
			<connectionString>Data Source=mhknbn2kdz.database.windows.net;Initial Catalog=AdventureWorks2012;User Id=sqlfamily;password=sqlf@m1ly</connectionString>
		</default>
	</settings>
	<test name="Keys on decimal value" uid="0001">
		<system-under-test>
			<execution>
				<query>
					select cast(10.3 as decimal(3,1))
				</query>
			</execution>
		</system-under-test>
		<assert>
			<equalTo>
				<resultSet>
					<row>
						<cell>10.30</cell>
					</row>
				</resultSet>
			</equalTo>
		</assert>
	</test>
</testSuite>

multiple-representations-of-numeric
After the execution of this test, you’ll receive a report of failure similar to the screenshot at your right. If you analyze this report you’ll understand that the representation of the numerical value “10.3” has not been handled the same way in the system-under-test and in the assert. In the system-under-test, NBi receives the information from Sql Server that the value returned is a decimal (so a numeric) and NBi will handle this value as a numeric. It means that when displaying this value, NBi will use your regional settings and, in my case, it will use a comma as decimal separator. The display will be “10,3” as highlighted in blue on the screenshot. On the other hand, for the assertion, NBi is taking the assumption that the value “10.30” is a textual value. So the representation in the report will not be modified and the value “10.30” will be displayed (highlighted in red). When NBi compares the content of two cells, it always use the type specified in the assertion. So in this case, NBi compares effectively two textual values “10,3” and “10.30”. Following this logic, the two rows are effectively different.

type-in-the-headerThe good news is that you can easily tune this behavior. In fact if you’re vigilant, you’ve probably seen that NBi is warning you about the comparison as textual values in the failure’s report. In the header of the tables, NBi explains the considered type for the comparison. In this sample, NBi explains that it’s currently using a type “Text” (circled in red).

To change this, you’ll need to overwrite the default configuration of NBi. By default, NBi considers that all the columns of a result-set are textual values except the last one which is numeric. But in the case of result-set that only one column, NBi considers this column as textual. To change this, you need to express a column definition in your assertion. This definition is highlighted in the code below at the line 3.

<assert>
    <equalTo>
        <column index="0" role="value" type="numeric"/>
        <resultSet>
            <row>
                <cell>10.30</cell>
            </row>
        </resultSet>
    </equalTo>
</assert>

Putting in place this configuration of the result-set, we’ve defined that the column is now a “numeric” and it’s role is “value” and not anymore a “key”. NBi will now compare two numeric values “10.3” and “10.30”, which are identical, and return a success.
Green-message

Advertisements

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