Common advantages of automated testing include:

  • Reliable/repeatable/reusable
  • Better quality software
  • Fast
  • Long-term savings

These all jump to mind when talking about automated testing in Business Intelligence. In most cases, automated testing makes sense for BI. The only negatives I can imagine are:

  1. The upfront time and expense of creating good, reliable automated scripts, and;
  2. That you can never guarantee every outcome 100%, and some manual testing as well as human analysis of results will always be necessary.

But these are weak arguments compared with the benefits of automated testing.

Benefits of automated testing

I think we can agree that quality of data in BI is the most important thing. If you had a handful of data in your BI system, you could check it manually. But most BI systems have huge data warehouses. How much of that data could you verify manually? Certainly no more than 5 or 10%. As a result, at least 90% of your data would remain untested. And of course that can lead to missing data, data truncation, data type mismatch, data format mismatch, null translation, wrong translation, misplaced data, duplicated records, rejected rows and so on. All of this can be prevented with automated testing in which you can verify bigger volumes of data in a smaller period of time. This is the number one reason for automated testing in BI – data quality.

Next, the upfront cost of automated testing is easily justified by the long-term results. If you relied only on manual testing you would almost surely get systemic errors down the road. The expense to fix these errors later far outweighs the initial investment in automation. Plus, you would waste valuable time fixing errors when you could be bringing the system online.

Beyond these obvious benefits of automated testing there are other more subtle, but also important, benefits. For starters, there is the human factor in manual testing. The routine, monotonous nature of this work naturally leads to errors. Better to leave this kind of testing to machines and allow humans to spend their time more creatively, improving the quality of the project. Moreover, automated testing enables the entire team to watch testing efforts as they happen. You can share information on your data’s health through real-time reports, audit history and dashboards. This timely knowledge about the testing process can lead to more interaction and productive collaboration among team members.

And finally, automated testing can be easily repeated at any time of day, night, weekend or holiday, based on a schedule or triggers of your making. The time that would be needed to test the ETL manually is almost inconceivable. And while test cases run automatically many times in row, you have time for creating new testing scenarios, analyzing results and other more important tasks.

In conclusion, it would be difficult to justify NOT automating BI testing. The upfront time and expense of building test scripts is far less than the potential time and expense you would spend fixing systemic errors to build a reliable BI system without automation.