Generally I think it’s best to stick with fixed perspectives unless the alternate perspectives are designed (and enforced) to specific goals. The problem with NWN2 is that instead of settling on a camera system (or mixed camera system) and designing to that (or vice versa), they just gave the players so many options that the camera worked against the gameplay as often as it worked with it.
Will Wright discussed this recently in an interview (and it’s showed up in other Spore videos). In Spore, the camera reacts to both your play style and the gameplay at the time. For example, the cellular level looks like it plays like a 2D game, but when you become a creature it switches to a 3D camera mode. It then chooses a perspective based on whether you are controlling your creature with a mouse or WASD. Then as you move on to the next tiers (tribal, civ, and space) the camera changes to fit the gameplay. In the interview he says some really interesting things about making the computer better playmates, responding (or guessing) at what you’re trying to do and enabling that.