Setup Unit Testing with NUnit & NBi

I recently attended the PASS Summit in Seattle. For me one of the most interesting talks was by Davide Mauri’ who presented a very interesting session on Unit testing BI Projects. At Adatis we have our own testing framework which we have been using for some time now, however the products looked good in the demonstration and I have been excited to try them out for myself and see how they perform. The results are very promising.

This is a quick blog article to detail the initial setup of the two products and running of a very basic test, if you are interested in Agile BI (it is interesting) then these two products provide a good way of implementing unit tests in a BI project. The software supports testing of SSIS package execution, T-SQL Queries, MDX, DAX and SSRS reports. Production of tests can also be automated from a table in SQL Server (keep posted for a future blog on this).

1. Download the NUnit setup files from http://www.nunit.com/index.php?p=download

2. Run the setup utility. With the default options (this will install NUnit to C:Program Files (x86)NUnit 2.6.3)

3. Download the the files for NBi from http://nbi.codeplex.com these come as a zipped folder.

4.  You then need to copy the following files from the of the Framework folder contained in the NBi zip file into the NUnit folder (default C:Program Files (x86)NUnit 2.6.3bin). The folder must contain the following additional files upon completion:

5.  You now need to add a file which points to the tests you wish to execute. I created a subdirectory in the folder called ‘Tests’ and placed my test file in there (sample test file in following step). The file must be called Nbi.NUnit.Runtime.dll.config

The contents of the below file should be in this format, we can see that for this example we are pointing to a file with the name SampleTests.nbits contained in the folder Tests.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<configSections>
		<section name="nbi" type="NBi.NUnit.Runtime.NBiSection, NBi.NUnit.Runtime"/>
	</configSections>
	<nbi testSuite="TestsSampleTest.nbits"/>
</configuration>

<?xml version="1.0" encoding="utf-8"?>
<testSuite name="My first test suite" >="https://NBi/TestSuite">
	<test name="Test MDX Syntax">
		<system-under-test>
			<execution>
				<query name="MDX" connectionString="Provider=MSOLAP.5;Data Source=.md;Integrated Security=SSPI;Initial Catalog=Adventure Works DW 2008R2;"> SELECT [Measures].[Reseller Order Count] ON 0 FROM [Adventure Works] </query>
			</execution>
		</system-under-test>
		<assert>
			<syntacticallyCorrect/>
		</assert>
	</test>
	<test name="Test MDX Results">
		<system-under-test>
			<execution>
				<query connectionString="Provider=MSOLAP.5;Data Source=.md;Integrated Security=SSPI;Initial Catalog=Adventure Works DW 2008R2;"></query>
			</execution>
		</system-under-test>
		<assert>
			<equalTo>
				<resultSet>
					<row>
						<cell>3796</cell>
					</row>
				</resultSet>
			</equalTo>
		</assert>
	</test>
	<test name="Test SQL Results">
		<system-under-test>
			<execution>
				<query connectionString="Data Source=.;Initial Catalog=AdventureWorksDW2014;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"></query>
			</execution>
		</system-under-test>
		<assert>
			<equalTo>
				<resultSet>
					<row>
						<cell>29358677.2207</cell>
					</row>
				</resultSet>
			</equalTo>
		</assert>
	</test>
</testSuite>

8. If you run the Nunit.exe file in the NUnit folder you should see the following GUI interface:

9. If you click the run button and the tests are running successfully then you should see the following:

10. If you are intending to use NUnit & NBi for automated testing you can call them straight from a command line from the NUnit directory by issuing the following command: Nunit-Console nbi.nunit.runtime.dll The results will be output to an XML file which can be used to record details of the tests or processed by SSIS, to store the test details in a database for easy future reference.