Testing Business Intelligence (BI) and datawarehouse solutions

I recently had the pleasure of entertaning a small group of BI-professionals on the ever interesting topic of “test”. My brief was very short – like “can you participate for an hour and talk about test?”. Of course I could.

So we went through the usual discussions about testing challenges in general, the more specific ones about the nature of BI and datawarehouse solutions, the complexity of data, the heterogenuos system-of-systems setups we usually encounter in this area of business and last but not least the more universal ones about always being last in the food chain and how this affects testing.

 

Before speaking I got a bit of inspiration from this excellent link.

A lot of problems were discussed and finally somebody raised his voice and said: “But I hoped you were able to provide us with the silver bullet of testing BI solutions.” After 20 seconds of silence I had to admit: “There is no silver bullet”.

Of course there is no silver bullet. There are several bullets including those big ones for cannons but the real bullet is looking at the organisation of BI- and datawarehouse teams and understanding their background and the way they developed from “merge this data into one excel sheet” into “align this data from these 80 sources and give me a transparent and flexible datamart”. That is the essence of testing challenges. That most other branches of IT have understood and acknowledged these challenges and have adapted with proper processes and tools.

Within BI there seems to have been an understanding that “we can test our way out of the problems” – and then it has been one failure after the other. The combination of functional testers and test managers and “BI-teams” trying to do end-to-end testing is not a happy combination. Add to that missing or in-complete test environments and lots of configuration and reconfiguration happening all the time and you have a pre-programmed failure.

If I were to spend my money on testing within this field from scratch I would bet them on testing the ETL part. This is where you have a relative chance of success based on the fact that:

• It is a relatively simple process (or set of different processes with similar goal).
• It is possible to do checks for every step
• the input and output can be predicted and to some extent it does not matter whether you have complete data sets
• It is possible to repeat the ETL process (full or partially) for every error that is found to see that re-test and regression testing results are as expected.

Doing end-to-end testing is the ultimate goal, ETL is the pragmatic start with a chance of success. It’s similar to all other complex integrated test tasks with some slightly different challenges related to BI.