Recently Arun Sundararaman from Accenture has posted an article on DWBI Testing in Information-management.com. The article can be found out here. The article brings in a very timely discussion on the state of testing methodologies for data warehousing projects.
DWBI testing is so far the least explored area in the data warehousing domain. Majority of data warehousing projects that fail, rarely fail in the implementation phase, rather they mostly fail in the user acceptance phase. This is largely due to the fact that end users often find their data warehouse generating unacceptable reports (Or reports generating numbers outside their "tolerance" limit) while compared to actually known business scenarios. Whatever be the root cause of that, proper testing is the only way of detecting and fixing those issues.
Unfortunately, in the current data warehousing context, the only viable method of testing is through manual SQL scripting. Metadata management tools fail miserably if "SQL Override" or "Stored Procedures" are used in the ETL phase. But that's not the only real problem of automated testing. The main issue is we are yet to come up with a generic testing strategy for data warehouse data reconciliation method.
I believe this is a high time when data warehousing practitioners, both individual and organizations, take data warehousing testing seriously and develop a common methodology for the same.