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 https://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 https://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:
SELECT SUM(fis.SalesAmount)as SalesAmount FROM dbo.FactInternetSales AS fis
Another query will be launched on OLAP DB:
SELECT ([Measures].[Internet Sales Amount]) ON COLUMNS FROM [Adventure Works]
NBi framework to create tests
The installation is available at https://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.
This folder should contain three files (all having the same name “TestProject” but with different extensions).
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:
<?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <section name="nbi" type="NBi.NUnit.Runtime.NBiSection, NBi.NUnit.Runtime"/> </configSections> <nbi testSuite="TestProject\TestProject.nbits"/> <connectionStrings> <clear /> <add name="DW" connectionString="Server=BI\SQLSERVER2014;Database=AdventureWorksDW2012;Trusted_Connection=yes;" /> <add name="OLAP" connectionString="Provider=MSOLAP.4;Data Source=BI\SQLSERVER2014;Initial Catalog=AdventureWorksDW2014;Integrated Security=SSPI" /> </connectionStrings> </configuration>
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:
<?xml version="1.0" encoding="utf-8"?> <testSuite name="My first test suite" xmlns="https://NBi/TestSuite"> <settings> <reference name="DataWarehouse"> <connectionString>@DW</connectionString> </reference> <reference name="OlapDB"> <connectionString>@OLAP</connectionString> </reference> </settings> <test name="My first test"> <system-under-test> <execution> <query connectionString="@OlapDB"> SELECT ([Measures].[Internet Sales Amount]) ON COLUMNS FROM [Adventure Works] </query> </execution> </system-under-test> <assert> <equalTo> <query connectionString="@DataWarehouse"> SELECT SUM(fis.SalesAmount) as SalesAmount FROM dbo.FactInternetSales AS fis </query> </equalTo> </assert> </test> </testSuite>
Here are our queries. There is only one test called “My first test”. See comments about this section below:
<settings> <reference name="DataWarehouse"> <connectionString>@DW</connectionString> </reference> <reference name="OlapDB"> <connectionString>@OLAP</connectionString> </reference> </settings>
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.
<NUnitProject> <Settings activeconfig="Default" processModel="Default" domainUsage="Default" /> <Config name="Default" binpathtype="Auto" appbase="..\" configfile="TestProject\TestProject.config"> <assembly path="Framework\NBi.NUnit.Runtime.dll" /> </Config> </NunitProject>
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:
Just press F5 to launch the test. When it is green, that means the test was successfully completed.
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):
SELECT SUM(fis.SalesAmount) - 1 as SalesAmount FROM dbo.FactInternetSales AS fis
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.
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.
A config file is built to hold connection strings:
There are two files with code. The first file is RunQuery.cs. It is used to run our queries:
using System;
using System.Data.SqlClient;
using Microsoft.AnalysisServices.AdomdClient;
namespace TestNunit
{
class RunQuery
{
static public object SQLExecuteScalar(string sqlQuery, string connString)
{
object result=0;
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
try
{
conn.Open();
result = cmd.ExecuteScalar();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return result;
}
static public object MDXExecuteScalar(string mdxQuery, string connString)
{
object result=null;
using (AdomdConnection conn = new AdomdConnection(connString))
{
AdomdCommand cmd = new AdomdCommand(mdxQuery, conn);
try
{
conn.Open();
result = cmd.ExecuteCellSet().Cells[0].Value;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return result;
}
}
}
The second file is Class1.cs. It is used to store our queries.
using System;
using System.Collections.Generic;
using System.Text;
using NUnit.Framework;
using System.Configuration;
namespace TestNunit
{
[TestFixture]
public class Class1
{
[Test()]
public void MyFirstTest()
{
// get connection string from config file
string olapConnectionString = Properties.Settings.Default.Olap;
string dwConnectionString = Properties.Settings.Default.DW;
// our queries for test
string sqlQuery = "SELECT SUM(fis.SalesAmount) - 1 as SalesAmount FROM
dbo.FactInternetSales AS fis";
string mdxQuery = "SELECT {[Measures].[Internet Sales Amount] } ON COLUMNS FROM [Adventure
Works]";
// execute queries and save results into vars
decimal sqlResult = (decimal)RunQuery.SQLExecuteScalar(sqlQuery, dwConnectionString);
decimal mdxResult = (decimal)RunQuery.MDXExecuteScalar(mdxQuery, olapConnectionString);
// compare results from DW and Olap
Assert.AreEqual(sqlResult, mdxResult, "InternetSalesAmount are different in DW and Olap
databases");
}
}
}
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:
I started the test (pressed F5), and I got following image:
The DW and OLAP present different values. The error is due to the wrong SQL query being used (see highlight).
SELECT SUM(fis.SalesAmount) - 1 as SalesAmount FROM dbo.FactInternetSales AS fis)
So, I corrected the error, the DLL was rebuilt, and the new DLL was reloaded into NUnit. Here is the result:
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.
