Search results matching tags 'Business Intelligence' and 'QueryUnit'

QueryUnit 0.0.0.8 – Trust No One

manowar — Tue, 13 Apr 2010 06:22:01 GMT

Yesterday I’ve release an updated version of QueryUnit, the version 0.0.0.8.

QueryUnit now supports AreNotEqual, Greater, and Less assertions and is more capable of managing strings results.

I must say that I cannot live anymore without a proper Unit Testing of a BI solution. Just yesterday happened that one of the unit tests at a customer site failed showing a subtle situation where the release of a new version of custom application would have corrupted the source of BI data with a very low chance that someone would have noticed it before several days. It may happen when you have more the 15 systems that handles the data needed by your BI solution.

The key message of this situation is “Trust No One”: if your data hasn’t passed quality testing it’s not trustable. Period.

So QueryUnit is now officialy an hero (at least for me) :) No superpowers still, but useful above all.

http://queryunit.codeplex.com/

The importance of Unit Testing in BI

manowar — Thu, 01 Apr 2010 16:50:06 GMT

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:

http://queryunit.codeplex.com/

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
http://www.sqlpass.org/summit/eu2010/Agenda/ProgramSessions/AdaptiveBIBestPratices.aspx

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 :-))

Agile BI Development: QueryUnit is born!

manowar — Mon, 27 Jul 2009 16:29:39 GMT

Having a developer background I’m used to work trying to apply an Agile approach to projects. In these last years I focused my work primary on BI projects, and so I tried to apply an Agile approach also to them, since I really believe that Agile is the path to follow.

While Agile methods in the classic Object-Oriented field are quite widespread nowadays, in the BI field they are basically totally unknown to masses (at least this is my experience). Of course this means also that there is a total lack of tools and instruments to properly adopt Agile in your approach.

Unit-Testing is surely something that is absolutely necessary if you want or need to refactor a solution. Since now, to unit-test staging databases, datawarehouses and cubes was a pain. I needed a tool to automate everything. I searched on the web but I found nothing I really liked. So I started to wrote something by myself.

The first release is more a proof of concept that anything else, but I’ve started to use it in current BI project I’m working one with a lot of satisfaction. Now each time I change something in DWH or Cube I can automatically tests hundreds of queries. If all lights are green I can ask the customer to test the new implementation being sure that all existing reports, cubes, queries and so no will still be working perfectly. Otherwise I have some more work to do [:)].

Here’s a sample screenshot:

As you can see I’m using NUnit as Unit-Testing framework. It will allow to test SQL and MDX queries stored into an XML file, so that no .NET coding is required.

I’m going to release this first release ASAP, hope you’ll enjoy it as much as I am doing, and that you start to embrace Agile also in the DB field, if you’re not already with it. :)