THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

The importance of Unit Testing in BI

One of the main steps in the process we internally use to develop a BI solution is the implementation of Unit Test of you BI Data.

As you may already know, I’ve create a simple (for now) tool that leverages NUnit to allow us to quickly create Unit Testing without having to resort to use Visual Studio Database Professional:

Once you have a tool like this one, you can start also to make sure that your BI solution (DWH and CUBE) is not only structurally sound (I mean, the cube or the report gets processed correctly), but you can also check that the logical integrity of your business rules is enforced.

For example let’s say that the customer tell you that they will never create an invoice for a specific product-line in 2010 since that product-line is dismissed and will never be sold again. Ok we know that this in theory is true, but a lot of this business rule effectiveness depends on the fact the people does not do a mistake while inserting new orders/invoices and the ERP used implements a check for this business logic. Unfortunately these last two hypotesis are not always true, so you may find yourself really having some invoices for a product line that doesn’t exists anymore.

Maybe this kind of situation in future will be solved using Master Data Management but, meanwhile, how you can give and idea of the data quality to your customers? How can you check that logical integrity of the analytical data you produce is exactly what you expect?

Well, Unit Testing of a DWH or a CUBE can be a solution.

Once you have defined your test suite, by writing SQL and MDX queries that checks that your data is what you expect to be, if you use NUnit (and QueryUnit does), you can then use a tool like NUnit2Report to create a nice HTML report that can be shipped via email to give information of data quality:


In addition to that, since NUnit produces an XML file as a result, you can also import it into a SQL Server Database and then monitor the quality of data over time.

I’ll be speaking about this approach (and more in general about how to “engineer” a BI solution) at the next European SQL PASS

Adaptive BI Best Practices

I’ll enjoy discussing with you all about this, so see you there!

And remember:

“if ain't tested it's broken!”

(Sorry I don’t remember how said that in first place :-))

Published Thursday, April 1, 2010 7:50 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Alex K said:


Very interesting, thanks a lot for sharing. Sounds similar to what our team is doing. Do you have articles describing you typical testing usage? Mine are here:

April 2, 2010 5:13 PM

Davide Mauri said:

Hi Alex!

I've not yet written any articles, but I have a plan for doing it. Don't know when yet, but I'm going to do it :-)

April 6, 2010 2:25 AM

abx said:


May 31, 2018 11:01 PM

dongdong8 said:



June 29, 2018 3:31 AM

linying123 said:



July 16, 2018 9:23 PM

dongdong8 said:



July 23, 2018 11:43 PM

qqq said:


August 16, 2018 12:31 AM

shenyuhang said:


August 23, 2018 10:28 PM

chenlixiang said:



September 18, 2018 10:07 PM

kakakaoo said:

October 8, 2018 2:11 AM

chenjinyan said:


October 9, 2018 6:55 PM

chenqiuying said:


October 10, 2018 6:33 PM

kakakaoo said:


November 8, 2018 1:53 AM

Leave a Comment


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement