I worked in a medium-large enterprise (a bank) which required regular integration and transfer of data from a variety of sources, including 2-3 brands of database, 2 separate IBM mainframes, the occasional spreadsheet and probably a few other things I've tried to forget. Kettle is great for that kind of stuff.
In the larger BI suite, the Pentaho report designer is good for constructing reports for
Java webapps - either the BI server or external applications. We didn't use Pentaho for's OLAP capabilities, but that's because we weren't aware of them at the time.
One thing that's great about the Pentaho tools is that their control files are all XML format. Back then we were using the Microsoft DI tool, and it was frankly, hopeless. It was intended to be set up tediously and manually via a GUI interface. You could dump it to a really long and ugly Visual Basic format, but that was about it. Maybe do some OLE control, but not from our Sun servers, of course.
It's not infrequent, however, for me to make batch updates to Pentaho control files, however. In fact, just yesterday I took a whole raft of transformations and ran them through sed to add stuff that wasn't worth manually doing when I designed each transformation. For more extensive mods, I use XSLT. One of my favorite tricks is to take a Jasper report definition and do wholesale tweaking of the fonts on a page just to see what looks best. You can't do that with products such as (pick-a-random-IBM-product) which use binary file formats.