I agree there's not much out there - I took on my first (and so far, last) data warehouse testing project a couple of years ago and found it pretty hard to find good advice. I haven't responded so far, as I think my experience having done just one project is fairly slight so I was waiting to see if you got more useful responses.
Some good resources:
Karen N Johnson has useful articles about BI testing: part 1, part 2, and here's one specifically about testing SCDs. I've found what she has to say about BI testing really valuable.
There's a (quiet) BI Testing group on the Software Testing Club.
I remember the big challenges for us being around deciding where the highest risks were, given limited time and resources for testing, as well as needing to suddenly get very familiar with data warehousing without having any prior experience in the test team. We ended up working very closely with the developers, and doing a lot of exploratory testing to scope out what areas were causing the biggest headaches.
Strategies we used that I'd use again:
- Creating data mapping
spreadsheets to understand how data
from our data feeds ended up in the
final data warehouse was an useful
exercise for us, even though we
didn't create them for everything.
It helped us to build up a much
better understanding of how the data
flowed through the system, that we
could ask the designers and
developers to review.
- Focusing in: Using known datasets for field-level and row-level tests for key business scenarios we'd identified
- Defocusing: as integration
testing was our main focus, we also
wanted to widen our search again to catch any scenarios that we hadn't considered, so
as well as identifying specific
tests, we also grabbed a lot of
actual data generated by the test source
systems and reviewed that to see
what other nasties turned up. (Quite
a few, as it happened - this was very productive for us and caught a lot of issues that would have broken the production batch run).
- Don't forget flow tests - extend your scenarios over time, for instance don't just have single orders that get processed through, have an order that's raised, updated, amended, cancelled, re-opened, processed. Messy, ugly, real stuff.