One of the earliest memories of my business intelligence career was learning how to test, specifically how to unit test. As a developer, this type of testing was my primary responsibility. The basic direction was to ensure business rules were being adhered to in the program and to document what I had tested. As a junior developer, this direction seemed simple enough, until I was alone at my PC. Then, the same set of common complaints, concerns, issues and questions that I hear from junior developers today hit me. This column lists the common extract, transform and load unit test concerns I encountered and offers solutions with an overall directive: there is no excuse for poor unit testing.

The time saved by lax or poor testing (unit or otherwise) ends up costing much more in bug fixes and redesign efforts the farther down the road the flaws are discovered. If you're reading this article as a junior developer, I hope you have a mentor teaching you these tips. If you're an experienced architect and/or mentor, are you still following these guidelines when you get your hands dirty in development? And are you passing these skills on to the next generation?

For the record, what follows is not the same thing as unit testing. Just because a program complies (or is valid) does not mean that it is unit tested. Also, if a program executes successfully, it is not automatically unit tested. A program has to run and data has to pass through that program from a source to a target with business rules executed to be unit tested.

The following are not valid excuses to avoid unit testing:

  • "It was a small change."
  • "I didn't have time."
  • "It's too hard to replicate the environment" (meaning variables that exist in a full load are not present in the program that is being changed).
  • "No one else is doing it."
  • And my favorite ... "I don't have test data."

If the change was small, that's great - unit testing should take no time. But you still need to do it. If you don't have time, make time. The time you'll have to take later when you discover that you have an issue will be more costly. You have to test and you have to make dates; it's part of the deal. Sometimes the amount of hard coding required to simulate the variables that are present as part of the normal load can be difficult, but there is always a way to ensure your changes are adequately tested. Consider making a copy of your completed program where you can hard code the components that are normally variables. Recreate table structures and programs in a personal development schema and change the environment that your program runs in so you can control the situation. It may sound a little daunting, but even in the worst cases, I have rarely seen this take more than an hour or two - don't be lazy. Finally, mocking up test data is just a part of life on the BI developer trail, so get used to it. There are free tools available that help, or I find Excel to be very effective. Save the data you've mocked up and use it as a starting point for the next time you need it.
Unit testing happens at a program level. A good unit test spreadsheet should contain documentation on all of the business rules tested, the positive and negative outcome when applicable, the script used to do the actual test, the data used, the expected result, the actual result and a space for notes. Be the developer with the documented unit test cases. When there is an issue or question after your code has been promoted past the development layer, you can be confident about your code and start with questions about the data or the environment instead of questioning whether your program is working correctly. Make scripts that update records in any data set to create the test cases you will need time and again to regression test. Automate the data setup, test script execution and pass/fail logic. This all takes extra time in setup but will save time in the long run as enhancements are implemented.

Test the success and failure of every business rule and piece of logic - that's all there is to it. Sometimes junior resources are not managed closely enough when timelines are compressed, or the fundamentals are missed when experienced resources just don't do the basic blocking and tackling. It is likely there will still be bugs, and no one is perfect, but there's no excuse for a program running for the first time in the test layer. There's always a way to perform a good unit test, and doing it is worth it!

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access