BI Tools – Testing with DBFit

During work on our in-house lightweight metadata repository (MDR), I’ve started to use DBFit for database acceptance testing and have been pleasantly surprised at the simplicity of the tool and its ability to deliver on its promise of low entry cost testing.

DBFit is a part of FitNesse, itself an implementation of the FIT acceptance testing framework.  FIT concerns itself primarily with user acceptance testing rather than unit testing and FitNesse’s implmentation as a wiki allows functional users to get more directly involved in the testing of your application.  Google it a bit for more.

The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

DBFit is directly targeted at database testing.  Tests are written as wiki tables with a query or procedure execute followed by a listing of the expected results.  It can handle direct SQL, stored procedures, or, with a bit of simple customization, custom procedures.  With about 20 minutes work I have it executing SSIS packages natively.

Here’s why DBFit is really attractive to me:

1. Zero install.  It runs from the command line and either starts a mini web server for visual manipulation or executes the tests from the command line.  This means I can include the entire tool in my source control tree with no external dependencies (other than .NET and Java).
2. Filesystem based wiki.  All tests are stored in a simple text file in a transparent directory structure.  I can put the whole thing under source control with zero effort.
3. Simple test language.  It took me about 2 minutes to learn how to write a test:

   !|Query|SELECT COUNT(*) AS RowCount FROM dbo.MDRObjects|
   |RowCount|
   |2|

4. Extensible.  The wrapper is Java and the test mechanism itself is Java or .NET depending on your target.  So, if you need a novel fixture you can write your own and include it.  As I mentioned, I added SSIS support in about 20 minutes (thanks to Ken Collier’s post).  Here’s the extent of the code (it needs to be made more robust, but this is the gist):

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;

namespace warehouse.etlTest {
  public class ExecuteSsisFixture: fit.ColumnFixture  {
    public string packageLocation = null;   //File path to .dtsxfile      
    public int executionResult {            //0 = "Success"               
      get {                                 //1 = "Failed"                    
          return runThePackage();           //3 = "Cancelled by user"     
      }                                     //4 = "Unable to locate file"
    }                                       //5 = "Unable to load file"   
                                            //6 = "Internal error occured"
    public int runThePackage() {            
        Package pkg;                        
        Application app;                    

        app = new Application();
        pkg = app.LoadPackage(packageLocation, null);

        return(Convert.ToInt32(pkg.Execute()));
    }
  }
}

So, I’m a convert.  After writing tests in complex frameworks and having to code them in a variety of languages (none well suited to data), this is a breath of fresh air.  All excuses for not testing your database are now NULL!

OK, there are a few gotchas:

1. Test stability (ability to return the system under test to its pre-test state) is handled in DBFit by simply running everything in a transaction and then rolling back the transaction during tear down.  This works when everything’s happening on the same connection under transaction control, but it doesn’t work for ETL packages that run under independent transactions.  BUT, this is a universal issue, not limited to DBFit.  See my post on Test Fixtures for more.
2. There’s going to be a bit of scripting magic needed to get this working within our CI tool.  XUnit works out of the box.  I think (hope!) this will be a minor inconvenience.
3. You still have to write SQL.  So, this isn’t for the completely non-technical end user.  But, I’m not really expecting a business user to get too deep here.  Most tests will still be developed by technical staff, either IT or business analysts with SQL skills.  AND, it’s still WAAY better than writing .NET code or even a full blown T-SQL stored proc.

Try it, you’ll like it!