Reasons to create autotests

BI solutions are usually designed to help businesses make decisions about their companies. Can you imagine a situation in which a decision is made based on incorrect data? It could lead to serious problems for the company.  It is therefore very important to have trusted and reliable data in your BI system. One possible method of detecting incorrect data is the implementation of autotests. They may be used in different phases of your BI system. You may use them in the development process of your BI to make sure that any changes made are expected and acceptable. Autotests can also be used as part of your daily ETL. You can flag errors in critical data or even roll back new data to its previous version in your BI system.

Types of autotests

There are at least three possible uses for autotests:

  • Compare data between the source and the target system. The autotest could be used to check a simple metric like row count for some entity in the source and target system. Or it may be used to compare a record set. The purpose of this kind of test would be to make sure that all rows are loaded and that the distribution data meets your expectations.
  • Check the structure of database(s). You can create a test to check that a table in your data warehouse has declarative foreign keys and that they are indexed.  You can also check the definition of columns for an entity in the source and target system. For example, say we have a customer name with definition 100 chars in the source system and the same in the DW. After some time, the 100 chars are extended to 150 chars in the source system. If you don’t make the same changes in the DW, you may face issues in the future.  The autotest helps insure that the data corresponds.
  • Ensure security.  This kind of autotest is important if you use one database for different clients.  One client seeing data that belongs to another client is unacceptable. To avoid this problem, you design the database and grant access only to them. However, that is not enough. You need to create autotests that will ensure that one client cannot see the data of another client.

Tools for autotests

Many existing tools can be used to create an autotest. I chose to use NUnit partly because it allows autotests to be launched by UI and from the command line. But this is just one step. You need to prepare your tests and launch them with NUnit. I suggest two possible ways to prepare your tests:

  • Create a DLL ( for example using C#). When creating your autotests in this DLL, you have to write all code  from scratch.
  • Configure your tests in XML. You don’t need to create a DLL using C#.

NUnit installation, demo data, connection strings and simple autotests

Your first step is to install NUnit. It is available at http://nunit.org/index.php?p=download. I also recommend downloading https://launchpad.net/nunitv2/trunk/2.6.3/+download/NUnit-2.6.3.msi  because the tools we are going to use are somewhat limited. Also, the latest version of NUnit does not have GUI. They decided to build it separately, but it seems they have not finished it yet. I am going to use GUI to show how it can be used to launch autotests.

I used the AdventureWorksDW SQL database and the AdventureWorks OLAP database in the following examples. You can find them at http://sqlserversamples.codeplex.com/. Many different databases are available. You could use versions from SQL 2008 up to 2014. Just download the DW version and multidimensional model of OLAP DB. After you restore your DW database, you need to point the OLAP datasource connection to DW and reprocess OLAP DB.

Here are the connection strings I will use:

 

For SQL server

“Provider=SQLNCLI11;Server=BI\SQLSERVER2014;Database=AdventureWorksDW2012;Trusted_Connection=yes;”

 

For OLAP DB

“Provider=MSOLAP.4;Data Source=BI\SQLSERVER2014;Initial Catalog=AdventureWorksDW2014;Integrated Security=SSPI”

 

The test we are going to build is quite simple. We want to make sure that the TotalAmount of sales by internet in the DW is the same as in OLAP DB. Of course, this may not be an “honest” test because we may consider the DW as an intermediate layer. It is more appropriate to use the AdventureWorks OLAP database as the source for this test, but I am trying to simplify the process. I am going to use the following query to get data from the DW for this test:

 

 

Another query will be launched on OLAP DB:

 

 

NBi framework to create tests

The installation is available at http://www.nbi.io/. You just need to extract the content of the zip file. After this step, I created a folder on the same level called TestProject.

 

image09

 

This folder should contain three files (all having the same name “TestProject” but with different extensions).

 

image04

 

One file contains my test. (It has extension nbits.)

The second contains configuration for tests and for Nbi framework. (It has extension config.)

The third contains the NUnit project. It will be used by the NUnit program to launch tests. (It has extension nunit.)

Here is the text from the configuration file:

 

 

You need to change the connection string according to your environment. Note that the data in this file has two aliases for connection strings: “OLAP”  and “DW”. These aliases will be used in the file with the autotests.

Here is the content of the file that contains our autotest:

 

 

Here are our queries. There is only one test called “My first test”. See comments about this section below:

 

 

Here @DW and @OLAP are aliases from our config file. We have to create aliases again for these aliases before we can use them here. DataWarehouse and OLAP DB are new aliases, and we use them when we set connection strings for our queries (@ indicates alias).

The third file is the NUnit project file. It tells us where our config file for tests and Nbi framework is located.

 

 

Now the work is almost done. We can launch our test with NUnit. Go to your folder where NUnit was installed (in my computer, it is c:\Program Files (x86)\NUnit 2.6.3\bin folder) and start nunit.exe. After this step, you need to choose Menu File → Open Project or just press Ctrl + O and select our TestProject.Nunit file. You will see the following:

 

image05

 

Just press F5 to launch the test.  When it is green, that means the test was successfully completed.

 

image03

 

Now let’s play around a bit and try to get a failure. I changed our query (changed file TestProject.nbits and save these changes) in this way (minus one from our total):

 

 

After this step, you have to reload the test (just press Ctrl + R) to pick up the changes. When you launch the test again, your test will have failed, and you will see a red progress bar.

 

image00

 

In the above image, you can see what was expected from the DW and what was received from the OLAP DB.

Another framework can be found at https://biquality.codeplex.com/. Unfortunately, I had trouble installing it on Windows 8.1, but I successfully installed it on Windows XP. It has functionality similar to Nbi, and the tests are listed in the XML files.

Build DLL with autotest in VS using C#

You need to start your VS and create a new project – ClassLibrary. I used 4.5 framework for this step.

I added the above references to my project.

 

image06

 

A config file is built to hold connection strings:

 

image01

 

There are two files with code. The first file is RunQuery.cs. It is used to run our queries:

 

 

The second file is Class1.cs.  It is used to store our queries.

 

 

NOTE: [TestFixture] hints at NUnit. It says that the following class (Class1) has test methods.

[Test()] means that the method that follows will be tested in NUnit. So, I can just build the solution and get DLL with one test. I open NUnit GUI and select just built DLL:

 

image07

 

I started the test (pressed F5), and I got following image:

 

image08

 

The DW and OLAP present different values. The error is due to the wrong SQL query being used (see highlight).

 

 

So, I corrected the error, the DLL was rebuilt, and the new DLL was reloaded into NUnit. Here is the result:

 

image02

 

This test was successful.

Summary

I hope you were persuaded to create autotests. You must decide what variant to use. If you and your team are familiar with C# languages, and you are ready to spend time on this process, you may want to develop your test from scratch. On the other hand, it may be better for you to use an already existing solution and just put your queries into XML.

Software Development Software Testing Startups Co-Building Test Automation

Latest Insights in Software Development

The Rise of Kotlin – Moving Away from Java for Android Development

Kotlin is a programming language for the Java Virtual Machine that’s able to be used in any scenarios that currently…

Introducing our Sphere Heroes Program – Artem Korenev – Employee of the month

At Sphere, employee recognition is a key component of our corporate culture. We believe in celebrating the successes of our…

Write For Sphere

Are you a writer with tech expertise? Then we want to hear from you! Here are a few guidelines for…

View All Articles arrow

We are here to help:

checkmarkto become a customer checkmarkto become an investor checkmarkto send a media inquiry checkmarkto join our team checkmarkto simply say ‘hi’
Get in Touch