NBi is able to execute SQL, MDX or DAX queries and capture the result set to compare it with another resource. For this kind of test, you'll need to define your system under test as a "query" and your assertion as an "equalTo" constraint. You've the choice to assert the result of your query against three types of resources:
  • An embedded result set
  • An external csv file
  • Another query (or the same query on another connection)
NBi will create a brief analysis of the comparison of result sets generated. For this, you're able to specify the role played by each column and the variation that you'll allow on them. It's also possible to write the result sets (actual and expected) created during execution of the defined queries (for further analysis/comparison of the results).

System Under Test

Let's start with the definition of your system under test:

You must create a xml element named "execution" under the tag "system-under-test". Under this xml element, you must create another xml element named 'query'. You can associate a connection string to this query (or rely on the default connection string).
<system-under-test>
	<execution>
		<query connectionString="...">
	</execution>
</system-under-test>

The query can be specified directly inside the test suite.
<query connectionString="...">
<![CDATA[
	SELECT
		{[Measure].[MyMeasure]} ON 0,
		{[MyDimension].[MyHierarchy].Members} ON 1
	FROM
		MyCube
]]>
</query>
Usage of tags <![CDATA[ and ]]> is not mandatory but highly recommended. This will allow you to write the &, < and > characters in your query with no need to translate them in their xml equivalent (&amp; &lt; and &gt;)

An alternative is to reference your query from an external file
<query file="C:\myFile.sql" connectionString="..."/>

Assert

Once you system under test is defined, you'll need to specify that you want to assert the execution’s result of this query. This done by the tag
<assert>
	<equalTo />
</assert>

Embedded result set

The easiest way to express this is the usage of a result set embedded in your test suite.
<equalTo>
	<resultSet>
		<row><cell>First Member’s value</cell><cell>100.05</cell></row>
		<row><cell>Last Member’s value</cell><cell>77.7</cell></row>
	</resultSet>
</equalTo>


Note that the numeric values must be written with an international format (a dot (".") to separate the decimal part).

Special and Generic values

(improved in v1.3)
With NBi, you can also check your cell's value against special values such as null, empty or any value. This is detailed in the article about Special and Generic values.

Keys and Values

By default, NBi will take the assumption that the first column is a key (text) and the other columns are values (numeric without tolerance). This will influence the comparison result. If you want to override this configuration, you should read Result set's comparison configuration.

NBi is also able to manage dateTime and boolean formats without tricks in SQL queries, see more at Result set's comparison configuration

The comprison can also include Tolerances and roundings (improved in v1.3) methods when comparing numeric and dateTime types.

External CSV file

You also have the opportunity to specify that the result-set is defined in an external file (useful for large result set)
<equalTo>
	<resultSet file="C:\myResult.csv">
</equalTo>

You can also modify the CSV profile but using this tip

Another query

Finally, the third choice is to compare the result set of the query under test to the result set of another query. This can be useful to assure a non-regression between two systems or to compare the Datawarehouse data and the corresponding Olap data. The expression here under must be applied.
<equalTo>
	<query connectionString="">SELECT MyHierarchy, MyMeasure FROM MyTable</query>
</equalTo>

Naturally, you can also use other way to specify the parameters of your query (file reference, connectionString default, …)

Improve performance by parallelization of queries

Since v1.3, by default NBi will run your two queries (system-under-test and assertion) in parallel (and not anymore by serializing them). This usually improve performances because the two queries are usually on different systems.

When parallelization is activated, this can influence the output of result sets in the console or output windows. This output is only visible when the trace level is set to 4 (see Enable and display trace messages). The output of the two result sets could be mixed. This will not influence the test execution but your debugging experience can suffer. We recommend to inactivate the parallelization of queries when setting the trace level to 4.

If you want to inactivate this feature, you must specify it in the settings of your test-suite and it will affect the whole test-suite.
<settings>
    <parallelize-queries>false</parallelize-queries>
</settings>

Last edited Oct 30, 2014 at 1:13 AM by Seddryck, version 12

Comments

No comments yet.